Instead of Trigger In SQL Server

In this SQL Server tutorial, we will learn and comprehend how Instead of Trigger In SQL Server works. To help you comprehend the idea better, we will also talk about and learn various examples. The complete list of subjects we will address is provided below.

  • Instead of Trigger in SQL Server
  • Instead of Trigger in SQL Server With Example
  • Instead of Update Trigger in SQL Server
  • Instead of Delete Trigger in SQL Server
  • Instead of Trigger Example in SQL Server 2012
  • After and Instead of Trigger in SQL Server
  • After vs Instead of Trigger in SQL Server

Instead of Trigger in SQL Server

In this SQL Server subtopic, we will learn and understand how to use the SQL Server INSTEAD OF trigger to insert data into a basic table by a view.

The SQL Server INSTEAD OF Trigger is a trigger that allows us to skip DELETE, INSERT and UPDATE statements in a table and it executes other statements defined in the trigger instead. The INSERT, DELETE and UPDATE statements don’t occur at all.

In other words, the INSTEAD OF trigger is used to skip DML statements and execute the other statements.

We hope that you have understood the definition of the SQL Server INSTEAD OF triggers in the database in detail.

Instead of Trigger in SQL Server With Example

We will learn and understand how to create the SQL Server INSTEAD OF trigger, which will be explained with the help of an illustrated example.

SYNTAX:

CREATE TRIGGER [SCHEMA_NAME].[YOUR_TRIGGER_NAME]
ON [ YOUR_TABLE_NAME | VIEW_NAME]
INSTEAD OF { [INSERT], [DELETE], [UPDATE] }
AS
{ YOUR_SQL_STATEMENTS}

Here is the syntax explanation of the SQL Server INSTEAD OF trigger:

  • First, we need to specify the trigger_name and optionally the schema_name to which trigger is represented in the CREATE TRIGGER statement.
  • Second, specify the your_table_nawith with which trigger is associated.
  • Third, specify the event such as DELETE, INSERT or UPDATE statement to which trigger is released in the INSTEAD OF clause. The trigger may be used to respond to multiple events.
  • Fourth, we will place the trigger after the AS keyword. A trigger body may contain more than a TRANSACT-SQL statement.

Here is an illustrated example of using the INSTEAD OF trigger which will override the UPDATE, INSERT or DELETE operation on a view.

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE TRIGGER INSTEADOF_MIT
ON HARVARD_UNIVERSITY
INSTEAD OF INSERT
AS 
SELECT * FROM MIT_UNIVERSITY; 

INSERT INTO MIT_UNIVERSITY VALUES(1001, 'Jane','Hemsworth',
'hemsworth.23.jane@yahoomail.com',' Male');
  • As we see in the above query, we have created a trigger called INSTEADOF_MIT on the MIT_UNIVERSITY table.
  • The INSTEAD OF clause is used with the INSERT statement in the AS clause.
  • In the AS clause, the SELECT statement is used to retrieve all records of the MIT_UNIVERSITY table.
  • This means that if we add a new record to the MIT_UNIVERSITY table by using the INSERT INTO statement.
  • Then it will be executed properly even if we have to use the INSTEAD OF clause in the CREATE TRIGGER statement.
Instead of trigger in Sql Server with Example q
SQL Server INSTEAD OF Trigger Example

We believe you now understand how to utilize the INSTEAD OF clause with the SQL Server. And CREATE TRIGGER command to create a trigger for the table returned by the aforementioned query. We have provided a detailed explanation and presented an example to help with the exposition.

Read: SQL Server Date Format 

Instead of Update Trigger in SQL Server

The CREATE TRIGGER and UPDATE statements with the INSTEAD OF clause of the table by the query will be covered in detail and that will be clarified with the aid of an example.

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE TRIGGER INSTEADOFUPDATE
ON MIT_UNIVERSITY
INSTEAD OF UPDATE 
AS 
SELECT * FROM MIT_UNIVERSITY
WHERE STUDENT_ID=1001

UPDATE MIT_UNIVERSITY
SET STUDENT_FIRSTNAME= 'John'
WHERE STUDENT_ID=1001;
  • In this preceding query, we have created a table called the INSTEADOFUPDATE on the MIT_UNIVERSITY table.
  • In the INSTEAD OF clause procedure, we will use the SELECT statement to retrieve all records of the MIT_UNIVERSITY table with the WHERE condition.
  • In the WHERE condition, the STUDENT_ID column is used with the EQUAL operator to find a value that is equal to 1001 in the MIT_UNIVERSITY table.
  • The SELECT statement will be able to retrieve all records of the MIT_UNIVERSITY table if the WHERE condition turns out to be TRUE.
  • It means that instead of using the UPDATE statement it will run the SELECT statement query during the execution time.
  • This means that every time the UPDATE statement has been executed in the query in exchange it will run the SELECT statement query by using the CREATE TRIGGER statement.
  • The process of the UPDATE statement was to update the STUDENT_NAME column as John where the STUDENT_ID column is 1001 in the MIT_UNIVERSITY table. But it runs the SELECT statement query by using the CREATE TRIGGER statement.
Instead of Update Trigger in Sql Server Example
Example of SQL Server INSTEAD OF UPDATE Trigger

We hope that you have understood the subtopic “Instead of Update Trigger in SQL Server” by using the CREATE TRIGGER and UPDATE statements on the table by the query. For a better understanding, we have used an example and explained it in deepness.

Read: SQL Server First Day Of Month

Instead of Delete Trigger in SQL Server

The SQL Server INSTEAD OF clause on the DELETE trigger in the table by the query will be understood and demonstrated in this SQL Server section. It will be clarified with the aid of a concrete example.

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE TRIGGER INSTEADOFDELETE
ON MIT_UNIVERSITY
INSTEAD OF DELETE
AS 
SELECT * FROM MIT_UNIVERSITY
WHERE STUDENT_ID=1001;

DELETE FROM MIT_UNIVERSITY
WHERE STUDENT_ID=1001;

As we see in the above query, we have created a trigger called INSTEADOFDELETE on the MIT_UNIVERSITY table. We have used the INSTEAD OF clause on the DELETE statement as it will run the SELECT statement query. In the SELECT statement, it will retrieve all records from the MIT_UNIVERSITY table with the WHERE condition.

In the WHERE condition, the STUDENT_ID column is used with the EQUAL operator to find the value which is equal to 1001. The SELECT statement will be able to retrieve all records from the MIT_UNIVERSITY table if the WHERE condition turns out to be TRUE.

If the WHERE condition turns out to be FALSE then the SELECT statement will retrieve empty records from the MIT_UNIVERSITY table which is based on the WHERE condition.

The DELETE statement is used to delete records from the MIT_UNIVERSITY table based on the WHERE condition. In the WHERE condition, the STUDENT_ID column is used with the EQUAL operator to find the value which is equal to 1001.

Once the value is found it will delete records from the MIT_UINVERSITY table of that particular row.

Here comes the surprise part, once the trigger is fired with the INSTEAD OF clause then it means that the DML statement will not run i.e( DELETE, UPDATE and INSERT) statements. Apart from these statements, the SELECT statement will run on the MIT_UINVERSITY table.

Please note that from the below image, if you see the same records repeated many times in the MIT_UNIVERSITY table. It is because of not using the UNIQUE key for all columns in the MIT_UNIVERSITY table while using the CREATE TABLE statement.

Instead of Delete Trigger in Sql Server Example
Example of INSTEAD OF DELETE Trigger in SQL Server

We hope that you have understood how to use the CREATE TRIGGER and DELETE statement with the INSTEAD OF clause on the table by the query. For better illustration, we have used a sample and described it in deepness.

Read: Temp table in stored procedure in SQL Server

Instead of Trigger Example in SQL Server 2012

