February 25, 2008 at 8:44 am
Can i create a trigger on a table to only fire if another table is inserted/updated to in the same transaction?
For example:
i have 2 tables, table1 and table2. I want to set a trigger to only insert into table1 if there is a statement to insert into table2 with a particular status in the same transaction. So, only allow data to be added to table1 where data is also entered into table2 with status 2. Problem being that i dont think you can access other "inserted" information from another table during trigger execution - if that made sense ???? :w00t:
I know i can do this through a stored proc fired through the app, but ideally want this to be a trigger if i can.....
Also, whilst i'm on, is there a way i can see whether a trigger from another table has fired during a transaction without obviously checking the data it has updated.....ie a last fired date or the likes???
any help much appreciated
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
February 26, 2008 at 1:47 am
If I read your question right, what you're looking for is an INSERT trigger on table2, and in that trigger you place an insert statement into table1 if conditions apply.
Makes sense?
/Kenneth
February 26, 2008 at 3:15 am
Look up http://msdn2.microsoft.com/en-us/library/ms189749.aspx - you could take an application lock in a trigger on the first table and then check for the presence of that lock in the second table.
Alternatively there's also a way to store a couple of hundred characters in some sort of session (connection) specific variable. You could flag in this with some known string and then check for it in the second trigger. I cannot remember the syntax.
WAIT! Found it. See http://msdn2.microsoft.com/en-us/library/ms180125.aspx
Whatever you do, document this dependency in your trigger code! 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply