SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Instead Of Triggers


Instead Of Triggers

Author
Message
David Jackson
David Jackson
SSChasing Mays
SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)

Group: General Forum Members
Points: 653 Visits: 1913
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


http://glossopian.co.uk/
"I don't know what I don't know."
Sergiy
Sergiy
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10588 Visits: 11966
No bad experience.
Except when trigger is badly written.

Like your one. Wink

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:


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



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.
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23969 Visits: 9730
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
Matt Miller (4)
Matt Miller (4)
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12541 Visits: 18584
Sergiy (12/10/2007)
No bad experience.
Except when trigger is badly written.

Like your one. Wink

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 receiveSmileTongue

----------------------------------------------------------------------------------
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?
Sergiy
Sergiy
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10588 Visits: 11966
Well, what's gonna happen when you execute

insert into real_table --will get posted to the current table regardless of type
select col_list
from inserted i


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.
Sergiy
Sergiy
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10588 Visits: 11966
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.
Matt Miller (4)
Matt Miller (4)
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12541 Visits: 18584
Sergiy (12/12/2007)
Well, what's gonna happen when you execute

insert into real_table --will get posted to the current table regardless of type
select col_list
from inserted i


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?
Sergiy
Sergiy
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10588 Visits: 11966
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.
Sergiy
Sergiy
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10588 Visits: 11966
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.
Smile
Matt Miller (4)
Matt Miller (4)
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12541 Visits: 18584
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....Wink

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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search