需要列出所有的触发器在SQL Server数据库表名和表's模式

小开

最佳答案

这里有一种方法:

SELECT
sysobjects.name AS trigger_name
,USER_NAME(sysobjects.uid) AS trigger_owner
,s.name AS table_schema
,OBJECT_NAME(parent_obj) AS table_name
,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate
,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete
,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert
,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter
,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof
,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled]
FROM sysobjects


INNER JOIN sysusers
ON sysobjects.uid = sysusers.uid


INNER JOIN sys.tables t
ON sysobjects.parent_obj = t.object_id


INNER JOIN sys.schemas s
ON t.schema_id = s.schema_id


WHERE sysobjects.type = 'TR'

< p > 编辑: 在AdventureWorks2008上注释掉连接到sysusers以供查询

SELECT
sysobjects.name AS trigger_name
,USER_NAME(sysobjects.uid) AS trigger_owner
,s.name AS table_schema
,OBJECT_NAME(parent_obj) AS table_name
,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate
,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete
,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert
,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter
,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof
,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled]
FROM sysobjects
/*
INNER JOIN sysusers
ON sysobjects.uid = sysusers.uid
*/
INNER JOIN sys.tables t
ON sysobjects.parent_obj = t.object_id


INNER JOIN sys.schemas s
ON t.schema_id = s.schema_id
WHERE sysobjects.type = 'TR'

编辑2:用于SQL 2000

SELECT
o.name AS trigger_name
,'x' AS trigger_owner
/*USER_NAME(o.uid)*/
,s.name AS table_schema
,OBJECT_NAME(o.parent_obj) AS table_name
,OBJECTPROPERTY(o.id, 'ExecIsUpdateTrigger') AS isupdate
,OBJECTPROPERTY(o.id, 'ExecIsDeleteTrigger') AS isdelete
,OBJECTPROPERTY(o.id, 'ExecIsInsertTrigger') AS isinsert
,OBJECTPROPERTY(o.id, 'ExecIsAfterTrigger') AS isafter
,OBJECTPROPERTY(o.id, 'ExecIsInsteadOfTrigger') AS isinsteadof
,OBJECTPROPERTY(o.id, 'ExecIsTriggerDisabled') AS [disabled]
FROM sysobjects AS o
/*
INNER JOIN sysusers
ON sysobjects.uid = sysusers.uid
*/
INNER JOIN sysobjects AS o2
ON o.parent_obj = o2.id


INNER JOIN sysusers AS s
ON o2.uid = s.uid


WHERE o.type = 'TR'

小开

给你。

    SELECT
[so].[name] AS [trigger_name],
USER_NAME([so].[uid]) AS [trigger_owner],
USER_NAME([so2].[uid]) AS [table_schema],
OBJECT_NAME([so].[parent_obj]) AS [table_name],
OBJECTPROPERTY( [so].[id], 'ExecIsUpdateTrigger') AS [isupdate],
OBJECTPROPERTY( [so].[id], 'ExecIsDeleteTrigger') AS [isdelete],
OBJECTPROPERTY( [so].[id], 'ExecIsInsertTrigger') AS [isinsert],
OBJECTPROPERTY( [so].[id], 'ExecIsAfterTrigger') AS [isafter],
OBJECTPROPERTY( [so].[id], 'ExecIsInsteadOfTrigger') AS [isinsteadof],
OBJECTPROPERTY([so].[id], 'ExecIsTriggerDisabled') AS [disabled]
FROM sysobjects AS [so]
INNER JOIN sysobjects AS so2 ON so.parent_obj = so2.Id
WHERE [so].[type] = 'TR'

这里有几件事…

我也看到你试图拉父表的模式信息，我相信为了这样做，你还需要加入sysobjects表本身，这样你就可以正确地获得父表的模式信息。上面的查询可以做到这一点。此外，结果中不需要sysusers表，因此Join已被删除。

使用SQL 2000, SQL 2005和SQL 2008 R2进行测试

小开

SELECT
ServerName   = @@servername,
DatabaseName = db_name(),
SchemaName   = isnull( s.name, '' ),
TableName    = isnull( o.name, 'DDL Trigger' ),
TriggerName  = t.name,
Defininion   = object_definition( t.object_id )


