CODEWITHSUNDEEP
sql-servertriggerssql-server-2012-express
user2425056
user2425056

Reputation: 327

Trigger on table update is firing for more than the number of rows updated

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

Upvotes: 0

Views: 1277

Answers (1)

granadaCoder
granadaCoder

Reputation: 27894

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"......

Upvotes: 1

Related Questions