Trigger for UPDATE runs many time on batch Updates

All of my tables have a Trigger for CRUD operations. here is a sample:

ALTER TRIGGER [dbo].[Cities_tr] ON [dbo].[Cities] AFTER INSERT, UPDATE
AS
BEGIN 
    DECLARE @operation CHAR(6)

    SET @operation = CASE WHEN EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
        THEN 'Update'
        WHEN EXISTS (SELECT * FROM inserted)
        THEN 'Insert'     
        WHEN EXISTS(SELECT * FROM deleted)
        THEN 'Delete'
        ELSE NULL
        END 
    IF @operation = 'Insert'
        INSERT INTO history ([dt],[tname],[cuser] ,[id],op) 
            SELECT  GETDATE(),'Cities',  i.ldu, i.CityId,@operation
            FROM inserted i

    set nocount on

    IF @operation = 'Update'
        INSERT INTO history ([dt],[tname],[cuser] ,[id],op)   
            SELECT  GETDATE(),'Cities',  i.ldu,  i.CityId,@operation   
            FROM deleted d, inserted i
END 

If I update one row, everything works fine and trigger inserts one row in history.

For example

update top(1) cities set f=1

But if more than one row updated, updatedrow^2 rows will be inserted.

For example 9 for 3 rows 100 for 10 rows…

What is wrong with my trigger and how could I solve it?

Answer

You are cross joining inserted and deleted. Normally, they would be joined using the table’s primary key, which is presumably CityId:

    INSERT INTO history ([dt], [tname], [cuser] , [id], op)   
        SELECT  GETDATE(), 'Cities',  i.ldu,  i.CityId, @operation   
        FROM deleted d JOIN
             inserted i
             ON d.CityId = i.CityId;

In this case, deleted is not being used, so it does not even need to be included in the query.

You could implement the entire trigger as a single query in the table using LEFT JOIN:

    INSERT INTO history ([dt], [tname], [cuser] , [id], op)   
        SELECT GETDATE(), 'Cities',  i.ldu,  i.CityId,
               (CASE WHEN d.CityId IS NOT NULL THEN 'Update' ELSE 'Insert' END)  
        FROM inserted i LEFT JOIN
             deleted d                 
             ON d.CityId = i.CityId;