Instead Of Triggers

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » Instead Of Triggers

11 posts, Page 1 of 2

1 2 »»

Instead Of Triggers

Rate Topic

Display Mode

Topic Options

Author

Message

David Jackson

Posted Monday, December 10, 2007 8:13 AM

SSC-Addicted

Group: General Forum Members
Last Login: Friday, March 08, 2013 11:15 AM
Points: 440, Visits: 1,785

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

Sergiy

Posted Monday, December 10, 2007 1:22 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:01 AM
Points: 4,557, Visits: 8,237

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

GSquared

Posted Wednesday, December 12, 2007 1:13 PM

SSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:45 PM
Points: 15,442, Visits: 9,572

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

Matt Miller (#4)

Posted Wednesday, December 12, 2007 3:16 PM

SSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:45 PM
Points: 7,002, Visits: 13,999

Sergiy (12/10/2007)

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

Sergiy

Posted Wednesday, December 12, 2007 3:34 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:01 AM
Points: 4,557, Visits: 8,237

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

Sergiy

Posted Wednesday, December 12, 2007 3:39 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:01 AM
Points: 4,557, Visits: 8,237

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

Matt Miller (#4)

Posted Wednesday, December 12, 2007 4:45 PM

SSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:45 PM
Points: 7,002, Visits: 13,999

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

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

Sergiy

Posted Thursday, December 13, 2007 2:12 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:01 AM
Points: 4,557, Visits: 8,237

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

Sergiy

Posted Thursday, December 13, 2007 6:15 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:01 AM
Points: 4,557, Visits: 8,237

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

Matt Miller (#4)

Posted Thursday, December 13, 2007 6:52 AM

SSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:45 PM
Points: 7,002, Visits: 13,999

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 »

11 posts, Page 1 of 2

1 2 »»

Permissions