How to execute Trigger in SQL Server

This  SQL Server tutorial will discuss how to execute triggers in SQL Server. We will discuss and draw lessons from several cases to help you comprehend the subject better. The whole list of subjects we’ll discuss is provided below.

• How to execute trigger in SQL Server
• How to execute trigger manually in SQL Server
• How to call a stored procedure in trigger SQL Server

Also, take a look at the latest SQL Server tutorial: SQL Server OUTER JOIN + Examples

How to execute trigger in SQL Server

We will learn and understand how to execute triggers in SQL Server in detail, which will be explained with the help of an illustrated example.

Triggers in SQL Server are store programs which are automatically executed when the event has occurred. It is a database object which is attached to the table and is conducted automatically. We can’t call triggers explicitly. Triggers provide data integrity and are used to access and check data before and after modification using the DDL or DML queries.

Triggers are normally used in these events:

• INSERT statement is used to insert a new record into the table.
• DELETE statement is used to delete records from the table.
• UPDATE statement to update table record.

Now we learn how to define the execution of trigger in the SQL Server by the following query:

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE TRIGGER TRIGGER_TWO
ON CANADA_STATES
AFTER INSERT
AS 
BEGIN
PRINT 'My execution order is second.'
END;

CREATE TRIGGER TRIGGER_ONE
ON CANADA_STATES
AFTER INSERT 
AS 
PRINT 'My execution order is first.';

INSERT INTO CANADA_STATES 
VALUES(58,'Jennifier Lawerence','Manitoba',5454);

As we see in the above query, we have created two triggers called TRIGGER_TWO and TRIGGER_ONE by using the CREATE TRIGGER statement for the INSERT event.

In the AS clause, we have used the PRINT function to print the record as “My execution order is second” from the TRIGGER_TWO trigger and to print the other record as “My execution order is first” from the TRIGGER_ONE trigger. Now, we insert a new record into the CANADA_STATES table.

As we see that the order of execution of triggers is dependent upon the order of their creation. Multiple triggers on a SQL Server table for the same operation are not always invoked in a specific order by default.

We hope that you have understood the subtopic “how to execute trigger in SQL Server” by using the CREATE TRIGGER statement on the table by the query. For a better explanation, we have used an example and explained it in depth.

Read: SQL Server Date Format

How to execute trigger manually in SQL Server

In this section, we will try to execute the trigger manually in SQL Server in detail.

In the SQL Server, the trigger is not executed manually. They are special types of a store procedure that are defined to execute automatically in place of or after the data modification. They can be executed automatically on the INSERT, DELETE and UPDATE trigger actions.

There are two types of triggers which are used inside the Microsoft SQL Server 2022. They are the INSTEAD OF triggers and AFTER triggers. These triggers are different from each other in terms of their definition, purpose and when they are fired. Let’s see an example of the SQL Server triggered by the following query:

EXAMPLE-1: By using the INSTEAD OF trigger:

USE SQLSERVERGUIDES;

CREATE TRIGGER INSTEADOF_DML
ON CANADA_STATES
INSTEAD OF INSERT, DELETE, UPDATE
AS 
SELECT * FROM CANADA_STATES
WHERE STATE_ID>=30;

INSERT INTO CANADA_STATES
VALUES(59,'Annie Hathaway','Torento',22121)

DELETE FROM CANADA_STATES
WHERE STATE_ID=30;

UPDATE CANADA_STATES
SET FULL_NAME='Gal Gadot'
WHERE STATE_ID=38;

In the aforementioned query, a trigger is created called INSTEADOF_DML on the CANADA_STATES table by using the CREATE TRIGGER statement. Then we have used the INSTEAD OF trigger on the INSERT, DELETE and UPDATE statement which means that it will skip the execution of the DML STATEMENT and execute the SQL STATEMENT which is used inside the CREATE TRIGGER statement.

In the AS clause, the SELECT statement retrieves all records from the CANADA_STATES table with the WHERE condition. In the WHERE condition, the STATE_ID column is used with the GREATER THAN or EQUAL TO operator to find a value greater than or equal to 30 from the CANADA_STATES table.

If the WHERE condition turns out to be TRUE then the SELECT statement will retrieve all records from the CANADA_STATES table. But if the SELECT statement retrieves an empty record set from the CANADA_STATES table then the WHERE condition gets a FALSE value.

As we know the INSTEAD OF trigger is fired and it has skipped the execution of the DML statement. Here the execution of the DML STATEMENT says that:

• In the INSERT INTO statement, it will insert a new record into the CANADA_STATES table but it will not insert the record into the table as the INSTEAD OF trigger has skipped the statement.
• In the DELETE statement, it will delete the record from the CANADA_STATES table 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 30 from the CANADA_STATES table.
  • If the WHERE condition turns out to be TRUE then the DELETE statement will delete the record from the CANADA_STATES table and vice-versa.