FROM sys.triggers t
LEFT JOIN sys.all_objects o
ON t.parent_id = o.object_id
LEFT JOIN sys.schemas s
ON s.schema_id = o.schema_id
ORDER BY
SchemaName,
TableName,
TriggerName

小开

我最近有同样的任务，我使用以下sql server 2012 db。使用management studio并连接到要搜索的数据库。然后执行以下脚本。

Select
[tgr].[name] as [trigger name],
[tbl].[name] as [table name]


from sysobjects tgr


join sysobjects tbl
on tgr.parent_obj = tbl.id


WHERE tgr.xtype = 'TR'

小开

你还可以像下面这样获得触发器体:

SELECT      o.[name],
c.[text]
FROM        sys.objects AS o
INNER JOIN  sys.syscomments AS c
ON      o.object_id = c.id
WHERE   o.[type] = 'TR'

小开

使用此查询:

    SELECT
DB_NAME() AS DataBaseName,
S.Name AS SchemaName,
T.name AS TableName,
dbo.SysObjects.Name AS TriggerName,
dbo.sysComments.Text AS SqlContent,
FROM dbo.SysObjects
INNER JOIN dbo.sysComments ON dbo.SysObjects.ID = dbo.sysComments.ID
INNER JOIN sys.tables AS T ON sysobjects.parent_obj = t.object_id
INNER JOIN sys.schemas AS S ON t.schema_id = s.schema_id
WHERE dbo.SysObjects.xType = 'TR'
AND dbo.SysObjects.Name LIKE 'Permit_AfterInsert' ---- <----- HERE

小开

这可能会有所帮助。

SELECT DISTINCT o.[name] AS [Table]
FROM    [sysobjects] o
JOIN    [sysobjects] tr
ON  o.[id] = tr.[parent_obj]
WHERE   tr.[type] = 'tr'
ORDER BY [Table]


Get a list of tables and all their triggers.


SELECT DISTINCT o.[name] AS [Table], tr.[name] AS [Trigger]
FROM    [sysobjects] o
JOIN    [sysobjects] tr
ON  o.[id] = tr.[parent_obj]
WHERE   tr.[type] = 'tr'
ORDER BY [Table], [Trigger]

小开

SELECT
sysobjects.name AS trigger_name   ,OBJECT_NAME(parent_obj) AS table_name ,s.name AS table_schema
,USER_NAME(sysobjects.uid) AS trigger_owner




,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate
,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete
,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert
,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter
,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof
,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled]
FROM sysobjects


INNER JOIN sysusers
ON sysobjects.uid = sysusers.uid


INNER JOIN sys.tables t
ON sysobjects.parent_obj = t.object_id


INNER JOIN sys.schemas s
ON t.schema_id = s.schema_id


WHERE sysobjects.type = 'TR'

这对我很有用

小开

使用这个查询:

SELECT OBJECT_NAME(parent_id) as Table_Name, * FROM [Database_Name].sys.triggers

它既简单又有用。

小开

上面的代码是错误的，如下所示:

SELECT
sysobjects.name AS trigger_name
--,USER_NAME(sysobjects.uid) AS trigger_owner
--,s.name AS table_schema
--,OBJECT_NAME(parent_obj) AS table_name
--,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate
--,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete
--,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert
--,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter
--,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof
--,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled]
FROM sysobjects
/*
INNER JOIN sysusers
ON sysobjects.uid = sysusers.uid
*/
INNER JOIN sys.tables t
ON sysobjects.parent_obj = t.object_id


INNER JOIN sys.schemas s
ON t.schema_id = s.schema_id
WHERE sysobjects.type = 'TR'
EXCEPT
SELECT OBJECT_NAME(parent_id) as Table_Name FROM sys.triggers

小开

这是我使用的(通常包装在模型中):

