SQL CLR 触发器:上下文 DB 的名称

发布于 2024-09-12 00:41:08 字数 323 浏览 6 评论 0原文

我想在 SQL CLR 触发器中打开/关闭触发器递归。根据 http://www.devx.com/tips/Tip/30031,我必须打电话

EXEC sp_dboption '<name of db>', 'recursive triggers', 'true'/'false'

有没有办法知道当前的数据库名称是什么?创建触发器时,我要求用户选择一个,但我不想将其写在表中。

问候,

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,

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

夜访吸血鬼 2024-09-19 00:41:08

有一种非常简单的方法可以找到正在触发 SQLCLR 触发器的数据库的名称:只需建立到上下文连接的连接并获取Database 属性即可。您甚至不需要执行查询:-)。

以下内容应该适用于所有 SQLCLR 对象类型(存储过程、函数、用户定义聚合、用户定义类型和触发器):

string _DatabaseName;

using (SqlConnection _Connection = new SqlConnection("Context Connection = true;"))
{
    _Connection.Open();
    _DatabaseName = _Connection.Database;
}

就是这样!我刚刚在 SQLCLR 触发器中尝试过它,效果很好。


限制触发器触发其他触发器时要记住的另一件事是 TRIGGER_NESTLEVEL功能。这在 T-SQL 触发器中效果更好,其中 @@PROCID 的值可用并且包含触发器的 [object_id]。因此,在 T-SQL 触发器中,您可以单独限制每个触发器的递归,但仍然允许触发器在其他表上触发其他触发器。

在SQLCLR中它仍然可以使用,但是如果没有触发器的名称,您只能限制所有触发器。这意味着,您可以防止任何触发器在任何表(包括同一个表)上触发任何其他触发器,但无法限制触发同一个触发器,同时允许在其他表上触发可能会被相关触发器修改。只需使用上下文连接并通过 SqlCommand.ExecuteScalar() 运行 SELECT TRIGGER_NESTLEVEL();

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().

旧梦荧光笔 2024-09-19 00:41:08

当您创建触发器时您就知道数据库是什么......

CREATE TRIGGER etc
....
GO
DECLARE @db varchar(100)
SET @db = DB_NAME()
EXEC sp_dboption @db, 'recursive triggers', 'true'/'false'

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'
天邊彩虹 2024-09-19 00:41:08

我找到了更好的解决方案。

我必须完全避免调用 EXEC sp_dboption 。相反,我必须创建一个临时表作为“无递归”标志,然后在触发器开头检查表是否存在,如果表存在则退出。

为什么是临时表?

  1. 它在会话结束时被杀死。无需重置标志(在特殊情况下),这是避免触发器永久关闭所必需的。
  2. AFAIK,它是为每个连接独立创建和终止的。所以,如果用户同时更改数据,就不会产生冲突(这对于 EXEC sp_dboption 来说是不可避免的)。

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).
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文