CODEWITHSUNDEEP
sqlsql-servert-sqltriggers
Andreas
Andreas

Reputation: 2075

UPDATE and INSERT should fire trigger only once

Is there any way to combine an update and an insert statements in a way that they fires a trigger only once?

I have one particular table that has (and currently needs) a trigger AFTER INSERT, UPDATE, DELETE. Now I want to update one row and insert another row and have the trigger fire only once for that.

Is this at all possible?

I already tried a MERGE-Statement without success: The trigger fires once for the update- and once for the insert-part.

Upvotes: 0

Views: 3289

Answers (2)

Andreas
Andreas

Reputation: 2075

Well, problem solved for me. I did NOT find a way to combine the statements into one fire-event of the trigger. But the trigger behaves in an interesting way, that was good enough for me: Both calls to the trigger do already have access to the fully updated data.

Just execute the following statements and you will see what I mean.

CREATE TABLE Foo (V INT)
GO
CREATE TRIGGER tFoo ON Foo AFTER INSERT, UPDATE, DELETE
AS
  SELECT 'inserted' AS Type, * FROM inserted
  UNION ALL
  SELECT 'deleted', * FROM deleted
  UNION ALL
  SELECT 'actual', * FROM Foo
GO
DELETE FROM Foo
INSERT Foo VALUES (1)
;MERGE INTO Foo
  USING (SELECT 2 AS V) AS Source ON 1 = 0
  WHEN NOT MATCHED BY SOURCE THEN DELETE
  WHEN NOT MATCHED BY TARGET THEN INSERT (V) VALUES (Source.V);

As a result, the trigger will be called twice for the MERGE. But both times, "SELECT * FROM Foo" delivers the fully updated data already: There will be one row with the value 2. The value 1 is deleted already.

This really surprised me: The insert-trigger is called first and the deleted row is gone from the data before the call to the delete-trigger happens.

Only the values of "inserted" and "deleted" correspond to the delete- or insert-statement.

Upvotes: 1

Christian Barron
Christian Barron

Reputation: 2755

You could try something like this:

The trigger would check for the existence of #temp table.

If it doesn't exist, it creates it with dummy data. It then checks if the recent values contain the same user (SPID) that is running now and if the last time it was triggered was within 20 seconds.

If these are true then it will PRINT 'Do Nothing' and drop the table, otherwise it will do your trigger statement. At the end of your trigger statement it inserts into the table the SPID and current datetime.

This temp table should last as long as the SPID connection, if you want it to last longer make it a ##temp or a real table.

IF OBJECT_ID('tempdb..#temp') IS NULL
begin
Create table #temp(SPID int, dt datetime)
insert into #temp values (0, '2000-01-01')
end

If @@SPID = (select top 1 SPID from #temp order by dt desc) 
and Convert(datetime,Convert(varchar(19),GETDATE(),121)) between 
Convert(datetime,Convert(varchar(19),(Select top 1 dt from #temp order by dt desc),121)) and 
Convert(datetime,Convert(varchar(19),DateAdd(second, 20, (select top 1 dt from #temp order by dt desc)),121))
    begin
    PRINT 'Do Nothing'
    Drop table #temp
    end
    else
    begin
    --trigger statement

    Insert into #temp values (@@SPID, GETDATE())
    end

Upvotes: 0

Related Questions