Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 328
  • Last Modified:

update table in INSERT trigger without invoking seperate UPDATE trigger

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
ccrich
Asked:
ccrich
1 Solution
 
arbertCommented:
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
 
arbertCommented:
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
 
JesterTooCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
arbertCommented:
Ahh, good call....
0
 
ccrichAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
JesterTooCommented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now