If Else In Trigger SQL Server

This  SQL Server tutorial will discuss the If Else in Trigger SQL Server statement. 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.

• If Condition in Trigger SQL Server
• If Else In Trigger SQL Server

If Condition in Trigger SQL Server

Here we will learn and understand how to use SQL Server IF condition in the trigger of the table by the query, which will be explained with the help of an illustrated example.

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE TRIGGER IF_CONDITION
ON CANADA_STATES
FOR INSERT
AS
  BEGIN
      If (SELECT CANADA_STATENAME FROM INSERTED) LIKE '%_r'
      Begin

       INSERT INTO STATES_OF_CANADA
      ( 
STATE_ID,FULL_NAME,CANADA_STATENAME,STATE_ZIPCODE 
)
    SELECT
      STATE_ID,FULL_NAME,CANADA_STATENAME,STATE_ZIPCODE
    FROM Inserted AS I
END;
END;

INSERT INTO CANADA_STATES
VALUES(99,'Emma Watson','Newfoundland and Labrador',52010);

SELECT * FROM STATES_OF_CANADA;

As we see in the above query, a trigger is created called IF_CONDITION on the CANADA_STATES table by using the CREATE TRIGGER statement. Then we have used the FOR trigger on the INSERT statement which means that once the FOR trigger is fired the INSERT statement will be triggered and make affect the table. And in return, the SQL STATEMENT which is used inside the CREATE TRIGGER statement will be used and show the result set.

In the BEGIN statement, the IF condition is used. The SELECT statement will retrieve all records of the CANADA_STATESNAME column from the INSERTED table with the LIKE condition. In the LIKE condition, whose name ends with the alphabet r from the CANADA_STATENAME will be recorded in the STATES_OF_CANADA table.

But if any records whose name doesn’t end with the alphabet r in the CANADA_STATENAME column from the INSERTED table will be executed and they will not available because of the LIKE condition which is used inside the IF condition.

In another BEGIN condition, we have used the INSERT INTO SELECT statement which will duplicate all column records of the INSERTED table in the STATES_OF_CANADA table. And we have also used the ALIAS clause with the AS keyword to shorter the INSERTED pseudo table name as I in it. To end it, we have used the END statement.

In the INSERT INTO statement, it will insert a new record into the CANADA_STATES table after the FOR trigger is fired. But if we want to check it, we can use the SELECT statement to retrieve all records from the CANADA_STATES table after the action of the TRIGGER is fired on the INSERT statement.

We hope that you have understood the subtopic “If Else In Trigger SQL Server” by using the SQL Server IF-ELSE condition with the TRIGGER on the table by the query. For a better explanation, we have used an example and explained it in depth.

Read: SQL Server Trigger If Exists Raise Error

If Else In Trigger SQL Server

Here we will learn and understand how to use the SQL Server IF ELSE condition with the TRIGGER on the table by the query, which will be explained with the help of an illustrated example.

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE TRIGGER IFELSE_CONDITION
ON CANADA_STATES
AFTER INSERT
AS
  BEGIN
      If (SELECT CANADA_STATENAME FROM INSERTED) LIKE '%_r'
      PRINT 'All record will be inserted into the STATES_OF_TABLE with the help of LIKE
	          condition.'
     Else 
	 Print 'Query will be executed but not available in the CANADA_OF_STATES table.'
	  Begin
       INSERT INTO STATES_OF_CANADA
      ( 
STATE_ID,FULL_NAME,CANADA_STATENAME,STATE_ZIPCODE 
)
    SELECT
      STATE_ID,FULL_NAME,CANADA_STATENAME,STATE_ZIPCODE
    FROM Inserted AS I
END;
END;

INSERT INTO CANADA_STATES
VALUES(100,'Henry Cavil','Newfoundland and Labrador',87450);

In this aforementioned query, we have created a trigger called IFELSE_CONDITION on the CANADA_STATES table by using the CREATE TRIGGER statement. Then we have used the AFTER trigger on the INSERT statement which means that once the AFTER trigger is fired then the INSERT statement will be triggered and make an effect on the CANADA_STATES table. Then the SQL STATEMENT which is used inside the CREATE TRIGGER statement will be executed for the result set.

In the BEGIN statement, we have used the IF statement. In the IF statement, the SELECT statement retrieves all records of the CANADA_STATENAME column from the INSERTED table whose name ends with the alphabet r by using the LIKE clause. And only those records will be inserted into the STATES_OF_CANADA table whose name ends with the alphabet r by using the INSERT statement.

But any other records in the CANADA_STATENAME column doesn’t carry the name ends with the alphabet r will be executed successfully but they will not be available in the CANADA_OF_STATES table. But that record will be available in the CANADA_STATES table.

This means that a new record is inserted into the INSERTED table which will be also available to the STATES_OF_CANADA table.

But if the IF condition turns out to be TRUE then the PRINT statement will return “All records will be inserted into the STATES_OF_TABLE with the help of LIKE condition.” But if the IF statement condition turns out to be FALSE then the ELSE condition may return the value. Then the PRINT statement may return ‘Query will be executed but not available in the CANADA_OF_STATES table.’

In the INSERT statement, it will insert a new record into the CANADA_STATES table once the AFTER trigger is fired.

In the AS clause, the INSERT SELECT statement will retrieve all records of all columns in the CANADA_STATES table into the STATES_OF_CANADA table. But if we want to check it then we can use the SELECT statement to retrieve all records from the STATES_OF_CANADA table.

We hope that you have understood the subtopic “If Else In Trigger SQL Server” by using the SQL Server AFTER trigger on the INSERT statement in the table by the query. For a better illustration, we have used a sample example and explained it in depth.

Also, check the following SQL Server tutorials.

• What is SQL Server Cross Join?
• SQL Server Trigger to Increment Id
• How to execute Trigger in SQL Server
• Disable Trigger in SQL Server
• SQL Server Trigger After Insert Update

We now know how to use the If Else in Trigger 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.

• If Else In Trigger SQL Server
• If Condition in Trigger SQL Server