Instead of Trigger In SQL Server

In this SQL Server tutorial, we will learn and comprehend how INSTEAD OF TRIGGER in SQL Server works. Also, we will discuss how we can define an instead of trigger in SQL Server either for table or view.

Generally, when we define a trigger in SQL Server, a trigger performs some operation based on certain database actions. However, with the help of INSTEAD OF TRIGGER, we can perform some actions instead of the specified ones.

Now, to understand the idea of INSTEAD OF TRIGGER in SQL Server better, we will discuss and learn various examples. The complete list of examples that we will address is given below.

• Instead of Trigger in SQL Server
• Syntax of 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
• After vs Instead of Trigger in SQL Server

Instead of Trigger in SQL Server

In this SQL Server section, we will learn and understand how to use INSTEAD OF TRIGGER in SQL Server.

The INSTEAD OF TRIGGER in SQL Server 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.

Syntax of Instead of Trigger in SQL Server

Once we got an idea of instead of trigger in SQL Server, let us discuss how we can define a trigger in SQL Server. So, here is the syntax of defining an instead of trigger in SQL Server.

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

In the above syntax:

• First, we need to specify the name of the trigger while specifying the CREATE TRIGGER statement.
• Second, specify the table name with which the trigger will be associated.
• Third, specify the event such as DELETE, INSERT or UPDATE statement to which trigger is released in the INSTEAD OF clause. Moreover, we can specify more than one event.
• Fourth, we need to specify the body of the trigger after the AS keyword. Moreover, the trigger body may contain more than one TRANSACT-SQL statement.

With the end of this section, we have understood what is instead of trigger in SQL Server. Also, we have discussed the syntax to define an INSTEAD OF TRIGGER.

Instead of Trigger in SQL Server With Example

Next, let us discuss how we can use this syntax in SQL Server. For this, we will illustrate an example using a table and a view in SQL Server.

For example, we have States and Countries table in SQL Server. Here is the ER diagram for both tables.

Now, based on these tables, we have created a view named vwStateDetails. Here is the code for this view in SQL Server.

USE GeoNames
GO

CREATE VIEW vwStateDetails
AS
SELECT States.id, States.name AS [State Name], Countries.name AS [Country Name]
FROM States
INNER JOIN Countries
ON States.country_id = Countries.id

The above view in SQL Server will return both the state name and its particular country as a result.

Now, the SQL Server will return an error if we try to perform an insert operation on this vwStateDetails view.

USE GeoNames
GO

INSERT INTO vwStateDetails([State Name], [Country Name])
VALUES ('New Jersey', 'United States')

Here is the error message in SQL Server.

Now, to overcome this error in SQL Server, we can define an INSTEAD OF TRIGGER. The role of this INSTEAD OF TRIGGER is to fetch the inserted values. And instead of inserting values in the view, it will insert values in the States and Countries tables respectively.

Here is the code for the INSTEAD OF TRIGGER in SQL Server.

USE GeoNames
GO

CREATE TRIGGER trgvwStateDetails
ON vwStateDetails
INSTEAD OF INSERT
AS
BEGIN
	
  --Fetch country_id for States table
  DECLARE @CountryId int  
  SELECT @CountryId = Countries.id
  FROM Countries
  INNER JOIN INSERTED
  on inserted.[Country Name] = Countries.name
 
  --If the CountryId is null then throw an error
  IF(@CountryId is null)
  BEGIN
    RAISERROR('Invalid Country Name', 16, 1)
    RETURN
  END
 
  --Insert data in States table
  INSERT INTO States(name, country_id)
  SELECT [State Name], @CountryId
  FROM INSERTED
End

In the above example, we have defined a trigger that inserts a record in the States table instead of inserting data in the vwStateDetails view.

Now, if we perform the insert operation again on the vwStateDetails view, it will not insert data in the view. Instead, the trigger will be executed and it will insert a new record in the States table.

At the end of this section, we get a clear idea of how to the INSTEAD OF TRIGGER Syntax in SQL Server. And how to create an INSTEAD OF INSERT TRIGGER in SQL Server.

Read: SQL Server Date Format 

Instead of Update Trigger in SQL Server

In the previous section, we have seen how to define an INSTEAD OF TRIGGER for INSERT statement in SQL Server. Now, in this section, we will take a step further and understand how to define an INSTEAD OF TRIGGER for UPDATE operation in SQL Server.

For the example implementation, let us assume there is a wrong entry in the States table given in the previous section.

Now, to update its data, we are using the same vwStateDetails view in SQL Server. However, just like the INSERT statement, we cannot directly perform the UPDATE operation on the vwStateDetails view. Even if we try to perform it, the SQL Server will return an error.

So, we will create an INSTEAD OF UPDATE TRIGGER in SQL Server on the vwStateDetails view. And the SQL code for this task is given below.

USE GeoNames
GO

CREATE OR ALTER TRIGGER trgUpdateStateDetails
ON vwStateDetails
INSTEAD OF UPDATE
AS
BEGIN
	UPDATE dbo.States
	SET name = ins.[State Name]
	FROM dbo.States
	INNER JOIN INSERTED ins on States.id = ins.id;
END

In the above SQL code, we have defined an INSTEAD OF TRIGGER for UPDATE named trgUpdateStateDetails. This trigger will update the States table as specified for the vwStateDetails.

Next, let us update the vwStateDetals and correct the state name from Kalifornia to California.

