我怎样才能获得“外部名称”？ SQL CLR 触发器？

发布于 2024-09-24 08:25:39 字数 361 浏览 2 评论 0原文

我使用以下 SQL 创建了一个 SQL CLR 触发器：

GO
CREATE TRIGGER AuditAccountsTable
   ON [dbo].[Accounts]
   FOR INSERT,DELETE,UPDATE
AS 
EXTERNAL NAME namespace.Triggers.AuditTrigger

我正在尝试查询：

select * from sys.triggers

有没有办法通过在数据库中查询来找到触发器上的 EXTERNAL NAME namespace.Triggers.AuditTrigger ？

原文

I have created a SQL CLR trigger with the follow SQL:

GO
CREATE TRIGGER AuditAccountsTable
   ON [dbo].[Accounts]
   FOR INSERT,DELETE,UPDATE
AS 
EXTERNAL NAME namespace.Triggers.AuditTrigger

I am trying to query:

select * from sys.triggers

Is there a way to find the: EXTERNAL NAME namespace.Triggers.AuditTrigger on the trigger from querying in the DB?

分享到QQ

分享到微博

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

发布评论

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

多像笑话 2024-10-01 08:25:39

我不能确定，因为我没有地方可以测试这个，但是下面返回的文本列是否能让您接近您正在寻找的内容？

select t.name, c.text
    from sys.triggers t
        inner join sys.syscomments c
            on t.object_id = c.id
    where t.type_desc = 'CLR_TRIGGER'

I can't be sure as I don't have a place to test this, but does the text column returned below get you close to what you're looking for?

select t.name, c.text
    from sys.triggers t
        inner join sys.syscomments c
            on t.object_id = c.id
    where t.type_desc = 'CLR_TRIGGER'

回复收藏 0 原文

猫七 2024-10-01 08:25:39

与存储过程和函数等 T-SQL“模块”不同，SQLCLR T-SQL 包装对象的 CREATE 语句不存储在数据库中。这就是为什么您无法通过 sys.sql_modules 访问它们，OBJECT_DEFINITION，或已弃用的-SQL-Server-2005-and-should-不使用 sys.syscomments。这就是为什么 SQLCLR 存储过程和函数需要将其参数默认值存储在 sys 中。参数

相反，SQLCLR T-SQL 包装对象的 CREATE 语句是从元数据推断出来的，就像索引、主键、外键等一样。

您可以获取以下内容的所有部分：来自以下查询的 CREATE TRIGGER 语句：

SELECT OBJECT_SCHEMA_NAME(st.[object_id]) AS [SchemaName],
       st.[name] AS [TriggerName],
       OBJECT_SCHEMA_NAME(st.parent_id) AS [ParentSchemaName],
       OBJECT_NAME(st.parent_id) AS [ParentName],
       st.is_instead_of_trigger,
       SUBSTRING((
         SELECT N', ' + ste.[type_desc]
         FROM   sys.trigger_events ste
         WHERE  ste.[object_id] = st.[object_id]
         FOR XML PATH ('')
       ), 3, 500) AS [Actions],
       QUOTENAME(sa.name) AS [AssemblyName],
       QUOTENAME(sam.assembly_class) AS [AssemblyClass],
       QUOTENAME(sam.assembly_method) AS [AssemblyMethod]
FROM   sys.triggers st
INNER JOIN  sys.assembly_modules sam
        ON  sam.[object_id] = st.[object_id]
INNER JOIN  sys.assemblies sa
        ON  sa.[assembly_id] = sam.[assembly_id]
WHERE  st.parent_class = 1; --- OBJECT_OR_COLUMN

Unlike T-SQL "modules" such as Stored Procedures and Functions, the SQLCLR T-SQL wrapper objects do not have their CREATE statements stored in the database. This is why you cannot access them via sys.sql_modules, OBJECT_DEFINITION, or the deprecated-since-SQL-Server-2005-and-should-not-be-used sys.syscomments. This is why SQLCLR Stored Procedures and Functions need to have their parameter default values stored in sys.parameters

Instead, CREATE statements for SQLCLR T-SQL wrapper objects are inferred from meta-data, just like Indexes, Primary Keys, Foreign Keys, etc.

You can get all of the parts of the CREATE TRIGGER statement from the following query:

SELECT OBJECT_SCHEMA_NAME(st.[object_id]) AS [SchemaName],
       st.[name] AS [TriggerName],
       OBJECT_SCHEMA_NAME(st.parent_id) AS [ParentSchemaName],
       OBJECT_NAME(st.parent_id) AS [ParentName],
       st.is_instead_of_trigger,
       SUBSTRING((
         SELECT N', ' + ste.[type_desc]
         FROM   sys.trigger_events ste
         WHERE  ste.[object_id] = st.[object_id]
         FOR XML PATH ('')
       ), 3, 500) AS [Actions],
       QUOTENAME(sa.name) AS [AssemblyName],
       QUOTENAME(sam.assembly_class) AS [AssemblyClass],
       QUOTENAME(sam.assembly_method) AS [AssemblyMethod]
FROM   sys.triggers st
INNER JOIN  sys.assembly_modules sam
        ON  sam.[object_id] = st.[object_id]
INNER JOIN  sys.assemblies sa
        ON  sa.[assembly_id] = sam.[assembly_id]
WHERE  st.parent_class = 1; --- OBJECT_OR_COLUMN

回复收藏 0 原文

~没有更多了~