Click here to Skip to main content
15,881,882 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hello Everyone,

Following is my trigger on SQL Server 2008

SQL
ALTER TRIGGER [dbo].[Trigger_UpdateLeadMasters] ON [dbo].[LeadMasters]
FOR UPDATE
AS

DECLARE @CompanyName	nvarchar(50);	
DECLARE @PersonName	nvarchar(50);	
DECLARE @Designation	nvarchar(50);	
DECLARE @Number	nvarchar(50);	
DECLARE @Number2	nvarchar(50);	
DECLARE @Emailaddress	nvarchar(50);	
DECLARE @Address	nvarchar(MAX);	
DECLARE @Address2	nvarchar(MAX);	
DECLARE @CityName	nvarchar(50);	
DECLARE @State	nvarchar(50);	
DECLARE @PinNumber	nvarchar(50);	
DECLARE @Product	nvarchar(50);	
DECLARE @RemarkNote	nvarchar(MAX);
DECLARE @Audit_Action varchar(100);


select @CompanyName=i.CompanyName from inserted i;	
select @PersonName=i.PersonName from inserted i;		
select @Designation=i.Designation from inserted i;
select @Number=i.Number from inserted i;		
select @Number2=i.Number2 from inserted i;		
select @EmailAddress=i.EmailAddress from inserted i;		
select @Address=i.Address from inserted i;		
select @Address2=i.Address2 from inserted i;	
select @CityName=i.CityName from inserted i;		
select @State=i.State from inserted i;		
select @PinNumber=i.PinNumber from inserted i;	
select @Product=i.Product from inserted i;		
select @RemarkNote=i.RemarkNote from inserted i;

IF UPDATE(CompanyName)
set @Audit_Action=' Name Modified or Updated'
IF UPDATE(PersonName)
set @Audit_Action='Person Name Modified or Updated'
IF UPDATE(Designation)
set @Audit_Action='Designation Modified or Updated'
IF UPDATE(Number)
set @Audit_Action='1st Phone Number Modified or Updated'
IF UPDATE(Number2)
set @Audit_Action='2nd Phone Number Modified or Updated'
IF UPDATE(EmailAddress)
set @Audit_Action='Email Address Modified or Updated'
IF UPDATE(Address)
set @Audit_Action='Address Modified or Updated'
IF UPDATE(Address2)
set @Audit_Action='Alternate Address Modified or Updated'
IF UPDATE(CityName)
set @Audit_Action='City Name Modified or Updated'
IF UPDATE(State)
set @Audit_Action='State Modified or Updated'
IF UPDATE(PinNumber)
set @Audit_Action='PinNumber Modified or Updated'
IF UPDATE(Product)
set @Audit_Action='Product Field Modified or Updated'
IF UPDATE(RemarkNote)
set @Audit_Action='Remark Note Modified or Updated'

insert into LoggerLeadMasters(CompanyName,PersonName,Designation,Number,Number2,EmailAddress,Address,Address2,CityName,State,PinNumber,Product,RemarkNote,Audit_Action,Audit_Timestamp)
Values(@CompanyName,@PersonName,@Designation,@Number,@Number2,@EmailAddress,@Address,@Address2,@CityName,@State,@PinNumber,@Product,@RemarkNote,@Audit_Action,GETDATE())


But the problem is whenever I modify any field in LeadMasters Table, the trigger goes to line

SQL
IF UPDATE(RemarkNote)
set @Audit_Action='Remark Note Modified or Updated'

and stores wrong information to LoggerLeadMasters.Can anyone help me to fix this issue

Thanks
Posted
Updated 21-Nov-14 1:07am
v2
Comments
Tomas Takac 21-Nov-14 7:28am    
Can you post the update statement itself too?
Shweta N Mishra 21-Nov-14 10:37am    
What is your update statement on which this trigger has executed ?
vivek murli 21-Nov-14 22:43pm    
Thanks for replying guys

@Tomas and Shweta

The update statement is this
IF UPDATE(RemarkNote)
set @Audit_Action='Remark Note Modified or Updated'

In case of each update trigger,whatever you update in a table,the last update statement is executed like the one I have written above
Shweta N Mishra 22-Nov-14 2:53am    
np :)
vivek murli 22-Nov-14 2:56am    
@Shweta N Mishra Solution?

If it is only ever for single-row updates
Maybe something like this
Just done in notepad, not checked etc.

ALTER TRIGGER [dbo].[Trigger_UpdateLeadMasters] ON [dbo].[LeadMasters]
FOR UPDATE
AS

IF EXISTS(
SELECT 'Name Modified or Updated'
FROM inserted i JOIN deleted d ON (/* join by PK of UpdateLeadMasters */)
WHERE (i.CompanyName IS NULL AND d.CompanyName IS NOT NULL)
OR (i.CompanyName IS NOT NULL AND d.CompanyName IS NULL)
OR (i.CompanyName IS NOT NULL AND d.CompanyName IS NOT NULL
AND i.CompanyName <> d.CompanyName)
)
SET @AuditAction = 'Name Modified or Updated'

IF EXISTS(
SELECT 'Person Name Modified or Updated'
FROM inserted i JOIN deleted d ON (/* join by PK of UpdateLeadMasters */)
WHERE (i.PersonName IS NULL AND d.PersonName IS NOT NULL)
OR (i.PersonName IS NOT NULL AND d.PersonName IS NULL)
OR (i.PersonName IS NOT NULL AND d.PersonName IS NOT NULL
AND i.PersonName <> d.PersonName)
)
SET @AuditAction = 'Person Name Modified or Updated'

/* ... etc ..*/

insert into LoggerLeadMasters(CompanyName,PersonName,Designation,Number,Number2,EmailAddress,Address,Address2,CityName,State,PinNumber,Product,RemarkNote,Audit_Action,Audit_Timestamp)
Values(@CompanyName,@PersonName,@Designation,@Number,@Number2,@EmailAddress,@Address,@Address2,@CityName,@State,@PinNumber,@Product,@RemarkNote,@Audit_Action,GETDATE())
 
Share this answer
 
Obviously the trailing Insert would need to modified accordingly
 
Share this answer
 
Comments
vivek murli 26-Nov-14 23:12pm    
Thanks Robert.Will give it a try

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900