Get Information About DML Triggers
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
This topic describes how to get information about DML triggers in SQL Server by using SQL Server Management Studio or Transact-SQL. This information can include the types of triggers on a table, the name of a trigger, its owner and the date it was created or modified. If the trigger was not encrypted when it was created, you obtain the definition of the trigger. You can use the definition to help you understand how a trigger affects the table up on which it is defined. Also, you can find out the objects that a specific trigger uses. With this information, you can identify the objects that affect the trigger if they are changed or deleted in the database.
In This Topic
Before you begin:
To get information about DML triggers, using:
Before You Begin
Security
Permissions
sys.sql.modules, sys.object, sys.triggers, sys.events, sys.trigger_events
The visibility of the metadata in catalog views is limited to securables that a user either owns, or on which the user was granted some permission. For more information, see Metadata Visibility Configuration.
OBJECT_DEFINITION, OBJECTPROPERTY, sp_helptext
Requires membership in the public role. The definition of user objects is visible to the object owner or grantees that have any one of the following permissions: ALTER, CONTROL, TAKE OWNERSHIP, or VIEW DEFINITION. These permissions are implicitly held by members of the db_owner, db_ddladmin, and db_securityadmin fixed database roles.
sys.sql_expression_dependencies
Requires VIEW DEFINITION permission on the database and SELECT permission on sys.sql_expression_dependencies for the database. By default, SELECT permission is granted only to members of the db_owner fixed database role. When SELECT and VIEW DEFINITION permissions are granted to another user, the grantee can view all dependencies in the database.
Using SQL Server Management Studio
To view the definition of a DML trigger
In Object Explorer, connect to an instance of Database Engine and then expand that instance.
Expand the database that you want, expand Tables, and then expand the table that contains the trigger for which you want to view the definition.
Expand Triggers, right-click the trigger you want, and then click Modify. The definition of the DML trigger appears in the query window.
To view the dependencies of a DML trigger
In Object Explorer, connect to an instance of Database Engine and then expand that instance.
Expand the database that you want, expand Tables, and then expand the table that contains the trigger and its dependencies that you want to view.
Expand Triggers, right-click the trigger you want, and then click View Dependencies.
In the Object Dependencies window, to view the objects that depend on the DML trigger, select Objects that depend on <DML trigger name>. The objects appear in the Dependencies area.
To view the objects on which the DML depends, select Objects on which <DML trigger name> depends. The objects appear in the Dependencies area. Expand each node to see all the objects.
To obtain information about an object that appears in the Dependencies area, click the object. In the Selected object field, information is provided in the Name, Type, and Dependency type boxes.
To close the Object Dependencies window, click OK.
Using Transact-SQL
To view the definition of a DML trigger
Connect to the Database Engine.
From the Standard bar, click New Query.
Copy and paste one of the following examples into the query window and click Execute. Each example shows how you can view the definition of the
iuPerson
trigger.
USE AdventureWorks2022;
GO
SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID(N'Person.iuPerson');
GO
USE AdventureWorks2022;
GO
SELECT OBJECT_DEFINITION (OBJECT_ID(N'Person.iuPerson')) AS ObjectDefinition;
GO
USE AdventureWorks2022;
GO
EXEC sp_helptext 'Person.iuPerson'
GO
To view the dependencies of a DML trigger
Connect to the Database Engine.
From the Standard bar, click New Query.
Copy and paste one of the following examples into the query window and click Execute. Each example shows how you can view the dependencies of
iuPerson
trigger.
USE AdventureWorks2022;
GO
SELECT OBJECT_NAME(referencing_id) AS referencing_entity_name,
o.type_desc AS referencing_desciption,
COALESCE(COL_NAME(referencing_id, referencing_minor_id), '(n/a)') AS referencing_minor_id,
referencing_class_desc, referenced_class_desc,
referenced_server_name, referenced_database_name, referenced_schema_name,
referenced_entity_name,
COALESCE(COL_NAME(referenced_id, referenced_minor_id), '(n/a)') AS referenced_column_name,
is_caller_dependent, is_ambiguous
FROM sys.sql_expression_dependencies AS sed
INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id
WHERE referencing_id = OBJECT_ID(N'Person.iuPerson');
GO
To view information about DML triggers in the database
Connect to the Database Engine.
From the Standard bar, click New Query.
Copy and paste one of the following examples into the query window and click Execute. Each example shows how you can view information about DML triggers (
TR
) in the database.
USE AdventureWorks2022;
GO
SELECT name, parent_id, create_date, modify_date, is_instead_of_trigger
FROM sys.triggers
WHERE type = 'TR';
GO
USE AdventureWorks2022;
GO
SELECT name, object_id, schema_id, parent_object_id, type_desc, create_date, modify_date, is_published
FROM sys.objects
WHERE type = 'TR';
GO
USE AdventureWorks2022;
GO
SELECT OBJECTPROPERTY(OBJECT_ID(N'Person.iuPerson'), 'ExecIsInsteadOfTrigger');
GO
To view information about events that fire a DML trigger
Connect to the Database Engine.
From the Standard bar, click New Query.
Copy and paste one of the following examples into the query window and click Execute. Each example shows how you can view the events that fire the
iuPerson
trigger.
USE AdventureWorks2022;
GO
SELECT object_id, type, type_desc, is_trigger_event, event_group_type, event_group_type_desc
FROM sys.events
WHERE object_id = OBJECT_ID('Person.iuPerson');
GO
USE AdventureWorks2022;
GO
SELECT object_id, type,is_first, is_last
FROM sys.trigger_events
WHERE object_id = OBJECT_ID('Person.iuPerson');
GO
See Also
CREATE TRIGGER (Transact-SQL)
DROP TRIGGER (Transact-SQL)
ENABLE TRIGGER (Transact-SQL)
DISABLE TRIGGER (Transact-SQL)
EVENTDATA (Transact-SQL)
sp_rename (Transact-SQL)
ALTER TRIGGER (Transact-SQL)
sp_help (Transact-SQL)
sp_helptrigger (Transact-SQL)
sys.triggers (Transact-SQL)
sys.trigger_events (Transact-SQL)
sys.sql_modules (Transact-SQL)
sys.assembly_modules (Transact-SQL)
sys.server_triggers (Transact-SQL)
sys.server_trigger_events (Transact-SQL)
sys.server_sql_modules (Transact-SQL)
sys.server_assembly_modules (Transact-SQL)
OBJECTPROPERTY (Transact-SQL)
OBJECT_DEFINITION (Transact-SQL)
Povratne informacije
https://aka.ms/ContentUserFeedback.
Kmalu na voljo: V letu 2024 bomo ukinili storitev Težave v storitvi GitHub kot mehanizem za povratne informacije za vsebino in jo zamenjali z novim sistemom za povratne informacije. Za več informacij si oglejte:Pošlji in prikaži povratne informacije za