Select
[Parent] = Left((Case When Tr.Parent_Class = 0 Then '(Database)' Else Object_Name(Tr.Parent_ID) End), 32),
[Schema] = Left(Coalesce(Object_Schema_Name(Tr.Object_ID), '(None)'), 16),
[Trigger name] = Left(Tr.Name, 32),
[Type] = Left(Tr.Type_Desc, 3), -- SQL or CLR
[MS?] = (Case When Tr.Is_MS_Shipped = 1 Then 'X' Else ' ' End),
[On?] = (Case When Tr.Is_Disabled = 0 Then 'X' Else ' ' End),
[Repl?] = (Case When Tr.Is_Not_For_Replication = 0 Then 'X' Else ' ' End),
[Event] = Left((Case When Tr.Parent_Class = 0
Then (Select Top 1 Left(Te.Event_Group_Type_Desc, 40)
From Sys.Trigger_Events As Te
Where Te.Object_ID = Tr.Object_ID)
Else ((Case When Tr.Is_Instead_Of_Trigger = 1 Then 'Instead Of ' Else 'After ' End)) +
SubString(Cast((Select [text()] = ', ' + Left(Te.Type_Desc, 1) + Lower(SubString(Te.Type_Desc, 2, 32)) +
(Case When Te.Is_First = 1 Then ' (First)' When Te.Is_Last = 1 Then ' (Last)' Else '' End)
From Sys.Trigger_Events As Te
Where Te.Object_ID = Tr.Object_ID
Order By Te.[Type]
For Xml Path ('')) As Character Varying), 3, 60) End), 60)
-- If you like:
-- , [Get text with] = 'Select Object_Definition(' + Cast(Tr.Object_ID As Character Varying) + ')'
From
Sys.Triggers As Tr
Order By
Tr.Parent_Class, -- database triggers first
Parent -- alphabetically by parent

正如你所看到的，这是一个McGyver，但我认为这是值得的:

Parent                           Schema           Trigger name                     Type MS?  On?  Repl? Event
-------------------------------- ---------------- -------------------------------- ---- ---- ---- ----- -----------------------------------------
(Database)                       (None)           ddlDatabaseTriggerLog            SQL            X     DDL_DATABASE_LEVEL_EVENTS
Employee                         HumanResources   dEmployee                        SQL       X          Instead Of Delete
Person                           Person           iuPerson                         SQL       X          After Insert, Update
PurchaseOrderDetail              Purchasing       iPurchaseOrderDetail             SQL       X    X     After Insert
PurchaseOrderDetail              Purchasing       uPurchaseOrderDetail             SQL       X    X     After Update
PurchaseOrderHeader              Purchasing       uPurchaseOrderHeader             SQL       X    X     After Update
SalesOrderDetail                 Sales            iduSalesOrderDetail              SQL       X    X     After Insert, Update, Delete
SalesOrderHeader                 Sales            uSalesOrderHeader                SQL       X          After Update (First)
Vendor                           Purchasing       dVendor                          SQL       X          Instead Of Delete
WorkOrder                        Production       iWorkOrder                       SQL       X    X     After Insert
WorkOrder                        Production       uWorkOrder                       SQL       X    X     After Update

(向右滚动可以看到最后一列，也是最有用的一列)

小开

SELECT tbl.name as Table_Name,trig.name as Trigger_Name,trig.is_disabled
FROM [sys].[triggers] as trig inner join sys.tables as tbl on
trig.parent_id = tbl.object_id

小开

如果你正在寻找ALL触发器，请记住MS-SQL有基于sql的触发器(sysobjects.type = 'TR')和基于clr的触发器(sysobjects.type = 'TA')。

小开

你觉得这个怎么样?非常简洁:)

SELECT OBJECT_NAME(parent_id) Table_or_ViewNM,
name TriggerNM,
is_instead_of_trigger,
is_disabled
FROM sys.triggers
WHERE parent_class_desc = 'OBJECT_OR_COLUMN'
ORDER BY OBJECT_NAME(parent_id),
Name ;

小开

一个困难是文本或描述有换行。我的笨拙的拼凑，把它变成更表格的东西，是在SELECT子句中添加一个HTML文字，复制并粘贴到记事本，用html扩展保存，在浏览器中打开，然后复制并粘贴到电子表格。示例< / p >