USE GeoNames
GO

UPDATE vwStateDetails
SET [State Name] = 'California'
WHERE id = 1006

After executing the above SQL, the trigger will initialize and it will update the state name to California where the id is 6. Here is the result of the States table in SQL Server.

So, in this SQL Server section, we have learned how to create an INSTEAD OF UPDATE TRIGGER IN SQL Server using an example.

Read: SQL Server First Day Of Month

Instead of Delete Trigger in SQL Server

Just like creating INSTEAD OF TRIGGER for INSERT and DELETE, we can also create an INSTEAD OF TRIGGER in SQL Server for DELETE operation. Let us understand this whole concept using an example.

For the example implementation, we will try to perform the DELETE operation on the vwStateDetails view. However, when we try to perform the delete operation, the SQL Server instance will return an error.

So, to overcome this issue in SQL Server, we are going to create INSTEAD OF DELETE TRIGGER on the vwStatesDetails view. Here is the T-SQL code for the trigger in SQL Server.

USE GeoNames
GO

CREATE OR ALTER TRIGGER trgStateDetailsDelete
ON vwStateDetails  
INSTEAD OF DELETE  
AS  
BEGIN
	DECLARE @state_id int  
	
	SELECT @state_id = States.id
	FROM States
	INNER JOIN DELETED
	ON States.id = DELETED.id

	IF(@state_id is NULL )    
	BEGIN    
		RAISERROR('Invalid ID', 16, 1)    
		RETURN    
	END
	ELSE
	BEGIN
		DELETE FROM States
		WHERE id = @state_id
	END
END

In the above example, we created a trigger that will fetch the state id value from the DELETED table. And instead of trying to delete the record from view, it will perform the DELETE operation on the States table.

USE GeoNames
GO

DELETE FROM vwStateDetails
WHERE id = 5
GO

Now, when we try to perform the DELETE operation on the view, the trigger will be automatically executed. And it will delete the record from the States table.

Note: Please note that here in this example we have deleted a record from the States table where the state id is 5 and state name is Georgia.

Here is the resultset of the States table after performing the delete operation on the view in SQL Server.

At the end of this section, we understood how to create INSTEAD OF DELETE TRIGGER in SQL Server using an example.

Read: SQL Server view order by

After vs Instead of Trigger in SQL Server

In this SQL Server section, we will understand the key difference between AFTER TRIGGER and INSTEAD OF TRIGGER in SQL Server.

Generally, the FOR / AFTER trigger in SQL Server is utilized to perform certain operations once the INSERT, DELETE, or UPDATE operation is successfully executed. However, INSTEAD OF Trigger in SQL Server is the trigger that will execute some other operation instead of the specified operation.

Let us understand the difference between AFTER Trigger and INSTEAD OF Trigger in SQL Server using an example. So, for the example demonstration, we will create one AFTER Trigger and one INSTEAD OF Trigger on the States table for the INSERT operation.

And we will check how AFTER Trigger and INSTEAD OF Trigger is different from one another.

AFTER Trigger in SQL Server Example

USE GeoNames
GO

CREATE TRIGGER dbo.trgStatesInsert
ON dbo.States
AFTER INSERT	
AS
BEGIN
    INSERT INTO dbo.StatesLog(state_id, operation, updatedDate)
    SELECT id ,'INSERT',GETDATE() FROM INSERTED;
END

In the above example, we have created a trigger in SQL Server named trgStatesInsert. This trigger will insert a record in the StatesLog table after the INSERT statement is executed successfully.

USE GeoNames
GO

INSERT INTO dbo.States(name, country_id)
VALUES ('Arizona', 2)

So, whenever we perform an insert operation on the States table, the trigger will be executed after the INSERT statement. And the trigger will make an entry in the StatesLog table.

Here is the data of the States and StatesLog table after the insert operation.

INSTEAD OF Trigger in SQL Server Example

USE GeoNames
GO

CREATE TRIGGER dbo.trgStatesInsteadOfInsert
ON dbo.States
INSTEAD OF INSERT	
AS
BEGIN
    INSERT INTO dbo.StatesLog(state_id, operation, updatedDate)
    SELECT id ,'INSERT',GETDATE() FROM INSERTED;
END

In the above example, we have created a trigger in SQL Server named trgStatesInsteadOfInsert. This trigger will insert a record in the StatesLog table instead of performing the insert operation on the States table.

USE GeoNames
GO

INSERT INTO dbo.States(name, country_id)
VALUES ('Ohio', 2)

Now, when we perform the INSERT operation on the States table, the trigger will make an entry in the StatesLog table instead of inserting data in the States table.

Here is the data of the States and StatesLog table after executing the INSERT statement.

At the end of this section, we have covered the critical difference between AFTER trigger and INSTEAD OF Trigger in SQL Server.

Conclusion

So, in this SQL Server tutorial, we learned how Instead of Trigger In SQL Server works. Moreover, to help you understand the concept better, we also discussed and covered various examples. The complete list of topics we have addressed is given below.

• Instead of Trigger in SQL Server
• Syntax of 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
• After vs Instead of Trigger in SQL Server

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

• Alter view in SQL Server
• SQL Server OUTER JOIN
• Trigger For Delete SQL Server
• Indexed views in SQL Server
• SQL Server String_agg
• View SQL Server Error Logs
• SQL Server Drop Trigger If Exists