SQL Server Create Trigger If Not Exists

In this SQL Server tutorial, we will learn and comprehend how to use the SQL Server Create Trigger If Not Exists statement. 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.

• SQL Server Create Trigger If Not Exists
• SQL Server Instead Of Trigger If Not Exists
• SQL Server Before Trigger If Not Exists
• SQL Server For Trigger If Not Exists
• SQL Server After Trigger If Not Exists

Also, check the latest related tutorial: SQL Server Trigger On View

SQL Server Create Trigger If Not Exists

In this SQL Server tutorial, we will learn how to create a trigger which is used with the IF NOT EXISTS statement on the table by the query. And which will be explained with the help of an illustrated example.

EXAMPLE:

USE SQLSERVERGUIDES;

IF NOT EXISTS(SELECT * FROM SYS.TRIGGERS WHERE NAME='TR_INSTEADOFDML')
EXEC('CREATE TRIGGER TR_INSTEADOFDML
ON USA_STATES
FOR INSERT, DELETE, UPDATE
AS 
SELECT * FROM USA_STATES
ORDER BY STATE_ID DESC');

INSERT INTO USA_STATES
VALUES(28,'California',81624,'John Wasingston','Male');

UPDATE USA_STATES
SET FULL_NAME='Kimmy Gringer'
WHERE STATE_ID=27;

DELETE FROM USA_STATES
WHERE STATE_ID=24;

As we see in the above query, we have used the IF NOT EXISTS statement to check the trigger_name as TR_INSTEADOFDML from the NAME column in the SYS.TRIGGERS table. This means that it will delete the trigger_name if it exists by using the IF NOT EXISTS statement.

In the EXEC function, we have created a trigger called TR_INSTEADOFDML on the USA_STATES table by using the CREATE TRIGGER statement. Then we have used the FOR trigger on the DML STATEMENT which means that once the trigger is fired then the DML STATEMENT execution will be executed and will take effect on the USA_STATES table. After that, it will execute and make an effect on the SQL STATEMENT on the USA_STATES table.

In the INSERT INTO statement, it will insert a new record into the USA_STATES table after the FOR trigger is fired.

In the UPDATE statement, it will update and set a new string_value of the FULL_NAME column as Kimmy Gringer 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 27 from the USA_STATES table.

If the WHERE condition turns out to be TRUE then the UPDATE statement will update a new string_value of that column in the USA_STATES table. But 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 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 24 from the USA_STATES table. If the WHERE condition gets a TRUE value then the UPDATE statement will update a new value for that column.

Once the DML STATEMENT execution is triggered after the FOR trigger is fired. Here the execution of the SQL STATEMENT in the AS clause says that:

• In the SELECT statement, it will retrieve all records from the USA_STATES table which is based on the ORDER BY clause. In the ORDER BY clause, the STATE_ID column is used with the DESC keyword to find a value in descending order.
  • If the order is arranged in descending order then the SELECT statement will retrieve all records from the USA_STATES table for the result set.

We hope that you have understood the subtopic “SQL Server Create Trigger If Not Exists” by using the CREATE TRIGGER statement on the table by the query. We have used an example and explained it in depth, for better understanding.

Read: Full-text search in SQL Server

SQL Server Instead Of Trigger If Not Exists

Here we will learn and understand how to use the SQL Server IF NOT EXISTS statement used with the CREATE TRIGGER statement on the table by the query. And which will be explained with the help of an illustrated example.

An SQL trigger that is handled “instead of” a SQL Server UPDATE, DELETE or INSERT statement is known as an INSTEAD OF trigger. An INSTEAD OF trigger may only be created on a view and not a table, unlike SQL Server BEFORE and AFTER triggers.

EXAMPLE:

USE SQLSERVERGUIDES;

IF NOT EXISTS (SELECT * FROM sys.triggers WHERE name like '%a')
EXEC
('CREATE TRIGGER INSTEADOF_USA
ON USA_STATES
INSTEAD OF INSERT
AS 
SELECT * FROM USA_STATES
WHERE STATE_ID>=10;');

INSERT INTO USA_STATES 
VALUES(29,'Colorado',54621,'Emma Watson','Female');

In the preceding query, we have used the IF NOT EXISTS statement with the parameters. Inside the parameters, we have used the SELECT statement to retrieve all records from the SYS.TRIGGERS table which is based on the WHERE condition. In the WHERE condition. the NAME column is used with the LIKE operator to find a value whose name ends with the alphabet ‘a‘ an as “%a” from the SYS.TRIGGERS table.

In the EXEC statement, we have created a trigger called INSTEADOF_USA on the USA_STATES table by using the CREATE TRIGGER statement. Then we have used the INSTEAD OF trigger on the INSERT statement which means that once the trigger is fired, it will skip the execution of the DML statement on the table. And the SQL STATEMENTS will be executed which is used inside the CREATE TRIGGER statement.

In the AS clause, the SELECT statement retrieves all records from the USA_STATES table which is based on 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 10 from the USA_STATES table.

If the WHERE condition is 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 only when the WHERE condition gets a FALSE value.

As the INSTEAD OF trigger is triggered, the execution of the INSERT INTO statement doesn’t make any effect on the USA_STATES table. Here is the execution of the INSERT INTO statement says that:

• In the INSERT INTO statement, it is used to insert one record into the USA_STATES table.

Read: How to check if SQL Server is running

SQL Server Before Trigger If Not Exists

Here we will learn and understand how to use SQL Server IF NOT EXISTS statement used with the INSTEAD OF trigger on the table by the query. And which will be explained with the help of an illustrated example.

EXAMPLE:

USE SQLSERVERGUIDES;

IF NOT EXISTS (SELECT NAME FROM sys.triggers WHERE name like '%a')
EXEC
('CREATE TRIGGER TR_INSTEADOF_CANADA
ON CANADA_STATES
INSTEAD OF INSERT
AS 
SELECT * FROM CANADA_STATES
WHERE STATE_ID>=10');

INSERT INTO CANADA_STATES 
VALUES(25,'Ryan Reynolds','Manitoba',5421);

As we see in the above query, we have used the IF NOT EXISTS statement to check the trigger exits in the sys. triggers table or not. Inside the parameter, the SELECT statement is used to retrieve all records from the sys. triggers which are based on the WHERE condition. In the WHERE condition, the NAME column is used with the LIKE condition to find a value whose name ends with the alphabet a from the CANADA_STATES table.

We’ve used the CREATE TRIGGER statement in the EXEC function to create a trigger called TR_INSTEADOF_CANADA on the CANADA_STATES table. The INSERT statement will now run the SQL STATEMENTS that are used inside the CREATE TRIGGER statement instead of the DML STATEMENTS because the INSTEAD OF trigger was fired on the INSERT statement.

In the SELECT statement, it will retrieve all records from the CANADA_STATES table used 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 10 from the CANADA_STATES table.

If the WHERE condition gets TRUE, the SELECT statement will retrieve all records from the CANADA_STATES table.

The execution of the INSERT INTO statement has been skipped because the INSTEAD OF trigger is fired. Here the execution of the INSERT INTO statement says that: The INSERT INTO statement is used to insert a new record from the CANADA_STATES table.

We hope that you have understood how to use the SQL Server IF EXISTS statement with the INSTEAD OF trigger on the table by the query. For a better illustration, we have used an example and explained it in depth.

Read: What is a stored procedure in SQL server

SQL Server For Trigger If Not Exists

We will learn and understand how to use the SQL Server IF NOT EXISTS statement used with the FOR trigger on the table by the query. And which will be explained with the help of an illustrated example.

The SQL Server AFTER trigger is executed after the firing statement end with the INSERT, DELETE and UPDATE statements. We can also use the SQL Server FOR trigger whose method is the same as the AFTER trigger.

Here is an illustrated example of the SQL Server IF NOT EXISTS statement used with the FOR trigger on the table by the following query:

EXAMPLE:

USE SQLSERVERGUIDES;

IF NOT EXISTS (SELECT NAME FROM sys.triggers WHERE name = 'FOR_USASTATES')
EXEC('
CREATE TRIGGER FOR_USASTATES
ON USA_STATES
FOR DELETE
AS 
SELECT * FROM USA_STATES
WHERE STATE_ID>=22;');

DELETE FROM USA_STATES
WHERE STATE_ID=2;

As we see in the above query, we have used the IF NOT EXISTS statement to search for a trigger called FOR_USASTATES by using the SELECT statement in the parameter.

In the EXEC function, we have created a trigger called FOR_USASTATES on the USA_STATES table by using the CREATE TRIGGER statement. Then we have used the FOR trigger on the DELETE statement which means that the DML STATEMENT will be executed first and will be affected on the USA_STATES table. And then the SQL STATEMENT will be executed and will make an effect on the USA_STATES table.

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 which is equal to 2 from the USA_STATES table. If the WHERE condition turns out to be TRUE then the DELETE statement will delete one record from the USA_STATES table.

If the DELETE statement has been executed and has not made any effect on the USA_STATES table then it means that the WHERE condition turns out to be FALSE.

As the DML statement has been executed after the FOR trigger is fired. Then the execution of the SQL STATEMENT in the AS clause will be affected and here the execution says:

• In the SELECT statement, we have to retrieve all records from the USA_STATES table which is based on the WHERE condition. In the WHERE condition, the STATE_ID column is used with GREATER THAN or EQUAL TO operator to find a value greater than or equal to 22 from the USA_STATES table.
  • If the WHERE condition turns out to be TRUE then the SELECT statement will retrieve all records from the USA_STATES table.
  • If the SELECT statement brings an empty record set from the USA_STATES table only when the WHERE condition gets a FALSE value.

We hope that you have understood the subtopic “SQL Server For Trigger If Not Exists” by using the SQL Server FOR trigger on the table by the query. For a better understanding, we have used an example and explained it in depth.

Read: Loop in SQL Server stored procedure

SQL Server After Trigger If Not Exists

Here we will learn and understand how to use the SQL Server IF NOT EXISTS statement used with the AFTER trigger on the table by the query. And which will be explained with the help of an illustrated example.

EXAMPLE:

USE SQLSERVERGUIDES;

IF NOT EXISTS (SELECT NAME FROM sys.triggers WHERE name = 'AFTER_CANADASTATES')
BEGIN
	EXEC(
	'CREATE  TRIGGER AFTER_CANADASTATES
	ON CANADA_STATES
	AFTER UPDATE
	AS 
	SELECT * FROM CANADA_STATES
	WHERE STATE_ID>=20;');
END

UPDATE CANADA_STATES
SET FULL_NAME='Michael Jordan'
WHERE STATE_ID=23;

In the aforementioned query, we have used the IF NOT EXISTS statement to check the name as “AFTER_CANADASTATES” from the sys. triggers table. In the BEGIN statement, we have used the EXEC function to create a trigger called AFTER_CANADASTATES on the CANADA_STATES table by using the CREATE TRIGGER statement.

Then the AFTER trigger is used on the UPDATE statement which means that once the AFTER trigger is triggered then the DML STATEMENT will be executed and make effected on the USA_STATES table. After that, the SQL STATEMENT will be executed and make affected the USA_STATES table.

In the UPDATE statement, it will update and set a string_value of the FULL_NAME column as Michael Jordan in 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 23 from the USA_STATES table.

If the WHERE condition turns out to be TRUE then the UPDATE statement will update a new value for that column in the USA_STATES table.

Once the execution of the DML STATEMENT has been executed once the FOR trigger is fired. Here the execution of the SQL STATEMENT in the AS clause says that:

• In the SELECT statement, it is used to retrieve all records from the USA_STATES table which is based on 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 20 from the USA_STATES table.
  • If the WHERE condition turns out to be TRUE then 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 if the WHERE condition gets a FALSE value.

We hope that you have understood the subtopic “SQL Server After Trigger If Not Exists” by using the SQL Server AFTER trigger on the table by the query. For a better explanation, we have used an example and described it in depth.

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

• How to execute Trigger in SQL Server
• SQL Server stored procedure parameters
• SQL Server stored procedure return value
• SQL Server stored procedure vs function
• SQL Server stored procedure if else

We now know SQL Server Create Trigger If Not Exists 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.

• SQL Server Create Trigger If Not Exists
• SQL Server Instead Of Trigger If Not Exists
• SQL Server Before Trigger If Not Exists
• SQL Server For Trigger If Not Exists
• SQL Server After Trigger If Not Exists