SQL Server Performance

Write a Trigger with Parameters

Discussion in 'General Developer Questions' started by dineshasanka, Nov 28, 2005.

  1. dineshasanka Moderator

    I want to write a trigger with parameters.

    please let me know how can I do that


    Above is a question I got from a mail. My initial thought is NO. what is your thought on this.

    ----------------------------------------
    http://spaces.msn.com/members/dineshasanka
  2. Chappy New Member

    Youre right, a trigger cant accept parameters. Im not sure where he would expect to pass the parameters from anyway?

    Typically you would select any pertinent data you need, from other tables, inside the trigger. Either that or redesign to use stored procs instead of triggers if at all possible.
  3. Twan New Member

    The one thing that some people forget/aren't aware of is that the inserted and deleted tables contain the rows before/after snapshots

    Cheers
    Twan
  4. SQLDBcontrol New Member

    Intersting question. I'd be more interested to find out why someone would want to write a trigger that accepted parameters.


    quote:Originally posted by dineshasanka

    I want to write a trigger with parameters.

    please let me know how can I do that


    Above is a question I got from a mail. My initial thought is NO. what is your thought on this.

    ----------------------------------------
    http://spaces.msn.com/members/dineshasanka


    Karl Grambow

    www.sqldbcontrol.com
  5. FrankKalis Moderator

    I suspect people will find many reasons for that. [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br />...and ignore the fact that one should keep trigger logic as simple and short as possible since triggers are executed synchronously. One of the "best" example in this context is the famous question: "How to send mail from a trigger?" [xx(]<br /><br />I would really rethink the logic here.<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  6. Madhivanan Moderator

    >>Above is a question I got from a mail. My initial thought is NO. what is your thought on this.

    May be the mailer tries to simulate the stroed Procedure coding in Trigger

    Madhivanan

    Failing to plan is Planning to fail
  7. Adriaan New Member

    In that case, in addition to what Twan mentioned about people not being aware of the deleted and inserted snapshots, lots of people who do know about the snapshots are not aware that if a trigger fires for an action spanning multiple rows, then the snapshots contain multiple rows too.

    So if the original question is on how to send the data from the snapshots to a procedure, then I'm afraid it can only be done the ugly way: in the trigger, open a cursor on deleted or inserted, then loop through the cursor calling the procedure with the parameters you're retrieving from the cursor.

    Like Frank said, triggers are synchronous, so it would be irresponsible to implement such a trigger ...

    Anyway, the only valid reason for doing this stuff in triggers is when data is manipulated directly on the table. If you can force the use of only stored procedures to manipulate the data, then you can certainly define the whole follow-up processing in the stored procedures, instead of in triggers.

Share This Page