How to get trigger definition in SQL Server

In this SQL Server tutorial, we will learn how to get trigger definition in SQL Server. Moreover, we will discuss and learn several instances to assist you in better understanding the concept. The full list of topics we will cover is given below.

• How to get trigger definition in SQL Server
• How to get Trigger in Select SQL Server
• What is Trigger With Example in SQL Server

Also check this related post: SQL Server Trigger Before Insert

How to get trigger definition in SQL Server

Here we will learn and understand how to view trigger definitions in the SQL Server. And which will be explained with the help of an illustrated example.

EXAMPLE:

USE SQLSERVERGUIDES;

SELECT 
    definition   
FROM 
    sys.sql_modules  
WHERE 
    object_id = OBJECT_ID('TR_INSTEADOF_HARVARD'); 

As we see in the above query, we have used the SELECT statement to retrieve the DEFINITION column from the sys.sql_modules table which is based on the WHERE condition. In the WHERE condition, the OBJECT_ID column is used with the EQUAL TO operator to find the object_id as TR_INSTEADOF_HAARVARD trigger from the sys.sql_modules table.

If the WHERE condition turns out to be TRUE then the SELECT statement will retrieve all records from the sys.sql_modules table.

We will learn and understand the subtopic “how to get trigger definition in SQL Server” by using the SQL Server SELECT statement in the sys.sql_modules table by the query. For a better explanation, we have used an example and explained it in depth.

Read: Disable Trigger in SQL Server

How to get Trigger in Select SQL Server

We will learn and understand how to use the SQL Server SELECT statement to get triggered in the SQL Server. And which will be explained with the help of an example.

To acquire the trigger ID in this query, send the trigger name to the OBJECT_ID method. Then, depending on the trigger’s ID, we utilized the OBJECT_DEFINITION() function to retrieve the Transact-SQL written texts of the definition.

EXAMPLE:

SELECT OBJECT_DEFINITION(OBJECT_ID('INSTEADOF_HARVARD')) AS trigger_definition;

The preceding query uses the SELECT statement on the OBJECT_DEFINITION function. In the OBJECT_DEFINITION function, it is used to find object_id as INSTEADOF_HARVARD trigger from the sys. trigger table. To shorter the function_name, we have used the ALIAS clause with the AS keyword and given the name TRIGGER_DEFINITION for the output column_name.

We hope that you have understood how to use the OBJECT_DEFINITION function to get a trigger from the system by the query. For a better description, we have used an instance and clarified it in depth.

Read: SQL Server Trigger After Insert Update

What is Trigger With Example in SQL Server

We will learn and understand what is SQL Server Trigger and which is explained with the help of an example.

A trigger is a specific kind of stored procedure that launches automatically whenever a database server event takes place. When a user attempts to edit data using a data manipulation language (DML) event, DML triggers are activated. DML operations are statements that INSERT, UPDATE, or DELETE data from a table or view.

There are three types of the SQL Server TRIGGER:

• INSTEAD OF trigger
• AFTER trigger
• FOR trigger

EXAMPLE:-1

USE SQLSERVERGUIDES;

CREATE TRIGGER AFTER_USA
ON USA_STATES
AFTER DELETE
AS 
SELECT * FROM USA_STATES
WHERE STATE_ID>=20;

DELETE FROM USA_STATES
WHERE STATE_ID=28;

In the aforementioned query, a trigger is created called AFTER_USA on the USA_STATES table by using the CREATE TRIGGER statement. Then we have used the AFTER statement on the DELETE statement which means that it will execute the DML STATEMENTS once the AFTER trigger is fired. And then it will execute the SQL STATEMENT which is used inside the AS clause.

In the DELETE statement, it will delete one record from the USA_STATES table which is based on the WHERE condition. In the WHERE condition, the STATE_ID column is used with the EQUAL TO operator to find a value equal to 28 from the USA_STATES table.

If the DELETE statement is not able to delete the record from the USA_STATES table then the WHERE condition turns out to be FALSE. If the WHERE condition turns out to be TRUE then the DELETE statement will delete one record from the USA_STATES table.

In the AS clause, the SELECT statement execution will be executed after the trigger is fired. And the execution of the SELECT statement says that:

• In the SELECT statement, it will retrieve all records from the USA_STATES table with the WHERE condition. In the WHERE condition, the STATE_ID column is used with the GREATER THAN orEQUAL TO operator to find a value greater than or equal to 20 from the USA_STATES table.
  • If the WHERE condition gets TRUE, the SELECT statement will retrieve all records from the USA_STATES table.
  • But if the SELECT statement retrieves an empty record set from the USA_STATES table then the WHERE condition gets a FALSE value.

EXAMPLE:-2:

USE SQLSERVERGUIDES;

CREATE TRIGGER TR_FORDML
ON CANADA_STATES
FOR INSERT, UPDATE, DELETE
AS 
SELECT * FROM CANADA_STATES
ORDER BY STATE_ID DESC;

INSERT INTO CANADA_STATES
VALUES(25,'Lucifier MorningStar','Nunavut',85414);

UPDATE CANADA_STATES
SET CANADA_STATENAME='Nunavut'
WHERE STATE_ID=19;

DELETE FROM CANADA_STATES
WHERE STATE_ID=16;

