SQL Server 2005 trigger - how to safely determine

Question

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?

Answer 1

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

Answer 2

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.