Question: 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;

```
The problem with your code is that you are cross joining `inserted` and `deleted`. On a multi-rows update, both contain many rows, which the cartesian product multiplies.

It looks like you actually want to log the "new" rows (either inserted or updated). If so, you don’t want to select from `deleted`. Also, the conditional logic can be moved within a single query, which allows to simplify your code as follows:

```
ALTER TRIGGER dbo.Cities_tr
    ON dbo.Cities
    AFTER INSERT, UPDATE  
AS
BEGIN
    INSERT INTO history (dt, tname, cuser, id, op)
    SELECT 
        getdate(),
        'Cities',
        ldu,
        cityId,
        case when exists (select 1 from deleted) then 'Update' else 'Insert' end
    FROM inserted;
END

```

On the other hand, if you want to log both the "old" and "new" rows (which is not what your code does, even on a single-row update), then you want to `union all` two queries that select from `inserted` and `deleted`.