Hello Everyone,
Following is my trigger on SQL Server 2008
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
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