Event Trigger

[Greyskull]

Hello all,

Here is the situation, I want to create a trigger so that whenever a field gets updated say salary is increased by £2000, i want it to record a history that there was a £2000 increase, be it one record update or multiple records.

Here is what I currently have as my code:

ALTER TRIGGER [dbo].[Audit_Update] On [dbo].[tblEmployee]
FOR UPDATE
AS

DECLARE @notes VARCHAR(200);
DECLARE @DELTA MONEY;

SELECT @DELTA = e.Salary From tblEmployee e
SET @Notes='SALARIES UPDATED'

IF UPDATE(Salary)
	INSERT INTO TBL_EMP_AUDIT (notes, delta) VALUES (@Notes, @Delta)

The above code records the value that was on the last record it processed i.e record had £15000 it will record £15000 instead of it saying
'£5000'.


Thanks in advance,

[brucevde]

There are two temporary tables available to Triggers called Inserted and Deleted. These tables have the exact same structure as the table being modified. The Deleted table contains the current data. The Inserted table contains the new data.

Use these temp tables in your query to determine which records need to be created in the history table.

ALTER TRIGGER [dbo].[Audit_Update] On [dbo].[tblEmployee]
FOR UPDATE
AS

DECLARE @notes VARCHAR(200);

SET @Notes='SALARIES UPDATED'

INSERT INTO TBL_EMP_AUDIT (notes, delta) 
    Select @Notes, Deleted.Salary 
    From Deleted 
             Inner Join Inserted On Deleted.PK Field = Inserted.PK Field
    Where Deleted.Salary <> Inserted.Salary

[Greyskull]

Thanks for the reply. [Sorry took a while to get back]

How do I now edit the trigger so that it can also record the time and date when it the table is modified.

Thanks,

[brucevde]

The GetDate() function returns the current date/time.

INSERT INTO TBL_EMP_AUDIT (notes, delta, ModifiedDate) 
    Select @Notes, Deleted.Salary, GetDate()
    From Deleted 
             Inner Join Inserted On Deleted.PK Field = Inserted.PK Field
    Where Deleted.Salary <> Inserted.Salary