Any Column Updated / Inserted Trigger

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.>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|||Thanks to the both of you for your responses.
I will consider both. As Hugo indicated, I may need to re-think my
logic. 2 seperate triggers may make the code more encapsilated than
what I was orignally thinking.
Thanks,
-Rigs

Hugo Kornelis <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message news:<j67v709bohd8s7d475hmoja8jmns5r2gth@.4ax.com>...
> 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|||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)|||Thanks to the both of you for your responses.
I will consider both. As Hugo indicated, I may need to re-think my
logic. 2 seperate triggers may make the code more encapsilated than
what I was orignally thinking.
Thanks,
-Rigs

Hugo Kornelis <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message news:<j67v709bohd8s7d475hmoja8jmns5r2gth@.4ax.com>...
> 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