Sunday, May 19, 2013

What Operation Type Invoked a Trigger?

This was a question brought to my attention and I didn’t have an answer. After searching, I realized that there is no direct function for it. Hence I came up with following solution.

I used inserted and deleted virtual tables.

Statement

inserted

deleted

INSERT

rows just inserted

 

DELETE

 

rows just deleted

UPDATE

modified row contents

original row contents

With the above cases, I came up with following trigger.

CREATE TRIGGER trg_data_ins_del_upd
ON Data
FOR INSERT,DELETE,UPDATE
AS
BEGIN
DECLARE
@ins int ,@del int

SELECT
@ins = Count (*) From inserted
SELECT @del = Count (*) From deleted
IF @ins > 0 AND @del > 0
INSERT INTO Operation (Operation) VALUES ('Update')
ELSE IF @ins > 0
INSERT INTO Operation (Operation) VALUES ('Insert')
ELSE IF @del > 0
INSERT INTO Operation (Operation) VALUES ('Delete')
END

However, I feel in the above case, you better of having three separate triggers than loading everything to one trigger.

No comments:

Post a Comment