I'd like to turn trigger recursion on/off in my SQL CLR trigger. According to http://www.devx.com/tips/Tip/30031, I have to call
EXEC sp_dboption '<name of db>', 'recursive triggers', 'true'/'false'
Is there a way to get to know what the current DB name is? When creating trigger, I ask users to choose one, but I don't want to write it in a table.
Regards,
There is a very simple way to find the name of the database in which the SQLCLR Trigger is being fired: just make a connection to the Context Connection and get the Database property. You don't even need to execute a query :-).
The following should work in all SQLCLR object types (Stored Procedure, Function, User-Defined Aggregate, User-Defined Type, and Trigger):
string _DatabaseName;
using (SqlConnection _Connection = new SqlConnection("Context Connection = true;"))
{
_Connection.Open();
_DatabaseName = _Connection.Database;
}
That's it! I just tried it in a SQLCLR Trigger and it works great.
Another thing to keep in mind for limiting Triggers firing other Triggers is the TRIGGER_NESTLEVEL function. This works better in T-SQL Triggers where the value of @@PROCID is available and contains the [object_id] of the Trigger. So in T-SQL Triggers you can limit the recursion of each trigger individually but still allow Triggers to fire other Triggers on other Tables.
In SQLCLR it can still be used, but without the name of the Trigger you can only limit all Triggers. Meaning, you can prevent any Trigger from firing any other Trigger on any Table, including on the same Table, but there is no way to limit the firing of only that same Trigger while allowing Triggers on other tables that might be modified by the Trigger in question. Just use a Context Connection and run SELECT TRIGGER_NESTLEVEL(); via SqlCommand.ExecuteScalar().
You know what the database is when you create the trigger...
CREATE TRIGGER etc
....
GO
DECLARE @db varchar(100)
SET @db = DB_NAME()
EXEC sp_dboption @db, 'recursive triggers', 'true'/'false'
I've found a better solution.
I have to avoid calling EXEC sp_dboption at all. Instead, I have to create a temp table as a flag "no recursion", then check existing of the table at the beginning of the trigger and exit, if the table exists.
Why temporary table?