Insert and delete in one trigger

Question

How can I combine the following two triggers, one 'after insert' and the other 'after delete' into one trigger?

create trigger TechStaff_update_Studio
on TechStaff after insert
begin
    update Studio
    set employees = employees + 1
    where studioID = Studio.studioID
end

create trigger TechStaff_update_Studio2
on TechStaff after delete
as
begin
    update Studio
    set employees = employees - 1
    where studioID = Studio.studioID
end

Which SQL Server are you targeting? Have you checked this question [http://stackoverflow.com/questions/741414/insert-update-trigger-how-to-determine-if-insert-or-update](http://stackoverflow.com/questions/741414/insert-update-trigger-how-to-determine-if-insert-or-update)? — VirtualMichael, Jul 24 '15 at 02:10

Blorgbeard · Accepted Answer · 2015-07-24T02:33:07.817

Like this, I guess:

create trigger TechStaff_update_Studio
on TechStaff after insert, delete
as
begin
    update s set employees = employees
        + (select count(*) from inserted)
        - (select count(*) from deleted)
    from Studio s

end

Notice that you must actually count rows rather than assuming one row inserted/deleted per trigger execution (e.g. delete * from TechStaff would fire the trigger once).

See MSDN on the inserted and deleted pseudo-tables.

And your where clause was always true (studioID = studioID), and therefore not needed. I assume you did mean to update every row of the Studio table (is there only one row?).

Edit: Since you say there are multiple Studio rows, you will need to be more clever. You have to get the count of deleted and inserted rows by StudioID. The following could probably be simplified, but I lack the time to make it shorter.

create trigger TechStaff_update_Studio
on TechStaff after insert, delete
as
begin
    update s set employees = employees + c.Delta
    from Studio s
    join (
        select StudioID, sum(i) as Delta
        from (
            select StudioID, 1 as i from inserted
            union all
            select StudioID, -1 as i from deleted 
        ) counts
        group by StudioID
    ) c on c.StudioID = s.StudioID
end

You may well be right. Still, I won't bother writing code for that until OP confirms. — Blorgbeard, Jul 24 '15 at 02:22
Your answer is very helpful. Thank you very much! like jpw said, there are many rows in the Studio table. — user1670163, Jul 24 '15 at 02:23
@user1670163 In that case, please check my edit. The original one will not give correct counts for some cases. — Blorgbeard, Jul 24 '15 at 02:34

score 0 · Answer 2 · answered Jul 24 '15 at 02:14

Simply you could handle it in one trigger like this :

CREATE TRIGGER [dbo].[<TriggerName>]
ON [<SchemaName>].[<TabaleName>] FOR INSERT, DELETE
AS 
........-- Your Code

And remember that inside the Trigger, you can use two tables Deleted and Inserted that they have the same as structure as your table and they have the rows that was inserted or deleted.

For more information see this link : Use the inserted and deleted Tables

score 0 · Answer 3 · answered Jul 24 '15 at 02:17

There are "tables" you can use to check if data is INSERTED or DELETED (updates are considered as deletes).

Use it on your IF statement:

For checking updates:

SELECT * FROM DELETED

For checking inserts:

SELECT * FROM INSERTED

EDIT:

This is for SQL Server

Insert and delete in one trigger

3 Answers3