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;
I have written one trigger which calls my console application , when a value is inserted in the table .
I am passing exe of that application in trigger.
I want to pass one value from trigger to the application. Can u tell me how to do this?
How my application will get that value? What changes are required in the application?
i am working at a project and i have problem dealing with updating data tables in the moment of insertion.
My User Interface communicate with 3 or 4 tables, and i have 2 other tables in which some informations must be updated.
So using the Triggers as you had shown us can be a solution for my problem?
I was doing Google for this issue from yesterday and had no clue for how to solve the issue. I am designing an Audit trail for an application and wanted to have some column which allows me to do grouping of audit trail records. I was searching for a mechanism which allows me to set some group id in one trigger and access the same group id in another trigger. After going thru your article I implemented it and achieved the same using
Now I am able to do the grouping of records based on Id I set in
Thanks a Ton !
I had few questions :
I have consecutive update operation on multiple tables, I have chosen trigger for Audit trail :
1) what will happen if multiple user have update query on same table, I hope
CONTEXT_INFO value I set remains, untill both triggers are fired. i.e. Trigger1 on table1 and Trigger2 on table2
2) Does the value of
CONTEXT_INFO gets shared between multiple users if all of them update the records?
-> What if all users update same record as same time ?
-> What if all
usersupdate different record as same time ?
3) Do I need to use the BEGIN TRANSACTION …for this process …
UPDATE Query for Table 1 [This will eventually fire Trigger 1]
UPDATE Query for Table 2 [This will eventually fire Trigger 2] & so on …
1. Triggers can return a value
2. Triggers can be called explicitly from stored procedures
3. Triggers can be called explicitly from a function
4. Triggers cannot take input parameters