How to distinguish operation type in trigger?

#1

Hi,

I want to have all-in-one trigger, defined like this:

CREATE TRIGGER MyInsertDeleteUpdateHandler
ON MyTable
FOR DELETE, INSERT, UPDATE
AS
BEGIN
(...)
END

Now, how can I tell why this trigger was fired (what event caused
trigger to be fired) - was it DELETE, INSERT or UPDATE?

Is there something like this: @@event_type,
so I could do for example IF (@@event_type = DELETE) (...)

Of course I can create 3 triggers instead of 1, to be sure what event
fired my trigger.

I can also count records in _deleted_, _inserted_ tables or to do
JOINs with it. But, _inserted_ table is common for UPDATE and INSERT
events..

Any suggestions?
Thanks in advance.

Hubert

#2

Quote:

I can also count records in _deleted_, _inserted_ tables or to do
JOINs with it. But, _inserted_ table is common for UPDATE and INSERT
events..

However, the deleted table will be empty for when fired by INSERT. You can
determine the statement type as follows:

IF EXISTS(SELECT * FROM inserted)
IF EXISTS(SELECT * FROM deleted)
SELECT @event_type = 'update'
ELSE
SELECT @event_type = 'insert'
ELSE
IF EXISTS(SELECT * FROM deleted)
SELECT @event_type = 'delete'
ELSE
--no rows affected - cannot determine event
SELECT @event_type = 'unknown'

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Hubert Trzewik" <hubert.trzewik (AT) gmail (DOT) com> wrote

Quote:

Hi,

I want to have all-in-one trigger, defined like this:

CREATE TRIGGER MyInsertDeleteUpdateHandler
ON MyTable
FOR DELETE, INSERT, UPDATE
AS
BEGIN
(...)
END

Now, how can I tell why this trigger was fired (what event caused
trigger to be fired) - was it DELETE, INSERT or UPDATE?

Is there something like this: @@event_type,
so I could do for example IF (@@event_type = DELETE) (...)

Of course I can create 3 triggers instead of 1, to be sure what event
fired my trigger.

I can also count records in _deleted_, _inserted_ tables or to do
JOINs with it. But, _inserted_ table is common for UPDATE and INSERT
events..

Any suggestions?
Thanks in advance.

Hubert

#3

On May 31, 3:19 pm, Hubert Trzewik <hubert.trze... (AT) gmail (DOT) com> wrote:

Quote:

Hi,

I want to have all-in-one trigger, defined like this:

CREATE TRIGGER MyInsertDeleteUpdateHandler
ON MyTable
FOR DELETE, INSERT, UPDATE
AS
BEGIN
(...)
END

Now, how can I tell why this trigger was fired (what event caused
trigger to be fired) - was it DELETE, INSERT or UPDATE?

Is there something like this: @@event_type,
so I could do for example IF (@@event_type = DELETE) (...)

Of course I can create 3 triggers instead of 1, to be sure what event
fired my trigger.

I can also count records in _deleted_, _inserted_ tables or to do
JOINs with it. But, _inserted_ table is common for UPDATE and INSERT
events..

Any suggestions?
Thanks in advance.

Hubert

For Inserted : Rows are in inserted only
For Updated: Rows are in inserted and deleted
For deleted: Rows are in deleted only

#4

On May 31, 1:48 pm, M A Srinivas <masri... (AT) gmail (DOT) com> wrote:

Quote:

On May 31, 3:19 pm, Hubert Trzewik <hubert.trze... (AT) gmail (DOT) com> wrote:

Hi,

I want to have all-in-one trigger, defined like this:

CREATE TRIGGER MyInsertDeleteUpdateHandler
ON MyTable
FOR DELETE, INSERT, UPDATE
AS
BEGIN
(...)
END

Now, how can I tell why this trigger was fired (what event caused
trigger to be fired) - was it DELETE, INSERT or UPDATE?

Is there something like this: @@event_type,
so I could do for example IF (@@event_type = DELETE) (...)

Of course I can create 3 triggers instead of 1, to be sure what event
fired my trigger.

I can also count records in _deleted_, _inserted_ tables or to do
JOINs with it. But, _inserted_ table is common for UPDATE and INSERT
events..

Any suggestions?
Thanks in advance.

Hubert

For Inserted : Rows are in inserted only
For Updated: Rows are in inserted and deleted
For deleted: Rows are in deleted only

That's right. One test more and.. UPDATE is seen both in _inserted_
and _deleted_, so I can distinguish it from INSERT (and DELETE of
course).

Thanks a lot.

Hubert

#5

Why not just break up the action into three triggers? if there is
common code use a stored procedure got that part.

Hubert Trzewik wrote:

Quote:

Hi,

I want to have all-in-one trigger, defined like this:

CREATE TRIGGER MyInsertDeleteUpdateHandler
ON MyTable
FOR DELETE, INSERT, UPDATE
AS
BEGIN
(...)
END

Now, how can I tell why this trigger was fired (what event caused
trigger to be fired) - was it DELETE, INSERT or UPDATE?

Is there something like this: @@event_type,
so I could do for example IF (@@event_type = DELETE) (...)

Of course I can create 3 triggers instead of 1, to be sure what event
fired my trigger.

I can also count records in _deleted_, _inserted_ tables or to do
JOINs with it. But, _inserted_ table is common for UPDATE and INSERT
events..

Any suggestions?
Thanks in advance.

Hubert

#6

Seribus Dragon (Seribus.news (AT) seribus (DOT) com) writes:

Quote:

Why not just break up the action into three triggers? if there is
common code use a stored procedure got that part.

That can be problematic, if you need to refer to the inserted/deleted
tables, which you often do in triggers.

Most of my triggers are for INSERT and UPDATE, but I have a couple
that are for all three actions.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

How to distinguish operation type in trigger?

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

How to distinguish operation type in trigger? - 05-31-2007 , 06:19 AM

Re: How to distinguish operation type in trigger? - 05-31-2007 , 07:43 AM

Re: How to distinguish operation type in trigger? - 05-31-2007 , 07:48 AM

Re: How to distinguish operation type in trigger? - 05-31-2007 , 09:32 AM

Re: How to distinguish operation type in trigger? - 06-04-2007 , 02:48 PM

Re: How to distinguish operation type in trigger? - 06-04-2007 , 05:15 PM