473,327 Members | 1,952 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

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.
Jul 20 '05 #1
2 7147
>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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: R. Santiago | last post by:
I have a table cc_rd_user_questions with the following columns: NUM NUMBER (25) DEFAULT 1 NOT NULL, COMPANY_ID NUMBER (15) NOT NULL, PROJ_ID NUMBER (15) NOT NULL, SEQ ...
1
by: Lisa Tang | last post by:
Hi, I have a table t1 with a long raw column, and I need to create a view v1 with a long raw column being a function ff1 of the long raw column in t1. And I need to update t1 with reverse...
5
by: Leonardo Almeida | last post by:
This is the case.... I would like to learn the statement that make the relation between these tables. Why? Cos these are separated in two different databases and if a user make an update in a...
1
by: Leonardo Almeida | last post by:
This is the case.... I would like to learn the statement that make the relation between these tables. Why? Cos these are separated in two different databases and if a user make an update in a...
4
by: Rigs | last post by:
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...
3
by: Jim Archer | last post by:
Hi All... I'm been fighting this problem for a few days now, and it seems like it should be simple. But the solution has eluded me so far... I need to flag a record when it is updated or when...
5
by: bsandell | last post by:
Hi, I have a view that looks something like this - CREATE VIEW myview AS SELECT myudf(col1) as col1, col2, col3 FROM mytable The view has an 'INSTEAD OF' trigger on it to do the correct...
3
by: Shestine | last post by:
I am trying to add a column to a current table, with data in it. I am only learning, and i have no idea how to change this to make it work. Here is the script I have right now it, but what it does is...
1
by: rverghese | last post by:
I have a BEFORE UPDATE trigger on a table that calls the tsvector_update_trigger() built in function to update a tsvector field. Since this trigger is called for every update to the table, the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.