Clever Geek Handbook

SQL Server 2005 trigger - how to safely determine if an UPDATE or DELETE has been fired?

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, is there a situation where the @OperationType is not populating correctly? EG: the data in the table is being modified by a bunch of UPDATE / DELETE statements, but the trigger doesn't fire once with each one?

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

0


source to share


2 answers


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
+4


source


Simple answer: No, there will not be a situation where the trigger cannot detect correctly (unless there are no changed rows).



The trigger will fire once for each statement, so it 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 should be better off using a couple of triggers.

+3


source






More articles:


All Articles