With the aid of an illustrative example, we will study and comprehend how to employ the SQL Server INSTEAD OF trigger in the table by the query when utilizing SQL Server 2012.

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE TRIGGER SQLSERVER
ON HARVARD_UNIVERSITY
INSTEAD OF DELETE
AS
BEGIN
SELECT STUDENT_ID,STUDENT_FIRSTNAME
FROM HARVARD_UNIVERSITY
WHERE STUDENT_ID>=5
END;

DELETE FROM HARVARD_UNIVERSITY
WHERE STUDENT_ID=10;
  • In the query that came before it, we added a trigger to the HARVARD_UNIVERSITY table called SQLSERVER. In the INSTEAD OF clause, it refers to instead of using the DELETE statement it will use the other statement i.e (SELECT) as it is will use the other statement which is shown in the above query.
  • In the BEGIN statement, we have used the SELECT statement to retrieve all records of the STUDENT_ID and STUDENT_FIRSTNAME column from the HARVARD_UNIVERSITY table which is used with the WHERE condition.
  • In the WHERE condition, the STUDENT_LASTNAME column is used with the GREATER THAN OR EQAUL TO operator to find greater or equal to the value of 10 from the HARVARD_UNIVERSITY table.
  • At the end of the first query, we have used the END statement to close the proper way of query. In the second query, the DELETE statement is used to execute and delete all records from the HARVARD_UNIVERSITY table with the WHERE condition.
  • In the WHERE condition, the STUDENT_ID column is used with the EQUAL TO operator to find the value as 10 from the HARVARD_UNIVERSITY table.
  • Because the trigger was activated as a result of the CREATE TRIGGER statement, the DELETE statement will not be able to be executed. As a result, because the trigger has been generated, the SELECT statement will execute in place of the DELETE statement.
Instead of Trigger Example in SQL Server 2012
INSTEAD OF Trigger Example in SQL Server 2012

By applying the INSTEAD OF clause on the table by the query, we hope you have grasped the subtopic “INSTEAD OF trigger Example in SQL Server 2012”. We provided a detailed explanation and an example to aid in understanding.

Read: SQL Server User Permissions

After and Instead of Trigger in SQL Server

In this SQL Server, we will learn and understand how to use the SQL Server AFTER and INSTEAD OF clauses on the table by the query. And which will be explained with the help of an illustrated example.

In the SQL Server, triggers are some special store procedures that are attached to the table, and when they are fired when some modification is required to a table. So basically, there are two types of triggers in the SQL Server:

  • AFTER
  • INSTEAD OF

INSTEAD OF Trigger:

  • The INSTEAD OF trigger causes the DML operations to be canceled such as DELETE, UPDATE, and INSERT operations.
  • Due to this, the SQL command sent to the SQL Server is discarded by the INSTEAD OF trigger.
  • The INSTEAD OF trigger is programmed to repeat procedures so that it looks like it could do something jointly.
  • SQL Server hasn’t yet performed or registered any changes when INSTEAD OF triggers are executed.
  • This trigger doesn’t bring any error report warning because it works even though operations don’t go through.

Here we will see the sample example of the SQL Server INSTEAD OF triggering by the following query:

EXAMPLE:

SELECT * FROM  MIT_UNIVERSITY;

CREATE TRIGGER INSTEADOF_MIT
ON HARVARD_UNIVERSITY
INSTEAD OF INSERT
AS 
SELECT * FROM MIT_UNIVERSITY; 

INSERT INTO MIT_UNIVERSITY VALUES(1001, 'Jane','Hemsworth',
'hemsworth.23.jane@yahoomail.com',' Male');

In this preceding query, we have created a trigger called INSTEADOF_MIT from the HARVARD_UNIVERSITY table. Then we have used the INSTEAD OF clause for the INSERT statement with the AS clause which means that when the INSERT trigger fires it will not throw any update error statement instead it will run the SELECT statement.

