Any Column Updated / Inserted Trigger

**RSMEINER** · Jul 20 '05, 03:27 AM

Re: Any Column Updated / Inserted Trigger

>Hi,[color=blue]
>
>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.
>
>[/color]

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** · Jul 20 '05, 03:27 AM

Re: Any Column Updated / Inserted Trigger

On 15 Apr 2004 13:01:40 -0700, Rigs wrote:
[color=blue]
>PS I know I could do this with 2 seperate triggers, but I'm trying to
>avoid that.[/color]

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)

Any Column Updated / Inserted Trigger

Any Column Updated / Inserted Trigger

Comment

Comment