sys.trigger_events - Event List of an Existing Trigger in SQL Server

Q

How To See the Event List of an Existing Trigger using sys.trigger_events in SQL Server?

✍: FYIcenter.com

A

If what are the DML events an existing trigger is handling, you can use the catalog view, sys.trigger_events. You need to join sys.trigger_events and sys.triggers to get a better list as shown in this tutorial example:

USE FyiCenterData
GO

SELECT t.name, e.type, e.type_desc
FROM sys.trigger_events AS e, sys.triggers AS t
WHERE e.object_id = t.object_id
GO
name           type   type_desc
-------------- ------ ---------
dml_message    1      INSERT
dml_message    2      UPDATE
dml_message    3      DELETE
new_user       1      INSERT
(4 row(s) affected)

The list clearly shows that dml_message handles 3 events: INSERT, UPDATE and DELETE.

⇒Creating and Managing Triggers in SQL Server

⇒⇒SQL Server Database Tutorials

2016-10-24, 1207👍, 0💬

sys.sql_modules - Getting Trigger Definitions Back in SQL Server
How To Get the Definition of a Trigger Back in SQL Server? If you want get the definition of an existing trigger back from the SQL Server, you can use the catalog view called sys.sql_modules, which stores definitions of views, stored procedures, and triggers. The sys.sql_modules holds trigger defini... 2016-10-24, 1139👍, 0💬

"DISABLE TRIGGER" - Disabling Triggers in SQL Server
How To Disable Triggers using "DISABLE TRIGGER" in SQL Server? If want to stop the execution of an existing trigger temporarily, you can use the "DISABLE TRIGGER" statement to disable it. The disabled trigger will be kept in the database. If you want to resume the execution of a disabled trigger, yo... 2016-10-24, 811👍, 0💬

"INSTEAD OF" - Overriding DML Statements with Triggers in SQL Server
How To Override DML Statements with Triggers in SQL Server? Sometime, you may want to implement some business logics in a DML trigger to cancel the DML statement. For example, you may want to check the new email address format provided by the UPDATE statement. If the email address is invalid, you to... 2016-10-22, 752👍, 0💬

Creating Triggers for INSERT Statements Only in SQL Server
How To Create a Trigger for INSERT Only in SQL Server? The trigger, dml_message, provided in previous tutorials was defined to handle all 3 types of DML statements, INSERT, UPDATE, and DELETE. If you do not want the trigger to handle all 3 types of DML statements, you can list only 1 or 2 of the sta... 2016-10-24, 743👍, 0💬

"INSERTED" - New Record of an DML Event Instance in SQL Server
How To Access the Inserted Record of an Event in SQL Server? When a DML event occurs, SQL Server will prepare a temporary table called "INSERTED", which contains the new record of the affected row, which is: A copy of the inserted row for an INSERT statement. A copy of the updated row for an UPDATE ... 2016-10-24, 739👍, 0💬

Triggers with Multiple Affected Rows in SQL Server
What Happens to a Trigger with Multiple Affected Rows in SQL Server? If there is only one row affected by a DML statement, we know that the DML trigger will be executed once. But how many times the DML trigger will be executed if the DML statement resulted multiple affected rows? The answer is still... 2016-10-24, 707👍, 0💬

Improving the Trigger to Handle NULL Values in SQL Server
How To Improve the Trigger to Handle NULL Values in SQL Server? When a NULL value is concatenated with a string, the result will be a null value. So if you want the trigger to properly report NULL values, you need to enhance the trigger as shown in the following tutorial example: USE FyiCenterData; ... 2016-10-24, 706👍, 0💬

"DELETED" - Old Record of an DML Event Instance in SQL Server
How To Access the Deleted Record of an Event in SQL Server? When a DML event occurs, SQL Server will prepare a temporary table called "DELETED", which contains the old record of the affected row, which is: A copy of the deleted row for a DELETE statement. A copy of the row to be updated for an UPDAT... 2016-10-24, 689👍, 0💬

"ALTER TRIGGER" - Modifying Existing Triggers in SQL Server
How To Modify Existing Triggers using "ALTER TRIGGER" in SQL Server? If you want to make changes to an existing trigger, you could use the "ALTER TRIGGER" statements to refine the trigger again. The tutorial exercise below shows you how to modify the trigger defined in a previous tutorial: USE FyiCe... 2016-10-24, 657👍, 0💬

All rights in the contents of this web site are reserved by the individual author. fyicenter.com does not guarantee the truthfulness, accuracy, or reliability of any contents.