INSTEAD Of triggers in SQL Server fires instead of the DML event (INSERT, UPDATE, DELETE) it is associated with. It only executes once per one each DML statement no matter how many rows are going to be affected. It is possible to create INSTEAD OF triggers on tables and views, but they are commonly used with view. The reason for this is it helps us to curb the restrictions on view that we could only update on base table or no table at all if there are some aggregations used in the view.
Triggers are part of a transition they are executed under and rolling back of a transaction reset all changes made by triggers.
Since triggers are a special form of stored procedure they support RETURN statement to exit from trigger.
We can only specify at most one instead of trigger on a table or view for each DML operations. INSTEAD OF triggers are not allowed on updatable views that use WITH CHECK OPTION therefore remove that option by using ALTER VIEW before defining the INSTEAD OF trigger.
Let see an example of trigger. We have a requirement to apply soft delete on table1 whenever there is a hard delete applied to it or if a delete operation is applied to table one to simply mark the record as inactive.
--create a sample table = table1 if object_id('table1','U') is not null drop table table1; go CREATE TABLE table1 ( col1 INT not null identity , col2 VARCHAR(20), isActive bit CONSTRAINT df_isActive DEFAULT(1) ); go -- create inistead of delete trigger on table 1 if object_id('tr_in_of_dl_table1','TR') is not null drop trigger tr_in_of_dl_table1; go create trigger tr_in_of_dl_table1 on dbo.table1 instead of delete as begin set nocount on; if exists( select 1 from deleted join table1 on table1.col1= deleted.col1) update t set t.isActive = 0 from deleted d join table1 t on t.col1= d.col1 end return; --assign values to table1 for test insert into table1 (col2 ) values('value1'),('value2'),('value3'); --check values select * from table1;
--now let us try to delete row2 delete from table1 where col1=2;
--let us check what the delete operation did to table1 select * from table1;
As you can see here instead of row two being deleted the trigger forces the statement to mark it as inactive record.