This means that the SELECT statement will retrieve all records from the MIT_UNIVERSITY table. But if the INSERT INTO statement is used on the MIT_UNIVERSITY table when already trigger is on the run because of the INSTEAD OF clause. The INSERT statement will not throw any error and the query will be executed properly but it will not be updated inside the MIT_UNIVERSITY table.

If we leave this trigger like this then it might throw a problem so the best option is the DROP TRIGGER statement which will remove the INSTEADOF_MIT table.

After and instead of trigger in Sql Server
Example of SQL Server INSTEAD OF Trigger

AFTER TRIGGER:

  • The AFTER trigger is normally used for complicated data verification.
  • These triggers can be a rollback or undo, the delete, insert, or update if the code inside the trigger doesn’t want the statements.
  • This code can do something else even if the transaction fails.
  • If the trigger doesn’t explicitly ROLLBACK the transactions then the data change operation will go as it was initially planned.
  • If the statement is rolled back the AFTER trigger can throw an error.
  • The AFTER trigger can occur after the modification but before the assumed commit, so the transaction is still open even if the AFTER trigger is fired. So, this is the main advantage of using the AFTER trigger.
  • If we want to redo all the transactions then we need to use the ROLLBACK keyword for the pending transactions.

Here is an illustrated example of the SQL Server AFTER trigger which is created on the MIT_UNIVERSITY table by including the RAISERROR and ROLLBACK TRANSACTIONS statement from the following query:

CREATE TRIGGER AFTER_MIT
ON MIT_UNIVERSITY
AFTER INSERT
AS 
PRINT 'Use of AFTER  trigger'
RIASERROR('Error', 16, 1),
ROLLBACK TRAN;

INSERT INTO MIT_UNIVERSITY VALUES(1001, 'Anthony','Mackie',
'Mackie.anthony@facebook.com',' Male');

In this above query first, we have created a trigger called AFTER_MIT on the MIT_UNIVERSITY table. Then we have used the AFTER clause on the INSERT statement with the AS keyword. In the AS clause, the PRINT function will print the name as “Use of AFTER trigger” and the RAISERROR function will provide an error type.

We can use the ROLLBACK TRAN statement to roll back the INSERT statement which is done after the AFTER clause.

We will use the INSERT statement then the transaction will be ended in the trigger and the batch will be aborted.

After and instead of trigger in Sql Server Example
Example of SQL Server AFTER Trigger

We hope that you have understood the concept of SQL Server AFTER and INSTEAD OF trigger. For a better understanding, we have used an example and explained it in depth.\

Read: Pass parameters to view in SQL Server

After vs Instead of Trigger in SQL Server

The distinction between the AFTER and INSTEAD OF triggers in SQL Server will be covered in this section on SQL Server and that will be clarified with the aid of an example.

AFTER TRIGGER:

The AFTER TRIGGER is used after the modification of the column in the table by the query. It requires the help of the DML statements i.e; INSERT, DELETE and UPDATE statements. An SQL trigger’s condition can be set using the WHEN condition. The SQL statements in the body of the SQL trigger routine are executed if the condition evaluates to true.

When the AFTER TRIGGER is triggered then we can use the ROLLBACK TRANSACTION statement to roll back the work of the statement. Even if we want to change the query code inside the trigger even if it fired, we can still do the rollback or delete, insert or update.

Here is an example of the AFTER trigger statement by the following query:

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE TRIGGER SQLSERVER
ON HARVARD_UNIVERSITY
AFTER DELETE
AS
BEGIN
SELECT STUDENT_ID,STUDENT_FIRSTNAME
FROM HARVARD_UNIVERSITY
WHERE STUDENT_ID>=5
END;

DELETE FROM HARVARD_UNIVERSITY
WHERE STUDENT_ID=10;

In this subtopic first query, we have created a trigger called SQLSERVER on the HARVARD_UNIVERSITY table. The AFTER clause is used on the DELETE statement which will be used before the INSERT statement.