• In the UPDATE statement, it will update and set a new string_value of the FULL_NAME column as Gal Gadot from the CANADA_STATES table with the WHERE condition.
  • If the UPDATE statement updates the new record of that column by a new value only when the WHERE condition turns out to be TRUE.
  • But if the WHERE condition turns out to be FALSE then the UPDATE statement will be executed but it will not update any value of that column in the CANADA_STATES table.

EXAMPLE-2: AFTER Trigger With The UPDATE Statement

USE SQLSERVERGUIDES;

ALTER TRIGGER INSTEADOF_DML
ON CANADA_STATES
AFTER UPDATE
AS 
BEGIN
SELECT * FROM CANADA_STATES
WHERE STATE_ID>=55
END;

UPDATE CANADA_STATES
SET FULL_NAME='Gal Gadot'
WHERE STATE_ID=56;

As we see in the above query, we have used the CREATE TRIGGER statement to create a trigger called INSTEADOF_DML on the CANADA_STATES table. Then we have used the AFTER trigger on the UPDATE statement which means that when the AFTER trigger is fired then the DML STATEMENT will be triggered and make an effect on the CANADA_STATES table. And the SQL STATEMENT which is used inside the CREATE TRIGGER statement will be triggered later.

In the UPDATE statement, it will update and set the new string_value of the FULL_NAME column as Gal Gadot from the CANADA_STATES table 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 56 from the CANADA_STATES table.

If the WHERE condition turns out to be TRUE then the UPDATE statement will update and the new string value of that column in the CANADA_STATES table. But if the UPDATE statement is executed properly without any error and has not updated the new value of that column in the CANADA_STATES table because the WHERE condition gets a FALSE value.

In the AS clause, the SELECT statement is used to retrieve all records from the CANADA_STATES table with the WHERE condition. In the WHERE condition, the STATE_ID column is used with the GREATER THAN or EQUAL TO operator to find a value greater than or equal to 55 from the CANADA_STATES table.

If the WHERE gets a TRUE value then the SELECT statement will retrieve all records from the CANADA_STATES table. But if the SELECT statement gets executed and brings no records from the CANADA_STATES table for the output only when the WHERE condition turns out to be a FALSE value.

We hope that you have understood that we can’t create triggers manually in the SQL Server. For a better explanation, we have given two examples of triggers and explained them in depth.

Read: Instead of Trigger In SQL Server

How to call a stored procedure in trigger SQL Server

We will learn and understand how to call a stored procedure in the trigger of the SQL Server in detail, which will be explained with the help of an illustrated example.

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE PROCEDURE FetchData
AS
BEGIN
SELECT * FROM CANADA_STATES
WHere STATE_ID>=55
END;

CREATE TRIGGER InsertData
On CANADA_STATES
FOR INSERT
AS
BEGIN
EXEC FetchData
END;

INSERT INTO CANADA_STATES
VALUES(60,'Alexandra Daddario','British Columbia',87120);

In the first query, we have created a procedure as FetchData by using the CREATE PROCEDURE statement. In the BEGIN statement, the SELECT statement is used to retrieve all records from the CANADA_STATES table with the WHERE condition. In the WHERE condition, the STATE_ID column is used with the GREATER THAN or EQUAL TO operator to find a value greater than or equal to 55 from the CANADA_STATES table.

If the WHERE condition turns out to be TRUE then the SELECT statement will retrieve all records from the CANADA_STATES table. But the SELECT statement retrieves an empty record set from the CANADA_STATES table only when the WHERE condition gets a FALSE value.

Then we created a trigger called InsertData on the CANADA_STATES table by using the CREATE TRIGGER statement and which will help to call the store procedure name as FetchData. In the BEGIN statement, we have used the EXEC function to fetch the store procedure name as FetchData by using the CREATE TRIGGER statement.

So, once the trigger is fired and triggered with the INSERT statement. In the INSERT statement, it will insert a new record into the CANADA_STATES table.

We hope that you have understood the subtopic “How to call store procedure in trigger SQL Server” by using the CREATE PROCEDURE and CREATE TRIGGER statements on the table by the query. For a better explanation, we have used an example and explained it in depth.

Also, take a look at some more SQL Server tutorials.

• SQL Server Drop Trigger If Exists
• SQL Server Trigger Update
• If Else In Trigger SQL Server
• SQL Server Trigger On View
• Trigger For Delete SQL Server
• SQL Server First Day Of Month
• SQL Server Trigger Before Update

We now know How to execute Trigger in SQL Server statement after reading this lesson. 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 execute trigger in SQL Server
• How to execute trigger manually in SQL Server
• How to call a stored procedure in trigger SQL Server