0

I have a trigger that is supposed to insert a new record into a audit history table (EmployeeSalaryHistory) upon the updating of the Employees table that the trigger is set to be on ON.

If I do an UPDATE on Employees where all the rows in the table are updated the trigger is called more times than the number of rows that are being updated.

e.g. if there are three rows in the Employees table the INSERT happens 9 times.

/*This UPDATE will cause the trigger to fire more than the number of rows in the Employees table.*/
UPDATE Employees SET Salary = Salary * 2 

/* supposed to be fired whenever the salary of an employee is updated */
CREATE TRIGGER [dbo].[EmployeesUpdateSalary] ON [dbo].[Employees]
AFTER UPDATE
NOT FOR REPLICATION
AS
BEGIN

INSERT INTO EmployeeSalaryHistory(EmployeeID, NewSalary, OldSalary)
SELECT I.EmployeeID, I.Salary, D.Salary
From inserted I, deleted D
WHERE I.Salary <> D.Salary

END
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user2425056
  • 327
  • 2
  • 4
  • 14
  • Maybe post your solution......as an *appended edit* to your original post.......so others can see what you ended up doing in the end. Keep your original text "in tact" so the original issue is seen clearly. – granadaCoder Jul 16 '13 at 19:50

1 Answers1

1

You essentially have a "cross join" with this query

inserted I, deleted D

3 x 3

Note, if I run this query...

Use Northwind
GO

Select * from
[dbo].[Categories] c1 , [dbo].[Categories] c2

I got "x squared" as the number of rows being returned, where x is the # of rows in the [dbo].[Categories] table.

EDIT

Try this

INSERT INTO EmployeeSalaryHistory(EmployeeID, NewSalary, OldSalary)
SELECT I.EmployeeID, I.Salary, D.Salary
From inserted I, deleted D
where I.EmployeeID = D.EmployeeID /* relationship */
and
I.Salary <> D.Salary /* filter */

Or

INSERT INTO EmployeeSalaryHistory(EmployeeID, NewSalary, OldSalary)
SELECT I.EmployeeID, I.Salary, D.Salary
From inserted I join deleted D on I.EmployeeID = D.EmployeeID /* relationship */
Where
I.Salary <> D.Salary /* filter */

To bring it back to the generic example:

    Use Northwind
    GO
Select * from
[dbo].[Categories] c1 , [dbo].[Categories] c2
Where c1.CategoryID = c2.CategoryID

will have "x rows", not "x squared rows"......

granadaCoder
  • 26,328
  • 10
  • 113
  • 146