Any Column Updated / Inserted Trigger

[Rigs]

Hi,

I'm a SQL Server newbie, so I'd appreciate if someone would tell me if
this is possible. I'm running SQL Server 2000 on Win2k Server

I have one table with a large number of columns. I have two pieces of
logic that I'd like to execute depending upon whether an insert or an
update statement was executed on that table. I'd prefer this
execution to occur from within a single trigger. If a row is
inserted, then I would like to execute logic A. If ANY column in the
table is updated, then I'd like logic B to be executed.

Is it possible to just determine if only "insert" or an "update"
ocurred from within the a single Trigger, without specifying each
individual column name? (I.E. not saying IF udpate(col1) or
update(col2) or ect...) Is it possible to just perform a check on the
process that occurred, irregardless of column? Like If INSERTED =
TRUE then execute insert logic. If UPDATED = TRUE, then run the
updated logic. I would like for all of this code to be stored within
the same trigger.

If anyone can provide some sample code on how to do this, if at all
possible, I would be much appreciative.

Thanks,
-Rigs

PS I know I could do this with 2 seperate triggers, but I'm trying to
avoid that.

[RSMEINER]

>Hi,

I'm a SQL Server newbie, so I'd appreciate if someone would tell me if
this is possible. I'm running SQL Server 2000 on Win2k Server

I have one table with a large number of columns. I have two pieces of
logic that I'd like to execute depending upon whether an insert or an
update statement was executed on that table. I'd prefer this
execution to occur from within a single trigger. If a row is
inserted, then I would like to execute logic A. If ANY column in the
table is updated, then I'd like logic B to be executed.

Is it possible to just determine if only "insert" or an "update"
ocurred from within the a single Trigger, without specifying each
individual column name? (I.E. not saying IF udpate(col1) or
update(col2) or ect...) Is it possible to just perform a check on the
process that occurred, irregardless of column? Like If INSERTED =
TRUE then execute insert logic. If UPDATED = TRUE, then run the
updated logic. I would like for all of this code to be stored within
the same trigger.

If anyone can provide some sample code on how to do this, if at all
possible, I would be much appreciative.

Thanks,
-Rigs

PS I know I could do this with 2 seperate triggers, but I'm trying to
avoid that.

Heres a real quick and dirty trigger example

CREATE TRIGGER trgtblClients ON tblClients
FOR INSERT, DELETE, UPDATE AS

DECLARE @ChgInsert CHAR(1)
DECLARE @ChgDelete CHAR(1)
DECLARE @ChgCode CHAR(1)
SET @ChgInsert = 'N'
SET @ChgDelete = 'N'
SET @ChgCode = 'N'
IF exists(select top 1 FROM inserted)
SET @ChgInsert = 'Y'

If exists(select top 1 from deleted)
SET @ChgDelete = 'Y'

/* Check for a insert */
IF @ChgInsert = 'Y' AND @ChgDelete = 'N'
Begin
SET @ChgCode = 'I'
End

/* Check for a change */
IF @ChgInsert = 'Y'AND @ChgDelete = 'Y'
Begin
SET @ChgCode = 'C'
End

/* Check for a delete */
IF @ChgInsert = 'N' AND @ChgDelete = 'Y'
Begin
SET @ChgCode = 'D'
End

IF @ChgCode = 'C'
BEGIN
/* DO YOUR CHANGE PROCESSING HERE */
END
IF @ChgCode = 'I'
BEGIN
/* DO YOUR INSERT PROCESSING HERE */

END
IF @ChgCode = 'D'
BEGIN
/* DO YOUR DELETE PROCESSING HERE */
END


Randy
http://members.aol.com/rsmeiner

[Hugo Kornelis]

On 15 Apr 2004 13:01:40 -0700, Rigs wrote:

PS I know I could do this with 2 seperate triggers, but I'm trying to
avoid that.

Hi Rigs,

Why are you trying to avoid using 2 seperate triggers?

I see a case for combining insert and update triggers if use need to
execute the SAME code on insert and on update. But since you have to
execute different code for each case, I'd think that using two
triggers provides a better documented system that's easier to
understand and easier to maintain. And your performance will improve
as well (allthough so little that you won't notice).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)