blocks|key|909711|text|select+objectproperty(object_id('TriggerName'),+'ExecIsTriggerDisabled')+|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|909712|1表示真，0明显表示假。|unstyled|909713|使用Jeff+O的查询并稍微修改一下|909714|SELECT++
+++++++TAB.name+as+Table_Name+
+++++,+TRIG.name+as+Trigger_Name
+++++,+TRIG.is_disabled++--or+objectproperty(object_id('TriggerName'),+'ExecIsTriggerDisabled')
FROM+[sys].[triggers]+as+TRIG+
inner+join+sys.tables+as+TAB+
on+TRIG.parent_id+=+TAB.object_id+|909715|或者将其添加为where子句。|909716|where+TRIG.is_disabled+=+0+--+or+1+depends+on+what+you+want|909717|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|R|8|@]|9|@]|A|$]]|$1|G|3|H|5|F|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|T|8|@]|9|@]|A|$B|C]]|$1|K|3|L|5|F|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$B|C]]|$1|O|3|-4|5|F|7|W|8|@]|9|@]|A|$]]]|P|$]]

<pre><code>select objectproperty(object_id('TriggerName'), 'ExecIsTriggerDisabled') 
</code></pre>

1 means true, 0 means false obviously

Use Jeff O's query and modify it a bit

<pre><code>SELECT 
 TAB.name as Table_Name 
 , TRIG.name as Trigger_Name
 , TRIG.is_disabled --or objectproperty(object_id('TriggerName'), 'ExecIsTriggerDisabled')
FROM [sys].[triggers] as TRIG 
inner join sys.tables as TAB 
on TRIG.parent_id = TAB.object_id 
</code></pre>

or add it as a where clause.

<pre><code>where TRIG.is_disabled = 0 -- or 1 depends on what you want
</code></pre>

blocks|key|446856|text|SELECT+*
FROM+sys.triggers
WHERE+is_disabled+=+0|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|446857|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>SELECT *
FROM sys.triggers
WHERE is_disabled = 0
</code></pre>

blocks|key|446138|text|SELECT+
+++++++TAB.name+as+Table_Name
+++++,+TRIG.name+as+Trigger_Name++
FROM+[sys].[triggers]+as+TRIG
inner+join+sys.tables+as+TAB
on+TRIG.parent_id+=+TAB.object_id|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|446139|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>SELECT 
 TAB.name as Table_Name
 , TRIG.name as Trigger_Name 
FROM [sys].[triggers] as TRIG
inner join sys.tables as TAB
on TRIG.parent_id = TAB.object_id
</code></pre>

blocks|key|446152|text|SELECT+
++++TAB.name+as+Table_Name,
++++TRIG.name+as+Trigger_Name,
++++Comments.Text+TriggerText
FROM+[sys].[triggers]+as+TRIG
++++Inner+Join+sys.tables+as+TAB+on+TRIG.parent_id+=+TAB.object_id
++++Inner+Join+syscomments+Comments+On+TRIG.object_id+=+Comments.id
WHERE
++++TRIG.is_disabled+=+0;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|446153|是完全解|unstyled|446154|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>SELECT 
 TAB.name as Table_Name,
 TRIG.name as Trigger_Name,
 Comments.Text TriggerText
FROM [sys].[triggers] as TRIG
 Inner Join sys.tables as TAB on TRIG.parent_id = TAB.object_id
 Inner Join syscomments Comments On TRIG.object_id = Comments.id
WHERE
 TRIG.is_disabled = 0;
</code></pre>

is complete solution

blocks|key|909763|text|select+*+from+sys.triggers|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|909764|这里是表的object_id，所以从sys.table中可以获得表名|unstyled|offset|length|style|CODE|909765|entityMap^0|0|I|9|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|N|8|@$G|O|H|P|I|J]]|9|@]|A|$]]|$1|K|3|-4|5|F|7|Q|8|@]|9|@]|A|$]]]|L|$]]

<pre><code>select * from sys.triggers
</code></pre>

Here object_id is for table so from joining <code>sys.table</code> you can get table name

blocks|key|909702|text|您可以查询sys.triggers视图。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|909703|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms188746.aspx^0|5|C|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

You can query the <a href="http://msdn.microsoft.com/en-us/library/ms188746.aspx" rel="nofollow">sys.triggers</a> view.

blocks|key|446184|text|select+so.name,+text
from+sysobjects+so,+syscomments+sc
where+type+=+'TR'
and+so.id+=+sc.id
and+text+like+'%25related_table_name%25'|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|446185|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>select so.name, text
from sysobjects so, syscomments sc
where type = 'TR'
and so.id = sc.id
and text like '%related_table_name%'
</code></pre>