So, the procedure of the BEGIN and END statement in which the SELECT statement has been done execution will do later. But before that, it will execute the DELETE statement.

The main purpose of the AFTER clause is that it will execute the insider statement later but it will execute the DML statement first which are (DELETE, UPDATE and INSERT) statements. In the DELETE statement, it will delete one record from the HARVARD_UNIVERSITY table with the WHERE condition.

In the WHERE condition, the STUDENT_ID column is used with the EQUAL TO operator to find the equal value which is 10 from the HARVARD_UNIVERSITY table.

So once the DELETE statement has been executed properly then it will move to the SELECT statement for execution. In the SELECT statement, it will retrieve all records of the STUDENT_ID and STUDENT_FIRSTNAME columns from the HARVARD_UNIVERSITY table.

In the WHERE condition, the STUDENT_ID column is used with the GREATER THAN OR EQUAL TO operator to find the value which is greater than or equal to 5 from the HARVARD_UNIVERSITY table. The SELECT statement will retrieve all records from the HARVARD_UINVERSITY table if the WHERE condition turns out to be TRUE.

In the end, the AFTER clause is used to skip the statement which has been used inside the trigger creation part.

After and instead of trigger in Sql Server
Example of SQL Server AFTER Trigger

INSTEAD OF TRIGGER:

The INSTEAD OF trigger fires a trigger of a DML modification (INSERT, DELETE and UPDATE) statements. The big difference between the AFTER and INSTEAD OF trigger is that the INSTEAD OF trigger allows us to override functionality or executed functionality that otherwise is not supported in the SQL Server. The INSTEAD OF trigger conducts earlier to data mutation.

The main purpose of the INSTEAD OF trigger is that it allows us to skip the INSERT, UPDATE and DELETE statements to the table or a view and perform other statements which are defined in the trigger instead. Here is an example of the INSTEAD OF trigger by the following query:

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE TRIGGER INSTEADOFSQL
ON MIT_UNIVERSITY
INSTEAD OF INSERT
AS 
SELECT * FROM MIT_UNIVERSITY 
WHERE STUDENT_ID>=1001;

INSERT INTO MIT_UNIVERSITY VALUES
(1002, 'Kevin','Johnson','Johnson.kevin@wwe.com','Male');

In this preceding query, we have used the CREATE TRIGGER statement to create a trigger called INSTEADOFSQL on the MIT_UNIVERSITY table. In the INSTEAD OF clause, the clause is used on the INSERT statement with the AS keyword which means that it will skip the DML statement as INSERT and will work on the SELECT statement.

In the SELECT statement, it is used to retrieve all records from the MIT_UNIVERSITY table with the WHERE condition. In the WHERE condition, the STUDENT_ID column is used with the GREATER THAN OR EQUAL TO operator to find the value which is greater than 1001 from the MIT_UNIVERSITY table.

The SELECT statement will be able to retrieve all records from the MIT_UINVERSITY table if the WHERE conditions turn out to be TRUE.

In the second query, we have used the INSERT statement to insert one new record into the MIT_UNIVERSITY table. But the INSERT statement will not be able to execute because of the INSTEAD OF clause, it will return the SELECT statement query.

After vs instead of trigger in sql server example
After vs instead of trigger in SQL server example

By applying the AFTER and INSTEAD OF trigger on the table by the query, we hope you have comprehended the subtopic “After vs Instead of Trigger in SQL Server.” We used an example and went into great detail to provide a better explanation.

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

So, in this SQL Server tutorial, we learned how Instead of Trigger In SQL Server works. To help you comprehend the idea better, we also discussed and learned various examples. The complete list of subjects we will address is provided below.

  • Instead of Trigger in SQL Server
  • Instead of Trigger in SQL Server With Example
  • Instead of Update Trigger in SQL Server
  • Instead of Delete Trigger in SQL Server
  • Instead of Trigger Example in SQL Server 2012
  • After and Instead of Trigger in SQL Server
  • After vs Instead of Trigger in SQL Server