SELECT obj.NAME AS TBL,trg.name,sm.definition,'<br>'
FROM SYS.OBJECTS obj
LEFT JOIN (SELECT trg1.object_id,trg1.parent_object_id,trg1.name FROM sys.objects trg1 WHERE trg1.type='tr' AND trg1.name like 'update%') trg
ON obj.object_id=trg.parent_object_id
LEFT JOIN (SELECT sm1.object_id,sm1.definition FROM sys.sql_modules sm1 where sm1.definition like '%suser_sname()%') sm ON trg.object_id=sm.object_id
WHERE obj.type='u'
ORDER BY obj.name;

你可能仍然需要使用标签将描述放到一个字段中，但至少它会在一行上，我发现这非常有帮助。

小开

    CREATE TABLE [dbo].[VERSIONS](
[ID] [uniqueidentifier] NOT NULL,
[DATE] [varchar](100) NULL,
[SERVER] [varchar](100) NULL,
[DATABASE] [varchar](100) NULL,
[USER] [varchar](100) NULL,
[OBJECT] [varchar](100) NULL,
[ACTION] [varchar](100) NULL,
[CODE] [varchar](max) NULL,
CONSTRAINT [PK_VERSIONS] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO


ALTER TABLE [dbo].[VERSIONS] ADD  CONSTRAINT [DF_VERSIONS_ID]  DEFAULT (newid()) FOR [ID]
GO




DROP TRIGGER [DB_VERSIONS_TRIGGER] ON ALL SERVER


CREATE TRIGGER [DB_VERSIONS_TRIGGER] ON ALL SERVER FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER, CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION, CREATE_VIEW, ALTER_VIEW,
DROP_VIEW, CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
SET NOCOUNT ON SET XACT_ABORT OFF;
BEGIN
TRY
DECLARE @DATA XML = EVENTDATA()
DECLARE @SERVER VARCHAR(100) = @DATA.value('(EVENT_INSTANCE/ServerName)[1]','VARCHAR(100)')
DECLARE @DATABASE VARCHAR(100) = @DATA.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'VARCHAR(100)')
DECLARE @USER VARCHAR(100) = @DATA.value('(/EVENT_INSTANCE/LoginName)[1]','VARCHAR(100)')
DECLARE @OBJECT VARCHAR(100) = @DATA.value('(EVENT_INSTANCE/ObjectName)[1]','VARCHAR(100)')
DECLARE @ACTION VARCHAR(100) = @DATA.value('(/EVENT_INSTANCE/EventType)[1]','VARCHAR(100)')
DECLARE @CODE VARCHAR(MAX) = @DATA.value('(/EVENT_INSTANCE//TSQLCommand)[1]','VARCHAR(MAX)' )


IF OBJECT_ID('DB_VERSIONS.dbo.VERSIONS') IS NOT NULL
BEGIN
INSERT INTO [DB_VERSIONS].[dbo].[VERSIONS]([SERVER], [DATABASE], [USER], [OBJECT], [ACTION], [DATE], [CODE]) VALUES (@SERVER, @DATABASE, @USER, @OBJECT, @ACTION, getdate(), ISNULL(@CODE, 'NA'))
END
END
TRY
BEGIN
CATCH
END
CATCH
RETURN

小开

我最终得到了这个超级通用的一行。希望这对原始海报和/或刚刚输入我在谷歌中做的相同问题的人都有用:

SELECT TriggerRecord.name as TriggerName,ParentRecord.name as ForTableName
FROM sysobjects TriggerRecord
INNER JOIN sysobjects ParentRecord ON TriggerRecord.parent_obj=ParentRecord.id
WHERE TriggerRecord.xtype='TR'

查询特点:

可用于任何SQL数据库(即初始目录)
自我解释
一个简单的陈述
可以直接粘贴到大多数语言的大多数IDE中

小开

SELECT
OBJECT_NAME(PARENT_OBJECT_ID) AS PARENT_TABLE,
OBJECT_NAME(OBJECT_ID) TRIGGER_TABLE,
*
FROM
SYS.OBJECTS
WHERE TYPE = 'TR'

小开

< p > Necromancing。
只是发布，因为到目前为止所有的解决方案都不够完整。< / p >

SELECT
sch.name AS trigger_table_schema
,systbl.name AS trigger_table_name
,systrg.name AS trigger_name
,sysm.definition AS trigger_definition
,systrg.is_instead_of_trigger






