After Trigger,
Instead of Trigger with Examples
Triggers are special type of stored
procedure that automatically execute when a DDL or DML statement associated
with the trigger is executed. DML Triggers are used to evaluate data after data
manipulation using DML statements. We have two types of DML triggers.
Types of DML
Triggers
1.
After Trigger (using FOR/AFTER CLAUSE)
This trigger fires after SQL Server completes the execution of the
action successfully that fired it.
Example :If you insert record/row in a table then the trigger associated with the
insert event on this table will fire only after the row passes all the checks,
such as primary key, rules, and constraints. If the record/row insertion fails,
SQL Server will not fire the After Trigger.
2.
Instead of Trigger (using INSTEAD OF CLAUSE)
This trigger fires before SQL Server starts the execution of the action
that fired it. This is much more different from the AFTER trigger, which fires
after the action that caused it to fire. We can have an INSTEAD OF insert/update/delete
trigger on a table that successfully executed but does not include the actual
insert/update/delet to the table.
Example :If you insert record/row in a table then the trigger associated with the
insert event on this table will fire before the row passes all the checks, such
as primary key, rules, and constraints. If the record/row insertion fails, SQL
Server will fire the Instead of Trigger.
Example
1. -- First create table Employee_Demo
2. CREATE TABLE Employee_Demo
3. (
4. Emp_ID int identity,
5. Emp_Name varchar(55),
6. Emp_Sal decimal (10,2)
7. )
8. -- Now Insert records
9. Insert into Employee_Demo values ('Amit',1000);
10.Insert into Employee_Demo values ('Mohan',1200);
11.Insert into Employee_Demo values ('Avin',1100);
12.Insert into Employee_Demo values ('Manoj',1300);
13.Insert into Employee_Demo values ('Riyaz',1400);
14.--Now create table
Employee_Demo_Audit for logging/backup purpose of table Employee_Demo create
table Employee_Demo_Audit
15.(
16. Emp_ID int,
17. Emp_Name varchar(55),
18. Emp_Sal decimal(10,2),
19. Audit_Action varchar(100),
20. Audit_Timestamp datetime
21.)
Now I am going to explain the use of
After Trigger using Insert, Update, Delete statement with example
1.
After Insert Trigger
1. -- Create trigger on table Employee_Demo for Insert
statement
2. CREATE TRIGGER trgAfterInsert on Employee_Demo
3. FOR INSERT
4. AS declare @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100);
5. select @empid=i.Emp_ID from inserted i;
6. select @empname=i.Emp_Name from inserted i;
7. select @empsal=i.Emp_Sal from inserted i;
8. set @audit_action='Inserted Record -- After Insert
Trigger.'; insert into
Employee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
9. values
(@empid,@empname,@empsal,@audit_action,getdate());
10.PRINT 'AFTER INSERT trigger fired.'
11.--Output will be
12. --Now try to insert data in
Employee_Demo table
13.insert into Employee_Demo(Emp_Name,Emp_Sal)values ('Shailu',1000);
14.--Output will be
15. --now select data from both the
tables to see trigger action
16.select * from Employee_Demo
17.select * from Employee_Demo_Audit
18.--Output will be
Trigger have inserted the new record to Employee_Demo_Audit table for
insert statement. In this way we can trace a insert activity on a table using
trigger.
2.
After Update Trigger
1. -- Create trigger on table Employee_Demo for Update
statement
2. CREATE TRIGGER trgAfterUpdate ON dbo.Employee_Demo
3. FOR UPDATE
4. AS
5. declare @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100);
6. select @empid=i.Emp_ID from inserted i;
7. select @empname=i.Emp_Name from inserted i;
8. select @empsal=i.Emp_Sal from inserted i; if update(Emp_Name)
9. set @audit_action='Update Record --- After Update
Trigger.';
10.if update (Emp_Sal)
11. set @audit_action='Update Record --- After Update
Trigger.';
12.insert
intoEmployee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
13.values
(@empid,@empname,@empsal,@audit_action,getdate());
14.PRINT 'AFTER UPDATE trigger fired.'
15.--Output will be
16. --Now try to upadte data in
Employee_Demo table
17.update Employee_Demo set Emp_Name='Pawan' Where Emp_ID =6;
18.--Output will be
19. --now select data from both the
tables to see trigger action
20.select * from Employee_Demo
21.select * from Employee_Demo_Audit
22.--Output will be
Trigger have inserted the new record to Employee_Demo_Audit table for
update statement. In this way we can trace a update activity on a table using
trigger.
3.
After Delete Trigger
1. -- Create trigger on table Employee_Demo for Delete
statement
2. CREATE TRIGGER trgAfterDelete ON dbo.Employee_Demo
3. FOR DELETE
4. AS
5. declare @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100); select @empid=d.Emp_ID FROM deleted d;
6. select @empname=d.Emp_Name from deleted d;
7. select @empsal=d.Emp_Sal from deleted d;
8. select @audit_action='Deleted -- After Delete Trigger.';
9. insert into Employee_Demo_Audit
(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
10.values
(@empid,@empname,@empsal,@audit_action,getdate());
11.PRINT 'AFTER DELETE TRIGGER fired.'
12.--Output will be
13. --Now try to delete data in
Employee_Demo table
14.DELETE FROM Employee_Demo where emp_id = 5
15.--Output will be
16. --now select data from both the
tables to see trigger action
17.select * from Employee_Demo
18.select * from Employee_Demo_Audit
19.--Output will be
Trigger have inserted the new record to Employee_Demo_Audit table for
delete statement. In this way we can trace a delete activity on a table using
trigger.
Now I am going to explain the use of
Instead of Trigger using Insert, Update, Delete statement with example
1.
Instead of Insert Trigger
1. -- Create trigger on table Employee_Demo for Insert
statement
2. CREATE TRIGGER trgInsteadOfInsert ON dbo.Employee_Demo
3. INSTEAD OF Insert
4. AS
5. declare @emp_id int, @emp_name varchar(55), @emp_sal decimal(10,2), @audit_action varchar(100);
6. select @emp_id=i.Emp_ID from inserted i;
7. select @emp_name=i.Emp_Name from inserted i;
8. select @emp_sal=i.Emp_Sal from inserted i;
9. SET @audit_action='Inserted Record -- Instead Of Insert
Trigger.';
10.BEGIN
11. BEGIN TRAN
12. SET NOCOUNT ON
13. if(@emp_sal>=1000)
14. begin
15. RAISERROR('Cannot Insert where salary <
1000',16,1); ROLLBACK; end
16. else begin Insert into Employee_Demo (Emp_Name,Emp_Sal) values (@emp_name,@emp_sal); Insert into
Employee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp) values(@@identity,@emp_name,@emp_sal,@audit_action,getdate());
17. COMMIT;
18. PRINT 'Record Inserted -- Instead Of Insert
Trigger.'
19.END
20.--Output will be
21. --Now try to insert data in
Employee_Demo table
22.insert into Employee_Demo values ('Shailu',1300)
23.insert into Employee_Demo values ('Shailu',900) -- It will raise error since we are
checking salary >=1000
24.--Outputs will be
25. --now select data from both the
tables to see trigger action
26.select * from Employee_Demo
27.select * from Employee_Demo_Audit
28.--Output will be
Trigger have inserted the new record to Employee_Demo_Audit table for insert
statement. In this way we can apply business validation on the data to be
inserted using Instead of trigger and can also trace a insert activity on a
table.
2.
Instead of Update Trigger
1. -- Create trigger on table Employee_Demo for Update
statement
2. CREATE TRIGGER trgInsteadOfUpdate ON dbo.Employee_Demo
3. INSTEAD OF Update
4. AS
5. declare @emp_id int, @emp_name varchar(55), @emp_sal decimal(10,2), @audit_action varchar(100);
6. select @emp_id=i.Emp_ID from inserted i;
7. select @emp_name=i.Emp_Name from inserted i;
8. select @emp_sal=i.Emp_Sal from inserted i;
9. BEGIN
10. BEGIN TRAN
11.if(@emp_sal>=1000)
12. begin
13. RAISERROR('Cannot Insert where salary <
1000',16,1); ROLLBACK; end
14. else begin
15. insert into
Employee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp) values(@emp_id,@emp_name,@emp_sal,@audit_action,getdate());
16. COMMIT;
17. PRINT 'Record Updated -- Instead Of Update
Trigger.'; END
18.--Output will be
19. --Now try to upadte data in
Employee_Demo table
20.update Employee_Demo set Emp_Sal = '1400' where emp_id = 6
21.update Employee_Demo set Emp_Sal = '900' where emp_id = 6
22.--Output will be
23. --now select data from both the
tables to see trigger action
24.select * from Employee_Demo
25.select * from Employee_Demo_Audit
26.--Output will be
Trigger have inserted the updated record to Employee_Demo_Audit table
for update statement. In this way we can apply business validation on the data
to be updated using Instead of trigger and can also trace a update activity on
a table.
3.
Instead of Delete Trigger
1. -- Create trigger on table Employee_Demo for Delete
statement
2. CREATE TRIGGER trgAfterDelete ON dbo.Employee_Demo
3. INSTEAD OF DELETE
4. AS
5. declare @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100); select @empid=d.Emp_ID FROM deleted d;
6. select @empname=d.Emp_Name from deleted d;
7. select @empsal=d.Emp_Sal from deleted d;
8. BEGIN TRAN if(@empsal>1200) begin
9. RAISERROR('Cannot delete where salary > 1200',16,1);
10. ROLLBACK;
11. end
12. else begin
13. delete from Employee_Demo where Emp_ID=@empid;
14. COMMIT;
15. insert into Employee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
16. values(@empid,@empname,@empsal,'Deleted -- Instead Of Delete
Trigger.',getdate());
17. PRINT 'Record Deleted -- Instead Of Delete
Trigger.' end END
18.--Output will be
19. --Now try to delete data in
Employee_Demo table
20.DELETE FROM Employee_Demo where emp_id = 1
21.DELETE FROM Employee_Demo where emp_id = 3
22.--Output will be
23. --now select data from both the
tables to see trigger action
24.select * from Employee_Demo
25.select * from Employee_Demo_Audit
26.--Output will be
Trigger have inserted the deleted record to Employee_Demo_Audit table
for delete statement. In this way we can apply business validation on the data
to be deleted using Instead of trigger and can also trace a delete activity on
a table.
Instead
of trigger another example:
If you have created a View in SQL which is based on a single table – the
DML operations you perform on the view are automatically propagated to the base
table.
However, when you have joined multiple tables to create a view you will
run into below error if you execute a DML statement against the view:
Msg 4405, Level 16,
State 1, Line 1
View or function 'ViewName' is not updatable
because the modification affects
multiple base tables.
To avoid this error and make a view modifiable you need to create
Triggers on the view. These triggers will be used to ‘pass’ the changes to base
tables.
You need to create a trigger on a view for all operations you need to
perform. For example, if you need to perform INSERT operations on a view you
need to create a INSTEAD OF Trigger for ‘passing’ the changes to base tables.
If you also need to perform a UPDATE/DELETE operation, you also need to create
additional INSTEAD OF Triggers for UPDATE/DELETE.
For example, let’s consider following view definition:
– © 2013 –
CREATE TABLE [dbo].[Table1]
( [ID] [INT] NULL,
[Name] [VARCHAR](20) NULL
)
CREATE TABLE [dbo].[Table2]
( [ID1] [INT] NULL,
[Name1] [VARCHAR](20) NULL
)
CREATE View [dbo].[View1]
AS
SELECTTable1.ID, Table1.Name, Table2.Name1
FROM Table1
INNER JOIN Table2
ON Table2.ID1 = Table1.ID
Now, if you try to insert to [View1], you will run into above error. To
enable INSERTs on [View1], we need to create INSTEAD OF Trigger as below:
– © 2013 –
CREATE TRIGGER [dbo].[Trig_Insert_Employee]
ON [dbo].[View1]
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO Table1
SELECT I.ID, I.Name
FROM INSERTED I
INSERT INTO Table2
SELECT I.ID, I.Name1
FROM INSERTED I
END
That’s all folks. Now, you can execute INSERT statement against the view
and it will INSERT the data to the base tables.
INSERT INTO View1 Values (1,'Gandalf','The Grey')
(1 row(s) affected)
(1 row(s)
affected)
(1 row(s)
affected)
Verify the base tables:
Triggers types
A trigger is an exceptional
sort of stored procedure which functions when we try to amend the data in a
table like inserting, deleting or updating data. It is a database object,
executed automatically and is bound to a table. Fundamentally, triggers are
classified into two types mainly-
- Instead of Trigger
- After Trigger
We know how we can insert,
delete or update operations aligned with excessively intricate views to support
natively through ‘Instead of Trigger’. In other words, we can use this trigger
as an interceptor for an action attempted on our table.
Instead of Trigger is an important
element which is emphasized in almost every SQL course. Here, we will discuss the situation where we want
to make a table column which can auto generate the customized sequence. We can
see an example of the same below-
Here, we don’t have to
misunderstand the id column above to be an identity column. This column is of
character data type . All we want is to auto generate this column as it is
displayed in the figure above.
Usage
‘Instead of Trigger’ can help us to easily solve the situation above. In ‘Instead of Trigger
‘ we insert the data into the virtual tables prior to checking the constraints.
As far as ‘After Trigger’ constraints are concerned, they are checked in the
first place. Data is then inserted into the virtual tables ( inserted and
deleted tables).
We can consider the code
mentioned below for better understanding-
CREATE TABLE [dbo].[Employee1](
[id] CHAR(10)
PRIMARY KEY,
[name] VARCHAR(50)
)
GO
INSERT INTO [dbo].[Employee1] VALUES('a1','John')
GO
Now, for an id column, we need
to automatically generate a2, a3, a4….. For this, we can write a code in an
insert trigger. Therefore, everytime the trigger command occurs, the trigger
fires and the next number is generated.
Let us consider the command
mentioned under-
INSERT INTO [dbo].[Employee1] (name) VALUES('Aslam')
Now we will enter the data in
the column (name). By doing so, we will be entering NULL values in the column
(id). We have a primary key column in the (id) column. In a primary key, there
is no permission for NULL. Therefore, the Primary Key constraint is violated.
In case, we make use of ‘After
Trigger’, then constraints are checked prior to inserting the data into the
implied table. The primary key constraint is violated in this case. Therefore,
we can’t put data into virtual table. As a result, we will not find the trigger
firing. On the other hand, on making use of ‘Instead of Trigger’, data is
inserted into the virtual table prior to the constraint check.
Therefore, our virtual
(inserted) table will be as-
Instead of Trigger’s code will
be now fired. It is written as-
--Instead of
Trigger
CREATE TRIGGER AutoIncrement_Trigger ON [dbo].[Employee1]
instead OF INSERT AS
BEGIN
DECLARE @ch CHAR
DECLARE @num INT
SELECT @num=SUBSTRING(MAX(id),2,1)
FROM [dbo].[Employee1]
SELECT @ch=SUBSTRING(MAX(id),1,1)
FROM [dbo].[Employee1]
IF @num=9
BEGIN
SET @num=0
SET @ch=
CHAR( ( 1 +
ASCII(@ch) ))
END
INSERT INTO [dbo].[Employee1] (id,name) SELECT
(@ch+CONVERT(VARCHAR(9),(@num+1))),inserted.name FROM inserted
END
Explanation
of the Code Above
The trigger’s code gets the
greatest value from the id column. This is done when we use MAX(id)function,
parse the integer data and the character. Now with the use of substring
function, put it in @ch and @num variables respectively.
When @num turns 9 then @num is
reset to 0. The character is then increased to the next character.
For
instance, if @ch= 'a' then
ASCII('a')=97
@ch=CHAR(1+97)=CHAR(98)='b'
Soon after, @num raises by 1
and gets coupled with the @ch variable. Then, it will be placed into the
dbo.employee1 table.
Now we can run the commands
mentioned under-
INSERT INTO [dbo].[Employee1] (name) VALUES('Aslam')
INSERT INTO [dbo].[Employee1] (name) VALUES('Alan')
INSERT INTO [dbo].[Employee1] (name) VALUES('Mike')
INSERT INTO [dbo].[Employee1] (name) VALUES('Rahul')
INSERT INTO [dbo].[Employee1] (name) VALUES('Vikas')
INSERT INTO [dbo].[Employee1] (name) VALUES('Vijay')
INSERT INTO [dbo].[Employee1] (name) VALUES('Vineet')
INSERT INTO [dbo].[Employee1] (name) VALUES('Rajat')
INSERT INTO [dbo].[Employee1] (name) VALUES('Alice')
SELECT * FROM
[dbo].[Employee1]
With the information provided
in the piece of writing above, we know how important is Instead of Trigger in
SQL. It provides a clear approach to modify views that we cannot change
directly through DML statements (INSERT, UPDATE, and DELETE).
NOTE: It is always a good idea to design your system such a way that you do
not need trigger to use. You can include the logic of trigger in your code as
well in your procedure and avoid the usage of the trigger. Triggers are very
difficult to debug as well adds lots of overhead to the system. There are many
performance problems due to poor implementation of the trigger. This post is
just created for demonstration of how triggers can be used in special cases.