In the preceding query, a trigger is created called TR_FORDML on the CANADA_STATES table by using the CREATE TRIGGER statement. Then we used the FOR trigger on the DML STATEMENT i.e; (INSERT, DELETE and UPDATE) statements. This means that once the FOR trigger is fired then the DML STATEMENT will be executed first then the SQL STATEMENT which is used inside the CREATE TRIGGER statement will be executed.

In the INSERT INTO statement, it is used to insert a new record into the CANADA_STATES table.

In the UPDATE statement, it will update and set a new string_value of the CANADA_STATENAME column as Nunavut in the CANADA_STATES table which is based on the WHERE condition. In the WHERE condition, the STATE_ID column is used with the EQUAL TO operator to find a value equal to 19 from the CANADA_STATES table.

If the WHERE condition gets a TRUE boolean value then the UPDATE statement will update the new value of the CANADA_STATESNAME column in the CANADA_STATES table. But if the UPDATE statement will not able to update the new value for that column then the WHERE condition has a FALSE value.

In the DELETE statement, it will delete one record from the CANADA_STATES table which is based on the WHERE condition. In the WHERE condition, the STATE_ID column is used with the EQUAL TO operator to find a value equal to 16 from the CANADA_STATES table.

If the WHERE condition can find a value from the CANADA_STATES table then the DELETE statement will be able to delete the value from the CANADA_STATES table. But if the WHERE condition gets a FALSE value then the DELETE statement will be executed but will not delete any record from the CANADA_STATES table.

As we see the execution of the DML STATEMENT has been executed and made an effect on the CANADA_STATES table. The SQL STATEMENT which is used inside the AS clause will be completed. Here the execution of the SQL STATEMENT says that:

• In the SELECT statement, it is used to retrieve all records from the CANADA_STATES table with the help of the ORDER BY clause. In the ORDER BY clause, the STATE_ID column is used with the DESC keyword to arrange the records in descending order in the CANADA_STATES table.
• Once the records are arranged in descending order then the SELECT statement will retrieve records based on the query.

EXAMPLE-3:

USE SQLSERVERGUIDES;

CREATE TRIGGER TR_INSTEADOFDML
ON USA_STATES
INSTEAD OF INSERT, UPDATE, DELETE
AS 
SELECT * FROM USA_STATES
ORDER BY STATE_ID DESC;

INSERT INTO USA_STATES
VALUES(28,'Ohio',54128,'Luke Heather','Male');

UPDATE USA_STATES
SET STATE_NAME='California'
WHERE STATE_ID=20;

DELETE FROM USA_STATES
WHERE STATE_ID=14;

As we can see from the aforementioned query, we used the CREATE TRIGGER command to create a trigger on the USA_STATES table called TR_INSTEADOFDML. The DML STATEMENT will then be skipped during execution in favor of the SQL STATEMENT that is utilized inside the AS clause because the INSTEAD OF trigger was applied to the DML STATEMENT.

The execution of the DML STATEMENT will not make any effect on the USA_STATES table because we have used the INSTEAD OF trigger and it is fired. But the execution of the DML STATMENT says that:

• In the INSERT INTO statement, it will insert a new record into the USA_STATES table. But that record will not be entered into the USA_STATES table because the INSTEAD OF trigger is fired.
• In the UPDATE statement, it will update and set a string_value of the STATE_NAME column as California in the USA_STATES table which is based on the WHERE condition. In the WHERE condition, the STATE_ID is used with the EQUAL TO operator to find a value equal to 20 from the USA_STATES table.
  • If the WHERE condition turns out to be TRUE then the UPDATE statement will be able to update a new value for that column in the USA_STATES table.
  • If the UPDATE statement is not able to update a new value for that column in the USA_STATES table only when the WHERE condition turns out to be FALSE.
• In the DELETE statement, it will delete one record from the USA_STATES table used with the WHERE condition. In the WHERE condition, the STATE_ID column is used with the EQUAL TO operator to find a value equal to 14 from the USA_STATES table.
  • The DELETE statement will delete one record from the USA_STATES table only when the WHERE condition gets a value from the USA_STATES table.
  • If we cannot delete one record from the USA_STATES table only when the WHERE condition turns out to be FALSE.

Once the INSTEAD OF trigger is fired and the DML STATEMENT execution is executed with the skip. Now, the SELECT statement will be executed and will make an effect on the USA_STATES table.

In the AS clause, the SELECT statement will retrieve all records from the USA_STATES table used with the ORDER BY clause. In the ORDER BY clause, the STATE_ID column is used with the DESC keyword which means that it will arrange the result set along with that column in descending order.

Once the column and the result set are arranged in descending order then the SELECT statement will be able to retrieve all records from the USA_STATES table.

We hope that you have understood the subtopic “What is Trigger With Example in SQL Server” by the query. We have used an example and explained it in depth, for better understanding.

You may also like to read the following SQL Server tutorials.

• Arithmetic operators in SQL Server
• SQL Server Trigger Before Update
• SQL Server Create Trigger If Not Exists
• SQL Server scheduled stored procedure
• Alter Stored Procedure in SQL Server
• Full-text search in SQL Server

After reading this lesson, we now know How to get trigger definition in SQL Server statement. We also discussed a few instances to help you comprehend the concept. Below is a list of all the topics we’ve covered.

• How to get trigger definition in SQL Server
• How to get Trigger in Select SQL Server
• What is Trigger With Example in SQL Server