CODEWITHSUNDEEP
databasesql-server-2005triggers
Alt_Doru
Alt_Doru

Reputation: 335

SQL Server 2005 trigger - how to safely determine if fired by UPDATE or DELETE?

I have the following code in a SQL Server 2005 trigger: 

CREATE TRIGGER [myTrigger] ON [myTable]
FOR UPDATE,DELETE
AS
BEGIN

DECLARE @OperationType VARCHAR(6)
IF EXISTS(SELECT 1 FROM INSERTED)
BEGIN
    SET @OperationType='Update'
END
ELSE
BEGIN
    SET @OperationType='Delete'
END

My question: is there a situation in which @OperationType is not populated correctly? E.G.: the data in the table is changed by a bunch of UPDATE/DELETE statements, but the trigger is not fired once by every one of them?

Do you have a better way to determine if the trigger was fired by an UPDATE or DELETE statement?

Upvotes: 1

Views: 3576

Answers (2)

Mehrdad Afshari
Mehrdad Afshari

Reputation: 422320

Simple answer: No, there will not be a situation in which the trigger fails to detect correctly (except when there are no changed rows).

The trigger will be fired once for every statement, so the thing is not possible and it will work correctly, but the point is, if you really want to do different tasks for UPDATE and DELETE, you'd better use a couple triggers.

Upvotes: 4

Gordon Bell
Gordon Bell

Reputation: 13643

Why don't you just create two separate triggers?

CREATE TRIGGER [myUpdateTrigger] ON [myTable]
FOR UPDATE
AS
BEGIN

END

CREATE TRIGGER [myDeleteTrigger] ON [myTable]
FOR DELETE
AS
BEGIN

END

Upvotes: 5

Related Questions