Re: How to distinguish operation type in trigger?
- From: "Dan Guzman" <guzmanda@xxxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 31 May 2007 11:43:43 GMT
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@xxxxxxxxx> wrote in message news:1180606751.757434.101410@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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
.
- References:
- How to distinguish operation type in trigger?
- From: Hubert Trzewik
- How to distinguish operation type in trigger?
- Prev by Date: Re: needed: 1 + null = 1 (Correction)
- Next by Date: Re: How to distinguish operation type in trigger?
- Previous by thread: How to distinguish operation type in trigger?
- Next by thread: Re: How to distinguish operation type in trigger?
- Index(es):
Relevant Pages
|
