You would find some useful links on web, if you search with the title of this blog post. Basically you cannot pass parameters to a Trigger because Triggers get fired automatically by the database engine, and you do not interact with them directly as they execute.
Putting it in another way, Stored Procedures and Functions etc. need to be invoked explicitly from T-Sql, therefore you can pass parameters to them, as you invoke them. But Triggers fire implicitly in response to external Sql execution, and you do not (and cannot) call them explicitly. So, there’s no way to pass parameters to them as you would pass to a Stored Procedure.
In some situations, it is really useful and required to have extra information available in Triggers than what is implicitly provided by the database engine through INSERTED and DELETED logical tables (e.g. the additional audit information that is normally required when using Triggers for audit trail and which is not directly available from the affected table(s)).
However, people have devised a couple of workarounds to overcome this.
- The first work-around takes advantage of the fact that you can access any database object in a Trigger. So basically, you store the information that should be available in the Trigger in a table and then access it in the trigger by querying that table.
- A lesser known but highly useful work-around is the use of CONTEXT_INFO provided by database engine (see this). You can store any information upto 128 bytes in a special Sql Server environment variable called CONTEXT_INFO and access it anytime later. This is session specific information and is not shared across sessions.
However, I recently resorted to the hybrid of 2 approaches. The information that I wanted to have accessible in the Trigger exceeded 128 bytes in size, so CONTEXT_INFO was not an option and the problem in storing in an external table was, how to recognize the information stored in the external table from within the Trigger (remember, there could be multiple simultaneous database sessions updating the same table, and all session would have the same Trigger firing on each change to the table, how would you associate the information stored in the external table for the current session).
As I said, a good use of both the above approaches simultaneously resolves the situation at hand. In a nut-shell, you store the desired information in the external table together with a unique key, like a GUID or an IDENTITY value. You then store this unique key in CONTEXT_INFO which you can then use in the Trigger to access the desired information.
Here’s how you would typically accomplish what has been said above:
You first create an additional table to hold the data to be accessible in the Trigger.
CREATE TABLE [dbo].[TriggerData]( [GUID] [uniqueidentifier] NOT NULL, [Data] [nvarchar](max) NULL ) ON [PRIMARY]
Then, before your Sql query to update data to the actual table, you store the desired additional information to be accessible in the Trigger to this table.
DECLARE @id uniqueidentifier; SET @id = NEWID(); INSERT INTO TriggerData VALUES (@id, 'My Custom Data');
Next, you associate the unique id to the current session.
DECLARE @context_info varbinary(100); SET @context_info = cast(@id as varbinary(100)); SET CONTEXT_INFO @context_info;
And finally you make desired changes to your table.
UPDATE Reservations SET Tax = Tax WHERE 1 = 1
Now, coming onto the Trigger, here’s how you would access this information in the Trigger:
CREATE TRIGGER [dbo].[Reservations_InsUpd_LogFares] ON [dbo].[Reservations] AFTER INSERT, UPDATE AS BEGIN DECLARE @id uniqueidentifier; SELECT @id = CAST(CONTEXT_INFO() as uniqueidentifier); DECLARE @data nvarchar(MAX); SELECT @data = Data FROM TriggerData WHERE [GUID] = @id; -- More sql statements, use @data for the external information. END
A bit of a work-around I agree, but this gives you virtually any session specific information you want to have accessible in the Triggers that is not available directly in the table on which the Trigger has been defined for. It would probably be best to move your table updation code that fires the Trigger to a Stored Procedure to avoid having to repeat the above process again and again.
A very important point to remember would be that you would need to incorporate some tolerance in the Trigger if the information it expects is not available through CONTEXT_INFO. Triggers fire implicitly (as mentioned above) whenever the underlying table changes on which the Trigger has been defined for (the Trigger can fire from Query analyzer, other triggers, Stored Procedures and direct sql statements etc). If you happen to update the table data directly without setting the CONTEXT_INFO, the trigger would still fire. So, you must keep always keep this in mind while using the above approach. Probably, a good thing to do would be to provide a Stored Procedure to update the underlying table, which provides the necessary information to the Trigger, and remove privileges for updating the table by direct Sql statements.
Also, you would need to clean-up the information stored in TriggerData. This can be done after your upation query and would be intuitive if done in a Stored Procedure after the data updation statement.
DELETE FROM TriggerData WHERE [GUID] = @id;