SQL Server Trigger If Exists Raise Error

This SQL Server tutorial will illustrate the execution of SQL Server Trigger If Exists Raise Error. Moreover, to assist you in better understanding the topic, we will debate and conclude several situations. The whole list of topics we’ll cover is given below.

• SQL Server Trigger If Exists Raise Error
• SQL Server Trigger If Exists Raise Error And Exit
• SQL Server Trigger If Exists Raise Error With Condition

SQL Server Trigger If Exists Raise Error

We will learn and understand how to use SQL Server IF statement with RAISERROR statement on the table by the query, which will be explained with the help of an illustrated example.

The RAISERROR statement allows us to generate your error message and return these messages to the application using the same format as a system error or warning message generated by the SQL Server Database Engine. In addition, the RAISERROR statement allows you to set a specific message-id, severity level, and state of error messages.

Here is a syntax of the SQL Server RAISERROR with the IF condition on the table by the following query:

SYNTAX:

CREATE OR REPLACE TRIGGER TRIGGER_NAME
ON YOUR_TABLE_NAME
AFTER [ INSERT | DELETE | UPDATE]
AS
BEGIN
DECLARE VALUES,
IF ( CONDITIONS )
SQL STATEMENTS
RAISERROR ( { message_id | message_text | @local_variable }  
    { ,severity ,state }  
    [ ,argument [ ,...n ] ] )  
    [ WITH option [ ,...n ] ]; 

INSERT INTO TABLE_NAME
VALUES(VALUE_1,VALUE_2, VALUE_3, .... , VALUE_N);

DELETE FROM TABLE_NAME
WHERE [CONDITIONS];

UPDATE TABLE_NAME
SET COLUMN_NAME=NEW_VALUE
WHERE [CONDITIONS];

Let’s examine the syntax of the SQL Server RAISERROR statement:

• Message_id: It is a user-defined error message stored in the SYS.MESSAGES catalog view.
• If we want to add a user-defined error message number then we can use the store procedure SP_ADDMESSAGE and a user-defined message should be greater than 50,000. The RAISERROR statement default uses the message_id 50,000 for raising an error.
• MESSAGE_TEXT: It is a user-defined message with formatting like the printf function in a C standard library and it can carry up to 2,047 characters in it.
• Severity: The severity level is an integer between 0 and 25 with each level representing the seriousness of the error. As 0-10 represent “Informational messages”, 11-18 for “Error” and 19-25 represent “Fatal Errors”.

Let’s see a sample example of the SQL Server IF condition with the RAISERROR statement on the table by the following query:

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE TRIGGER RAISERROR_IF
ON CANADA_STATES 
AFTER INSERT
AS
IF (ROWCOUNT_BIG()  = 0)
    RETURN;
ELSE IF EXISTS (
    SELECT * FROM inserted 
    WHERE STATE_ID  = '0'
)
BEGIN
    DECLARE @Raiserror VARCHAR(120)
    DECLARE @Class VARCHAR(50)
    SELECT TOP 1 @Class = FULL_NAME FROM inserted where STATE_ID  = '0';
    SET @Raiserror = 'Error: ' + @Class + ' is not added, Please CHANGE this to new the STATE_ID column';
    THROW 51000, @Raiserror, 1;
    ROLLBACK TRANSACTION;
END;

INSERT INTO CANADA_STATES
VALUES(0,'Christopher Nolan','Torento',87120);

As we see in the above query, we have created a trigger called RAISERROR_IF 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 DML STATEMENT will be triggered. Then the SQL STATEMENT will be executed which is used inside the CREATE TRIGGER statement.

In the AS clause, the ROW_COUNTBIG function is used inside the IF statement parameters and it will return several rows affected by the DML STATEMENT. This means that it will return 0 by using the IF condition.

In the ELSE IF condition, the SELECT statement will retrieve all records from the INSERTED pseudo 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 0 from the INSERTED table. If the WHERE condition turns out to be TRUE then the SELECT statement will retrieve all records from the INSERTED table otherwise vice-versa.

It will check with the EXISTS statement whether the records of all column exists in the INSERTED table or not.

In the BEGIN statement, we have declared the value of the RAISERROR and CLASS with the data type VARCHAR(120) and VARCHAR(50) by using the DECLARE keyword.

Then the SELECT statement is used with the TOP clause to fetch the first 1 record in the CLASS variable. In the CLASS variable, it will retrieve all records of the FULL_NAME column from the INSERTED 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 0 from the INSERTED pseudo table. If the WHERE condition turns out to be TRUE then the SELECT statement will retrieve all records from the INSERTED pseudo table. This means that once the new record is inserted into the CANADA_STATES table and the STATE_ID column record is 0 then it will throw an error.

It will set the Raiserror variable value with the help of the SET function. In the Error Message, it will write as ” Error:” with a new record of the FULL_NAME column “is not added, Please CHANGE this to new the STATE_ID column’ only when the STATE_ID column is 0.

In the INSERT statement, it will insert a new record into the CANADA_STATES table after the trigger is fired. But if the INSERT INTO statement inserted a new record into the CANADA_STATES table and the STATE_ID column is not equal to 0 then the new record will be inserted and the query will be executed.

We hope that you have understood the subtopic “SQL Server Trigger If Exists Raise Error” by using SQL Server IF statement with the RAISERROR statement on the table by the query. For a better understanding, we have used an example and explained it in depth.

Read: SQL Server Trigger to Increment Id

SQL Server Trigger If Exists Raise Error And Exit

Here we will learn and understand how to use SQL Server IF EXISTS statement 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 RAISERROR_IFEXISTS
ON USA_STATES 
AFTER UPDATE
AS
IF EXISTS (
    SELECT * FROM INSERTED 
    WHERE STATE_ID  ='25'
)
BEGIN
    DECLARE @RAISERROR VARCHAR(120)
    declare @NAME VARCHAR(50)
    SELECT @NAME= STATE_NAME FROM INSERTED WHERE STATE_ID ='25';
    SET @RAISERROR = 'Error: ' + @NAME + ' is not added, Please CHANGE the id of the STATE_ID column';
    THROW 51000, @RAISERROR, 1;
    ROLLBACK TRANSACTION;
END;

UPDATE USA_STATES
SET FULL_NAME='Nick Tesla'
WHERE STATE_ID=25;

In the aforementioned query, a trigger is created called RAISERROR_IFEXISTS on the USA_STATES table by using the CREATE TRIGGER statement. Then we used the AFTER trigger on the UPDATE statement which means that once the AFTER trigger is fired then the DML STATEMENT will be triggered and will be executed on the USA_STATES table. Then the SQL STATEMENT which is used inside the CREATE TRIGGER statement will be triggered.

In the IF EXISTS condition, the SELECT statement is used to retrieve all records from the INSERTED pseudo 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 25 from the INSERTED table. If the WHERE condition turns out to be TRUE then the SELECT statement will retrieve all records from the INSERTED table.

In the BEGIN statement, first, we have declared variables like RAISERROR and NAME with the data type like VARCHAR(120) and VARCHAR(50) in the CREATE TRIGGER statement. In the SELECT statement, it will retrieve a record of the STATE_NAME column in the NAME variable from the INSERTED 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 25 from the INSERTED table. This means that an updated record of the STATE_NAME column will show in the NAME variable while arising the RAISERROR.

By the SET function, the RAISERROR variable shows the message as “Error:” with the updated STATE_NAME column name and then ” is not added, Please CHANGE the id of the STATE_ID column’. Next, it throws the Message Number as 51000, level 16, state 1 in the message box by using the THROW clause. To close the trigger, we have used the END statement.

In the UPDATE statement, it will update and set a new string_name of the FULL_NAME column as Nick Tesla from the USA_STATES table with the WHERE condition. In the WHERE condition, the STATE_ID column is sued with the EQUAL TO operator to find a value equal to 25 from the USA_STATES table. But if the UPDATE statement updated a new value of the column from the USA_STATES table only when the WHERE condition turns out to be TRUE.

As we know the UPDATE statement will not be able to update the new value of the FULL_NAME column because it will throw an error that arises in the WHERE condition of the INSERTED table of the SELECT statement.

We hope that you have understood the subtopic “SQL Server Trigger If Exists Raise Error And Exit” by using the SQL Server AFTER trigger with the UPDATE and IF EXISTS statement on the table by the query. For a better description, we have used a sample and illustrated it in deepness.

Read: SQL Server Update Trigger Only If Column is Modified

SQL Server Trigger If Exists Raise Error With Condition

In this SQL Server subtopic tutorial, we will learn and understand how to use SQL Server TRIGGER with the IF EXISTS statement on the table by the query. And which will be explained with the help of an illustrated example.

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE TRIGGER IFEXITS_CONDITION
ON USA_STATES 
AFTER INSERT
AS
IF EXISTS (
    SELECT * FROM INSERTED 
    WHERE STATE_ID  ='35'
)
BEGIN
    DECLARE @RAISERROR VARCHAR(120)
    declare @NAME VARCHAR(50)
    SELECT @NAME= FULL_NAME FROM INSERTED WHERE STATE_ID ='35';
    SET @RAISERROR = 'Error: ' + @NAME + ' is not added, Please CHANGE the id of the STATE_ID column';
    THROW 51000, @RAISERROR, 1;
    ROLLBACK TRANSACTION;
END;

INSERT INTO USA_STATES
VALUES(35,'Florida',78450,'Stephen Hawking','Male');

In the preceding query, first, we created a trigger called IFEXISTS_CONDITION on the USA_STATES table by using the CREATE TRIGGER statement. Then the AFTER trigger fired on the INSERT statement which means that the DML STATEMENT will be triggered. Then the SQL STATEMENT which is used inside the CREATE TRIGGER statement will be executed.

With the IF EXISTS condition, the SELECT statement will retrieve all records from the INSERTED 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 35 from the INSERTED table. This means that the IF condition will check whether the condition exists or not.

In the BEGIN statement, RAISERROR and NAME variables are declared with the data types VARCHAR(120) and VARCHAR(50) by using the DECLARE clause. Then the SELECT statement will retrieve the record of the FULL_NAME column in the NAME variable from the INSERTED 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 35 from the INSERTED table. This means that when a new record is inserted into the USA_STATES table with the STATE_ID column as 35 then it will throw a RAISERROR statement in the MESSAGE BOX output.

In the RAISERROR statement, by the SET function, it will set the output message as “Error” with the FULL_NAME column new value and then continue the message by “is not added, Please CHANGE the id of the STATE_ID column”. In the MESSAGE box, it will throw the message number as 51000, level 16, state 1 by the INSERT statement for the new records in the USA_STATES table.

In the INSERT INTO statement, it will insert a new record into the USA_STATES table after the TRIGGER is fired. But if we have put a new value as 36 in the STATE_ID column of the USA_STATES table then new records were inserted into the USA_STATES table and for more checking, we can use the SELECT statement to retrieve records from the USA_STATES table.

We hope that you have understood how to use SQL Server TRIGGER used with the IF EXISTS and RAISERROR statement on the table by the query. We have used an example and explained it in depth, for a better understanding.

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

• SQL Server Create Trigger If Not Exists
• SQL Server Trigger Before Insert
• SQL Server Outer Join With Count
• SQL Server Row_Number Join
• Recursive Trigger in SQL Server
• SQL Server Trigger After Insert Update
• SQL Server Drop Trigger If Exists

We now know how to use SQL Server Trigger If Exists Raise Error 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 Trigger If Exists Raise Error
• SQL Server Trigger If Exists Raise Error And Exit
• SQL Server Trigger If Exists Raise Error With Condition