SQL Triggers examples

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.