Posted by Dan Guzman on 05/31/07 11:43

> 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@gmail.com> wrote in message
news:1180606751.757434.101410@m36g2000hse.googlegroups.com...
> 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
>

• Next in forum: Re: How to distinguish operation type in trigger?
• Prev in forum: Re: needed: 1 + null = 1 (Correction)
• Thread view: Re: How to distinguish operation type in trigger?

[Reply to this message]