CODEWITHSUNDEEP
sqlsql-servertriggerssql-update
Becky
Becky

Reputation: 115

SQL Server trigger : update query new vs old data

I have two tables. One has employee info in it. The other I want to add records to based on this employee table.

What I want to happen is whenever there are salary adjustments made to the employee table (by an UDPATE query), the extra table will have a row added to it containing a new event ID and the amount by which salaries have been adjusted (so if 5 people's salaries are increased by £1000, then the row will have the adjustment at £5000).

I've created the trigger and it adds the row with each update. However what it doesn't do is only bring in the additional salary. I can't think how to do that. I have this code so far;

Create trigger Salaryupdate
On TBL_Employees 
For update
As
    Insert TBL_audit (notes,Delta,AdjDate)
        Select 'Salary update', sum(salary), getdate()
        From TBL_Employees

I know the sum bit is wrong as I only want the change in salary value, not the total sum.

How can I find the difference between new and old values for the changed rows (or other method)?

I'm using SQL Server 2008.

Upvotes: 1

Views: 1355

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270483

You should be using the deleted and inserted tables in a trigger. So, I think:

Create trigger Salaryupdate
On TBL_Employees 
For update
As
    Insert TBL_audit(notes, Delta, AdjDate)
        Select 'Salary update',
               coalesce(newsalary, 0) - coalesce(oldsalary, 0),
               getdate()
        From (select sum(salary) as newsalary from inserted) i cross join
             (select sum(salary) as oldsalary from deleted) d;

Also, in SQL Server you can set AdjDate to have a default value of getdate() -- that way, the database takes care of setting the value when you insert another row.

Upvotes: 3

Related Questions