ANOTHER NEWBIE QUESTION

Can someone tell me the Transact-SQl equivalent of any of these:

IF INSERTING ...
IF DELETING ...
IF UPDATING ...

TIA
Allan M. Hart"Allan Hart" <allan.hart@.mnsu.edu> wrote in message news:<bqe8qk$1ch$1@.nitrogen.mnsu.edu>...
> Can someone tell me the Transact-SQl equivalent of any of these:
> IF INSERTING ...
> IF DELETING ...
> IF UPDATING ...
> TIA
> Allan M. Hart

I don't know what that syntax means, but I guess it's from trigger
code in another product, to identify the firing action? If so, then
one approach in MSSQL is to check for the existence of rows in the
inserted and deleted tables:

/* INSERT */
if exists (select * from inserted)
and not exists (select * from deleted)

/* DELETE */
if not exists (select * from inserted)
and exists (select * from deleted)

/* UPDATE */
if exists (select * from inserted)
and exists (select * from deleted)

Simon|||To add to Simon's response, you won't be able to infer the statement type
when no rows are affected by the statement firing the trigger.

/* UNKNOWN */
if not exists (select * from inserted)
and not exists (select * from deleted)

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Allan Hart" <allan.hart@.mnsu.edu> wrote in message
news:bqe8qk$1ch$1@.nitrogen.mnsu.edu...
> Can someone tell me the Transact-SQl equivalent of any of these:
> IF INSERTING ...
> IF DELETING ...
> IF UPDATING ...
> TIA
> Allan M. Hart|||Simon Hayes wrote:
> "Allan Hart" <allan.hart@.mnsu.edu> wrote in message news:<bqe8qk$1ch$1@.nitrogen.mnsu.edu>...
>>Can someone tell me the Transact-SQl equivalent of any of these:
>>
>>IF INSERTING ...
>>IF DELETING ...
>>IF UPDATING ...
>>
>>TIA
>>Allan M. Hart
>
> I don't know what that syntax means, but I guess it's from trigger
> code in another product, to identify the firing action? If so, then
> one approach in MSSQL is to check for the existence of rows in the
> inserted and deleted tables:
> Simon
It is likely from Oracle and it is based upon the abilty to write a
single trigger that contains within itself the ability to detect if the
the triggering actions as an insert, update, or delete.

--
Daniel Morgan
http://www.outreach.washington.edu/...oad/oad_crs.asp
http://www.outreach.washington.edu/...aoa/aoa_crs.asp
damorgan@.x.washington.edu
(replace 'x' with a 'u' to reply)