blocks|key|909785|text|以下是在SSMS中对“数据库触发器”进行刷新时发生的查询。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|909786|SELECT
tr.name+AS+[Name],
'Server[@Name='+%2B+quotename(CAST(
++++++++serverproperty(N'Servername')
+++++++AS+sysname),'''')+%2B+']'+%2B+'/Database[@Name='+%2B+quotename(db_name(),'''')+%2B+']'+%2B+'/DdlTrigger[@Name='+%2B+quotename(tr.name,'''')+%2B+']'+AS+[Urn],
tr.is_disabled+AS+[IsEnabled]
FROM
sys.triggers+AS+tr
WHERE
(tr.parent_class+=+0)
ORDER+BY
[Name]+ASC|code-block|syntax|javascript|909787|使用它，我创建了一个包含数据库触发器的版本(它增强了可接受的答案)。|909788|注意左联接和合并检查。|909789|SELECT++
+++++++COALESCE(TAB.name,+'DATABASE')+as+TargetObjectName
+++++,+TRIG.name+as+Trigger_Name
+++++,+TRIG.is_disabled++--or+objectproperty(object_id('TriggerName'),+'ExecIsTriggerDisabled')
+++++--+select+*
FROM+[sys].[triggers]+as+TRIG+
left+join+sys.tables+as+TAB+
on+TRIG.parent_id+=+TAB.object_id+
WHERE
/*+(TRIG.parent_class+=+0)+and+*/
TRIG.is_disabled+=+0|909790|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|S|8|@]|9|@]|A|$E|F]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Here is the query that occurs when you do a refresh on "Database Triggers" in SSMS.

<pre><code>SELECT
tr.name AS [Name],
'Server[@Name=' + quotename(CAST(
 serverproperty(N'Servername')
 AS sysname),'''') + ']' + '/Database[@Name=' + quotename(db_name(),'''') + ']' + '/DdlTrigger[@Name=' + quotename(tr.name,'''') + ']' AS [Urn],
tr.is_disabled AS [IsEnabled]
FROM
sys.triggers AS tr
WHERE
(tr.parent_class = 0)
ORDER BY
[Name] ASC
</code></pre>

Using that, I've created a version (that enhances the accepted answer) to include the database triggers.

Note the left join and the COALESCE check.

<pre><code>SELECT 
 COALESCE(TAB.name, 'DATABASE') as TargetObjectName
 , TRIG.name as Trigger_Name
 , TRIG.is_disabled --or objectproperty(object_id('TriggerName'), 'ExecIsTriggerDisabled')
 -- select *
FROM [sys].[triggers] as TRIG 
left join sys.tables as TAB 
on TRIG.parent_id = TAB.object_id 
WHERE
/* (TRIG.parent_class = 0) and */
TRIG.is_disabled = 0
</code></pre>

blocks|key|446886|text|//使用数据库|type|unstyled|depth|inlineStyleRanges|entityRanges|data|446887|use+[your_database_Name]

Select+*+from+sys.triggers+where+is_disabled=0|code-block|syntax|javascript|446888|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

//Use Your Database

<pre><code>use [your_database_Name]

Select * from sys.triggers where is_disabled=0
</code></pre>

My application is based on a sql server db.

All customers has the same db except for customizations.

Some customizations include: new tables, modified tables, custom views, custom triggers...

When I run the software update some scripts are executed. Now I manually disable triggers and reenable after the scripts are done.

Anyway I would like automatically to disable all the triggers (that are enabled, may be some of them could be already disabled) and then reenable them at the end.

Not to reinvent the whell, how to do that?

How to get only the active triggers on the current db?

Once I got this I can programmatically create and run the

<pre><code>DISABLE TRIGGER triggername ON TABLENAME

ENABLE TRIGGER triggername ON TABLENAME
</code></pre>

How to get the list of active triggers on a database?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我的应用程序是基于sql server db的。除自定义外，所有客户都具有相同的数据库。一些自定义包括:新表、修改后的表、自定义视图、自定义触发器.当我运行软件更新时，会执行一些脚本。现在，我手动禁用触发器，并在脚本完成后重新启用。无论如何，我想自动禁用所有触发器(这些触发器已经启用，可能有些已经禁用)，然后在结束时重新启用它们。不是为了重新创造号角，是怎么做的？如何只获取当前db?上的活动触发器

问如何获取数据库中的活动触发器列表？
EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何获取数据库中的活动触发器列表？EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何获取数据库中的活动触发器列表？
EN