?
Solved

update table in INSERT trigger without invoking seperate UPDATE trigger

Posted on 2005-03-14
7
Medium Priority
?
325 Views
Last Modified: 2008-02-26
I have a table that 2 seperate triggers.  one is an insert trigger, and one is an update trigger.

I need to perform an action only on insert, and I need to alter the inserted records.

I did update the base table within the insert trigger, but that fired the update trigger.  I don't wan't the update trigger to fire during the process

HELP!
0
Comment
Question by:ccrich
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 34

Accepted Solution

by:
arbert earned 1000 total points
ID: 13541904
The easiest way we've found around this scenario is to use a column that creates a flag.  Interrogate this flag column in the update trigger to see if an insert took place.
0
 
LVL 34

Expert Comment

by:arbert
ID: 13541920
You could also turn off the option to allow recursion on Triggers, but I'm not so sure that's a good idea--depends on if you need recursion or not:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_6nw3.asp
0
 
LVL 22

Expert Comment

by:JesterToo
ID: 13542138
If you're using MSSQL 2000 you could use an "instead of" trigger for the insert.  This will allow you to modify the data before it gets inserted.  The regular update trigger won't be fired.

0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 34

Expert Comment

by:arbert
ID: 13542179
Ahh, good call....
0
 

Author Comment

by:ccrich
ID: 13542676
1.  I don't have recursion turned on - but since I have a seperate update trigger - it fires (recursion would only help if I was doing an update within an update trigger.

2.  Instead of isn't really an option - since I want all the normal processing to happen - with a minor exception.

Finally - it appears that the flag is the best alternative (the first comment).  I will leave this open for a few more hours - to see if anyone else responds

Thanks
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 13546135
Just a suggestion ...

Instead of a column in the table for every row, that will have to be turned on and off (?), perhaps a separate table that indicates if that spid -- which should stay unique for the life of the triggers -- did the inserts.  For example:


CREATE TRIGGER ...
ON ...
AFTER INSERT
AS
INSERT INTO triggerControl (spid, tableName, code) VALUES(@@SPID, 'baseTable', 'I')
UPDATE ...


CREATE TRIGGER ...
ON ...
AFTER UPDATE
AS
DELETE  FROM triggerControl
WHERE spid = @@SPID AND tableName = 'baseTable' AND code = 'I'
IF @@ROWCOUNT > 0
    RETURN  --row found means UPDATE done by INSERT trigger, so ignore
0
 
LVL 22

Expert Comment

by:JesterToo
ID: 13546361
Using an "instead of" does not preclude any "normal" processing... it gives you much more control over the processing.  All that happens is you get a chance to check the data and make whatever alterations you need before inserting into the table.  You manually re-issue the INSERT from within the trigger after modifying the value(s).

   Here is a MS blurb on "instead of" triggers...  

http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part10/c3761.mspx

HTH,
Lynn
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question