December 10, 2007 at 8:13 am
Any one got any thoughts on using instead of triggers?
I am naturally inclined not to use triggers of any kind, but I have been asked to implement a parallel run of a new posting routine.
To cut a long story short, we want in certain instances, depending on a the type of posting, (a table lookup), to post to a parallel posting table depending on the postings type.
begin psuedo code
Create trigger ins_my_table
on real_table
INSTEAD OF INSERT
AS
Begin
Insert into par_table
select col_list
from inserted i
inner join post_Type_lookup p
on i.doc_type = p.doc_type
from where do_it = 1
insert into real_table --will get posted to the current table regardless of type
select col_list
from inserted i
End
End psuedo code
So essentially I'm doing two inserts, some going to a table to be processed by the new routine at a later date.
I've never used instead of triggers. Any one got any bad experiences using them?
Dave Jackson
December 10, 2007 at 1:22 pm
No bad experience.
Except when trigger is badly written.
Like your one. 😉
You do insert inside of INSTEAD OF INSERT.
Besides it does not make much sense, if suddenly nested trigger are allowed - you are in trouble, man.
This will do the same thing but without any risk:
[Code]
Create trigger ins_my_table
on real_table
FOR INSERT
AS
Begin
Insert into par_table
select col_list
from inserted i
inner join post_Type_lookup p
on i.doc_type = p.doc_type
from where do_it = 1
End
[/Code]
Because trigger is a part of INSERT transaction if insert into par_table fails insert into real_table (firing the trigger) will be rolled back as well.
_____________
Code for TallyGenerator
December 12, 2007 at 1:13 pm
Instead of an "instead of" trigger, you should use a "for" or "after" trigger (they're the same thing).
Another option is, if the inserts into the main table are handled by a stored procedure, have the stored proc insert into both tables when appropriate, and don't use a trigger.
The advantage to triggers is they are difficult to bypass. Doesn't matter which proc you use, doesn't matter which front end application you use, the trigger fires.
The main disadvantage to triggers is they are easy to forget about. The business rules change, and the trigger keeps on enforcing the old rule, because nobody remembers it's there.
I've found them useful in certain, limited circumstances. Mainly for complex referential integrity issues.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 12, 2007 at 3:16 pm
Sergiy (12/10/2007)
No bad experience.Except when trigger is badly written.
Like your one. 😉
You do insert inside of INSTEAD OF INSERT.
Besides it does not make much sense, if suddenly nested trigger are allowed - you are in trouble, man.
Not to stir up a hornet's nest - but I just checked that, and in 2005, that's actually not true Sergiy. Nested is on by default, and just for good measure, I also turned recursive triggers on... and it only inserts one copy of the data.
Maybe I didn't do this right, but David's code SHOULD work. And it's essentially right out of BOL on instead of triggers...
Granted though - in this case - the AFTER makes most sense (the INSTEAD would make more sense if you were filtering the INSERTS on the real table, and inserting everything into some external table for example....)
All right - I'm now ready for the beating I am about to receive:):P
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 12, 2007 at 3:34 pm
Well, what's gonna happen when you execute
[Code]
insert into real_table --will get posted to the current table regardless of type
select col_list
from inserted i
[/Code]
inside of INSTEAD OF INSERT trigger?
It will call the same trigger again and execute 1st insert from the trigger again.
Then it it will come to insert into real_table and again - INSTEAD of inserting it will execute trigger code. And so on, 32 times.
Not sure if it will insert into real_table finally, but 1st insert will be executed 32 times.
Before whole transaction will be rolled back because of the nesting error.
_____________
Code for TallyGenerator
December 12, 2007 at 3:39 pm
Matt Miller (12/12/2007)
And it's essentially right out of BOL on instead of triggers...
Is it SQL2005 BOL?
Because I'm thinking about writing a book discussing errors in SQL2005 BOL.
Appears to be quite thick volume.
_____________
Code for TallyGenerator
December 12, 2007 at 4:45 pm
Sergiy (12/12/2007)
Well, what's gonna happen when you execute[Code]
insert into real_table --will get posted to the current table regardless of type
select col_list
from inserted i
[/Code]
inside of INSTEAD OF INSERT trigger?
It will call the same trigger again and execute 1st insert from the trigger again.
Then it it will come to insert into real_table and again - INSTEAD of inserting it will execute trigger code. And so on, 32 times.
Not sure if it will insert into real_table finally, but 1st insert will be executed 32 times.
Before whole transaction will be rolled back because of the nesting error.
That's not the behavior that's happening here. With both nested triggers AND recursive triggers on (and restarting the service just to make sure) - it fires once and only once. Successfully.
No - really. I've never tried it before, so perhaps this is new (always did AFTER triggers and not INSTEAD OF), but the fact remains - it does work...
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 13, 2007 at 2:12 am
Matt Miller (12/12/2007)
That's not the behavior that's happening here. With both nested triggers AND recursive triggers on (and restarting the service just to make sure) - it fires once and only once. Successfully.
Well, it's not the behaviour I remembered when I wrote self-recurring trigger last time (2004?)
It's either something is changed since then, or that was not INSTEAD OF trigger, or something else.
Need to run some tests to understand what's the mechanics behind it.
But probably a little bit later.
_____________
Code for TallyGenerator
December 13, 2007 at 6:15 am
From BOL:
if a trigger is defined as an INSTEAD OF INSERT trigger for a table, and the trigger executes an INSERT statement on the same table, the INSERT statement executed by the INSTEAD OF trigger does not call the trigger again. The INSERT executed by the trigger starts the process of performing constraint actions and firing any AFTER INSERT triggers defined for the table.
Appears I was wrong.
Probably it was AFTER trigger which taught me never insert inserted recordset again.
🙂
_____________
Code for TallyGenerator
December 13, 2007 at 6:52 am
Good thing you found that. I hadn't noticed that part, and was about to start a thread to figure that out. Like I said - I never used these guys (INSTEAD OF) before, and I was starting to wonder if I had broken recursion or something....;)
I was just tweaking it because I'd never managed to break SQL server in that particular way before....
Thanks for the reference.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 17, 2007 at 12:25 pm
The only time I found an Instead Of trigger useful was when I normalized a table by pulling out the Name field into its own table. I renamed the original table without the Name field and created a view with the same name as the original table that used only one of the rows from the name table for each row in the base table. The original table (before I normalized it) was used by an application that could insert, update or delete rows. I used the inserted and deleted tables populated by triggers to insert, update or delete from the appropriate table (base and/or name tables). Of course, you have to make sure that you code them for set based inserts, updates and deletes.
Steve
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply