Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Instead Of Triggers Expand / Collapse
Author
Message
Posted Monday, December 10, 2007 8:13 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Saturday, July 12, 2014 6:44 AM
Points: 441, Visits: 1,799
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."
Post #431347
Posted Monday, December 10, 2007 1:22 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 3, 2014 4:30 PM
Points: 4,574, Visits: 8,366
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:

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.
Post #431508
Posted Wednesday, December 12, 2007 1:13 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:58 AM
Points: 13,872, Visits: 9,600
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
Post #432518
Posted Wednesday, December 12, 2007 3:16 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:23 PM
Points: 7,179, Visits: 15,768
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?
Post #432578
Posted Wednesday, December 12, 2007 3:34 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 3, 2014 4:30 PM
Points: 4,574, Visits: 8,366
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.
Post #432585
Posted Wednesday, December 12, 2007 3:39 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 3, 2014 4:30 PM
Points: 4,574, Visits: 8,366
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.
Post #432589
Posted Wednesday, December 12, 2007 4:45 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:23 PM
Points: 7,179, Visits: 15,768
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?
Post #432608
Posted Thursday, December 13, 2007 2:12 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 3, 2014 4:30 PM
Points: 4,574, Visits: 8,366
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.
Post #432708
Posted Thursday, December 13, 2007 6:15 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 3, 2014 4:30 PM
Points: 4,574, Visits: 8,366
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.
:)
Post #432794
Posted Thursday, December 13, 2007 6:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:23 PM
Points: 7,179, Visits: 15,768
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?
Post #432813
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse