SQL CLR trigger: name of the context DB

Question

Hello all,

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,

Answer 1

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'

Answer 2

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?

1. It's being killed at the end of the session. No need to reset the flag (in exceptional situation), which is necessary otherwise to avoid trigger being off permanently.
2. AFAIK, it's being created and killed independently for every connection. So, if the user changes data the same time, there will be no conflict (which is inevitable for EXEC sp_dboption).