Write a Trigger with Parameters

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.

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

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

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.