Is it possible to pass a parameter to trigger in SQL server database?

Question

Table can have trigger on insert/delete/update. Trigger will be fired internally by DB engine.

Is it possible to pass a parameter to trigger in SQL server database like StoreProcedure?

Answer 1

Indirectly via CONTEXT_INFO(). See Using Session Context Information. The fact that your trigger needs extra state is always a code smell.

Answer 2

Nope.

See: http://www.eggheadcafe.com/community/aspnet/13/10006230/pass-values-in-trigger.aspx

Answer 3

You can kludge it. Create a table specifically designed to hold the parameter information, then place data into the the parameter table before performing the operation that will initiate the trigger. Code the trigger to read from the parameter table.

That was the carrot, here is the stick:

1. This will complicate your triggers. You will need to account for the potential of missing paramters, and you will have to provide cleanup for the parameters.
2. You are creating a dependency with this technique, so it will only work with processes that are coded to use this parameter passing technique. So if your data is modified by more than just your application, this won't work.