Ich muss alle Auslöser in der SQL Server-Datenbank mit dem Tabellennamen und dem Tabellenschema auflisten.
Ich bin fast da mit diesem:
SELECT trigger_name = name, trigger_owner = USER_NAME(uid),table_schema = , table_name = OBJECT_NAME(parent_obj),
isupdate = OBJECTPROPERTY( id, 'ExecIsUpdateTrigger'), isdelete = OBJECTPROPERTY( id, 'ExecIsDeleteTrigger'),
isinsert = OBJECTPROPERTY( id, 'ExecIsInsertTrigger'), isafter = OBJECTPROPERTY( id, 'ExecIsAfterTrigger'),
isinsteadof = OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger'),
[disabled] = OBJECTPROPERTY(id, 'ExecIsTriggerDisabled')
FROM sysobjects INNER JOIN sysusers ON sysobjects.uid = sysusers.uid
WHERE type = 'TR'
Ich brauche nur das Schema der Tabelle.
Hier ist ein Weg:
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'
EDIT: Kommentierte out-Verknüpfung mit Sysusern für die Abfrage, um auf AdventureWorks2008 zu arbeiten.
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'
EDIT 2 : Für 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'
Bitte schön.
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'
Ein paar Dinge hier ...
Ich sehe auch, dass Sie versucht haben, die Schema-Informationen der übergeordneten Tabellen abzurufen. Ich glaube, dafür müssten Sie die sysobjects-Tabelle auch mit sich selbst verknüpfen, damit Sie die Schema-Informationen für die übergeordnete Tabelle korrekt erhalten. Die obige Abfrage tut dies. Auch die Sysusers-Tabelle wurde in den Ergebnissen nicht benötigt, sodass Join entfernt wurde.
getestet mit SQL 2000, SQL 2005 und SQL 2008 R2
Ich hatte die gleiche Aufgabe vor kurzem und ich benutzte die folgenden für SQL Server 2012 DB. Verwenden Sie Management Studio und stellen Sie eine Verbindung zu der Datenbank her, die Sie durchsuchen möchten. Führen Sie dann das folgende Skript aus.
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'
Sie können den Körper der Trigger auch wie folgt erhalten:
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
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
Verwenden Sie diese Abfrage:
SELECT OBJECT_NAME(parent_id) as Table_Name, * FROM [Database_Name].sys.triggers
Es ist einfach und nützlich.
Und was denkst du darüber: Sehr kurz und ordentlich :)
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
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'
das funktioniert für mich
Verwenden Sie diese Abfrage:
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
das kann helfen.
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]
Das ist, was ich verwende (normalerweise eingepackt in etwas, das ich in Model stecke):
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
Wie Sie sehen, ist es ein bisschen mehr McGyver, aber ich denke, es lohnt sich:
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
(Scrollen Sie nach rechts, um die letzte und nützlichste Spalte zu sehen.)
Der oben genannte Code ist falsch wie gezeigt:
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
Wenn Sie nach ALL
-Triggern suchen, beachten Sie, dass MS-SQL sowohl SQL-basierte Trigger (sysobjects.type = 'TR'
) als auch CLR-basierte Trigger (sysobjects.type = 'TA'
) hat.
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
C # Cribs: Ich endete mit diesem super generischen Einzeiler. Hoffe, dass dies sowohl für das Originalposter als auch für Personen nützlich ist, die dieselbe Frage, die ich in Google eingegeben habe, gerade eingegeben haben:
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'
Abfrageeigenschaften:
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
Nekromanzierung.
Einfach posten, weil alle bisherigen Lösungen etwas unvollständig sind.
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
Eine Schwierigkeit besteht darin, dass der Text oder die Beschreibung Zeilenvorschübe enthält. Mein unbeholfener Kludge, um es etwas tabellarischer zu gestalten, besteht darin, der HTML
-Klausel ein SELECT
-Literal hinzuzufügen, alles in den Notizblock zu kopieren, mit einer HTML-Erweiterung zu speichern, in einem Browser zu öffnen, dann in eine Tabelle zu kopieren .Beispiel
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;
sie müssen möglicherweise immer noch mit Tabulatoren herumalbern, um die Beschreibung in ein Feld zu bekommen, aber es wird sich zumindest in einer Zeile befinden, was ich sehr hilfreich finde.
SELECT
OBJECT_NAME(PARENT_OBJECT_ID) AS PARENT_TABLE,
OBJECT_NAME(OBJECT_ID) TRIGGER_TABLE,
*
FROM
SYS.OBJECTS
WHERE TYPE = 'TR'