-- https://stackoverflow.com/questions/5340638/difference-between-a-for-and-after-triggers
-- Difference between a FOR and AFTER triggers?
-- CREATE TRIGGER trgTable on dbo.Table FOR INSERT,UPDATE,DELETE
-- Is the same as
-- CREATE TRIGGER trgTable on dbo.Table AFTER INSERT,UPDATE,DELETE
-- An INSTEAD OF trigger is different, and fires before and instead of the insert
-- and can be used on views, in order to insert the appropriate values into the underlying tables.
-- AFTER specifies that the DML trigger is fired only when all operations
-- specified in the triggering SQL statement have executed successfully.
-- All referential cascade actions and constraint checks also must succeed before this trigger fires.
-- AFTER is the default when FOR is the only keyword specified.
,CASE WHEN systrg.is_instead_of_trigger = 1 THEN 0 ELSE 1 END AS is_after_trigger


,systrg.is_not_for_replication
,systrg.is_disabled
,systrg.create_date
,systrg.modify_date


,CASE WHEN systrg.parent_class = 1 THEN 'TABLE' WHEN systrg.parent_class = 0 THEN 'DATABASE' END trigger_class




,CASE
WHEN systrg.[type] = 'TA' then 'Assembly (CLR) trigger'
WHEN systrg.[type] = 'TR' then 'SQL trigger'
ELSE ''
END AS trigger_type


-- https://dataedo.com/kb/query/sql-server/list-triggers
-- ,(CASE WHEN objectproperty(systrg.object_id, 'ExecIsUpdateTrigger') = 1
--      THEN 'UPDATE ' ELSE '' END
-- + CASE WHEN objectproperty(systrg.object_id, 'ExecIsDeleteTrigger') = 1
--      THEN 'DELETE ' ELSE '' END
-- + CASE WHEN objectproperty(systrg.object_id, 'ExecIsInsertTrigger') = 1
--      THEN 'INSERT' ELSE '' END
-- ) AS trigger_event


,
(
STUFF
(
(
SELECT
', ' + type_desc AS [text()]
-- STRING_AGG(type_desc, ', ') AS foo
FROM sys.events AS syse
WHERE syse.object_id = systrg.object_id
FOR XML PATH(''), TYPE
-- GROUP BY syse.object_id
).value('.[1]', 'nvarchar(MAX)')
, 1, 2, ''
)
) AS trigger_event_groups


-- ,CASE WHEN systrg.parent_class = 1 THEN 'TABLE' WHEN systrg.parent_class = 0 THEN 'DATABASE' END trigger_class


,'DROP TRIGGER "' + sch.name + '"."' + systrg.name + '"; ' AS sql
-- ,systrg.*
FROM sys.triggers AS systrg


LEFT JOIN sys.sql_modules AS sysm
ON sysm.object_id = systrg.object_id


-- sys.objects for view triggers
-- LEFT JOIN sys.objects AS systbl ON systbl.object_id = systrg.object_id


-- inner join if you only want table-triggers
LEFT JOIN sys.tables AS systbl ON systbl.object_id = systrg.parent_id


LEFT JOIN sys.schemas AS sch
ON sch.schema_id = systbl.schema_id


WHERE (1=1)


-- AND sch.name IS NOT NULL
-- AND sch.name IS NULL
-- AND sch.name = 'dbo'
-- And here, exclude some triggers with a certain naming schema
/*
AND
(
-- systbl.name IS NULL
-- OR
NOT
(
systrg.name = 'TRG_' + systbl.name  + '_INSERT_History'
OR
systrg.name = 'TRG_' + systbl.name  + '_UPDATE_History'
OR
systrg.name = 'TRG_' + systbl.name  + '_DELETE_History'
)
)
*/


ORDER BY
sch.name
,systbl.name
,systrg.name

小开

不需要与其他表连接…所有信息都可以从sys.objects中获取。

SELECT  name as trigger_name
, object_name(parent_obj) as tableName
, object_schema_name(parent_obj) as schemaName
,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate
,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete
,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert
,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter
,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof
,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled]
FROM    sysobjects s
WHERE s.type = 'TR'