By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,058 Members | 1,209 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,058 IT Pros & Developers. It's quick & easy.

Any Column Updated / Inserted Trigger

P: n/a
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.
Jul 20 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
>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
Jul 20 '05 #2

P: n/a
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)
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.