After Trigger, Instead of Trigger Example

After Trigger, Instead of Trigger Example: An Overview

Triggers are a special type of stored procedure that automatically executes 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. These concepts are essential in understanding SQL Server. For in-depth knowledge and certification preparation, consider exploring a comprehensive SQL Server Tutorial or enrolling in a SQL Server Certification Course.

Types of DML Triggers

1. After Trigger (using FOR/AFTER CLAUSE)

This trigger fires after the SQL Server completes the execution of the action successfully that fired it.

Example: If you insert a 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, the SQL Server will not fire the After Trigger.

2. Instead of Trigger (using INSTEAD OF CLAUSE)

This trigger fires before the 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 is successfully executed but does not include the actual insert/update/delete to the table.

Example: If you insert a 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

-- First create table Employee_Demo
CREATE TABLE Employee_Demo
(
 Emp_ID int identity,
 Emp_Name varchar(55),
 Emp_Sal decimal (10,2)
)
-- Now Insert records 
Insert into Employee_Demo values ('Amit',1000);
Insert into Employee_Demo values ('Mohan',1200);
Insert into Employee_Demo values ('Avin',1100);
Insert into Employee_Demo values ('Manoj',1300);
Insert into Employee_Demo values ('Riyaz',1400);
--Now create table Employee_Demo_Audit for logging/backup purpose of table Employee_Demo create table Employee_Demo_Audit
(
 Emp_ID int,
 Emp_Name varchar(55),
 Emp_Sal decimal(10,2),
 Audit_Action varchar(100),
 Audit_Timestamp datetime
) 

This code inserts several sample records after creating a database called Employee_Demo to hold employee data (ID, name, and salary). It then creates a new table called Employee_Demo_Audit, whose purpose is to log modifications made to the Employee_Demo table. The audit table keeps track of employee information in addition to the timestamp and action performed (such as insert).

Read More - Most Asked DBMS Interview Questions

After Trigger using Insert, Update, Delete statement with example

Now I am going to explain the use of After Trigger using Insert, Update, Delete statement with example

1. After Insert Trigger

Example of After Insert Trigger

-- Create trigger on table Employee_Demo for Insert statement
CREATE TRIGGER trgAfterInsert on Employee_Demo
FOR INSERT
AS declare @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100);
select @empid=i.Emp_ID from inserted i;
select @empname=i.Emp_Name from inserted i;
select @empsal=i.Emp_Sal from inserted i;
set @audit_action='Inserted Record -- After Insert Trigger.'; insert into Employee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values (@empid,@empname,@empsal,@audit_action,getdate());
PRINT 'AFTER INSERT trigger fired.'
--Output will be 

The Employee_Demo table is inserted by these trigger logs. It prints a confirmation message after successful execution and records the inserted data (ID, name, salary) in the Employee_Demo_Audit database along with a timestamp and an "Inserted Record" message.

--Now try to insert data in Employee_Demo table
insert into Employee_Demo(Emp_Name,Emp_Sal)values ('Shailu',1000);
--Output will be 

A new record is added to the Employee_Demo table using this code. If there are no restrictions preventing it, the data ('Shailu', 1000) will be successfully inserted and the recording process will start.

--now select data from both the tables to see trigger action
select * from Employee_Demo
select * from Employee_Demo_Audit
--Output will be 

This code shows the trigger's effect. Every employee record in Employee_Demo, including the just added "Shailu" record, will be displayed in the first SELECT. A related record containing information about "Shailu"'s insertion, including the timestamp, may be found by performing a second SELECT on Employee_Demo_Audit.

Trigger has inserted the new record to the Employee_Demo_Audit table for insert statement. In this way, we can trace an insert activity on a table using a trigger.

2. After Update Trigger

Example of After Update Trigger

-- Create trigger on table Employee_Demo for Update statement
CREATE TRIGGER trgAfterUpdate ON dbo.Employee_Demo
FOR UPDATE
AS
declare @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100);
select @empid=i.Emp_ID from inserted i; 
select @empname=i.Emp_Name from inserted i;
select @empsal=i.Emp_Sal from inserted i; if update(Emp_Name)
 set @audit_action='Update Record --- After Update Trigger.';
if update (Emp_Sal)
 set @audit_action='Update Record --- After Update Trigger.';
insert intoEmployee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values (@empid,@empname,@empsal,@audit_action,getdate());
PRINT 'AFTER UPDATE trigger fired.'
--Output will be 

The Employee_Demo table updates are logged as a result of this. The updated data (ID, name, and salary) are captured, and an audit record with a timestamp and the message "Update Record" is created in Employee_Demo_Audit. Before displaying the message, the trigger determines whether the name or salary has changed.

--Now try to upadte data in Employee_Demo table
update Employee_Demo set Emp_Name='Pawan' Where Emp_ID =6;
--Output will be 

The Employee_Demo table's employee name is attempted to be updated by this code. The name of the employee with ID number six will be changed to "Pawan" if one exists. 

--now select data from both the tables to see trigger action
select * from Employee_Demo
select * from Employee_Demo_Audit
--Output will be 

The trigger actions and the update's outcomes are shown by this code. If successful, the revised employee data reflecting the name change will be displayed in the first SELECT on Employee_Demo. The updated information, including the new name and timestamp, will be logged in a new entry in the second SELECT on Employee_Demo_Audit.

Trigger has inserted the new record to the Employee_Demo_Audit table for the update statement. In this way, we can trace an update activity on a table using a trigger.

3. After Delete Trigger

Example of After Delete Trigger

-- Create trigger on table Employee_Demo for Delete statement
CREATE TRIGGER trgAfterDelete ON dbo.Employee_Demo
FOR DELETE
AS
declare @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100); select @empid=d.Emp_ID FROM deleted d;
select @empname=d.Emp_Name from deleted d;
select @empsal=d.Emp_Sal from deleted d;
select @audit_action='Deleted -- After Delete Trigger.';
insert into Employee_Demo_Audit (Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values (@empid,@empname,@empsal,@audit_action,getdate());
PRINT 'AFTER DELETE TRIGGER fired.'
--Output will be 

Deletions from the Employee_Demo table are logged as a result. Using the deleted table, it extracts the ID, name, and salary from the deleted rows and adds them to the Employee_Demo_Audit table along with a timestamp and a "Deleted" notice. After it is successfully executed, a confirmation message is also printed.

--Now try to delete data in Employee_Demo table
DELETE FROM Employee_Demo where emp_id = 5
--Output will be 

This SQL code deletes data from the "Employee_Demo" table with the employee ID 5.

--now select data from both the tables to see trigger action
select * from Employee_Demo
select * from Employee_Demo_Audit
--Output will be 

These SQL queries extract data from both the "Employee_Demo" and "Employee_Demo_Audit" databases to look into the trigger's impact.

Trigger has inserted the new record to the Employee_Demo_Audit table for the delete statement. In this way, we can trace a delete activity on a table using a trigger.

Instead of Trigger using Insert, Update, Delete statement with example

Now I am going to explain the use of Instead of Trigger using Insert, Update, Delete statement with example

1. Instead of Insert Trigger

Example of Instead of Insert Trigger

-- Create trigger on table Employee_Demo for Insert statement
CREATE TRIGGER trgInsteadOfInsert ON dbo.Employee_Demo
INSTEAD OF Insert
AS
declare @emp_id int, @emp_name varchar(55), @emp_sal decimal(10,2), @audit_action varchar(100);
select @emp_id=i.Emp_ID from inserted i;
select @emp_name=i.Emp_Name from inserted i;
select @emp_sal=i.Emp_Sal from inserted i;
SET @audit_action='Inserted Record -- Instead Of Insert Trigger.';
BEGIN 
 BEGIN TRAN
 SET NOCOUNT ON
 if(@emp_sal>=1000)
 begin
 RAISERROR('Cannot Insert where salary < 1000',16,1); ROLLBACK; end
 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());
 COMMIT;
 PRINT 'Record Inserted -- Instead Of Insert Trigger.'
END
--Output will be 

This SQL script adds an "INSTEAD OF INSERT" trigger to the "Employee_Demo" table, prohibiting insertion if the salary is less than 1000, and logs the action in the "Employee_Demo_Audit" table with a timestamp.

--Now try to insert data in Employee_Demo table
insert into Employee_Demo values ('Shailu',1300)
insert into Employee_Demo values ('Shailu',900) -- It will raise error since we are checking salary >=1000
--Outputs will be 

This code attempts to enter data into Employee_Demo. "Shailu," the initial insert (1300), will be successful. But, if there are no restrictions, the second ('Shailu,'900) will probably fail because of missing enforcement.

--now select data from both the tables to see trigger action
select * from Employee_Demo
select * from Employee_Demo_Audit
--Output will be 

Data from both tables are shown using this code. All employee records from Employee_Demo are displayed in the first selection, and the audit trail, which includes the recently added record from Employee_Demo_Audit, is shown in the second selection.

Trigger has inserted the new record to the Employee_Demo_Audit table for insert statement. In this way, we can apply business validation on the data to be inserted Instead of the trigger and can also trace an insert activity on a table.

2. Instead of Update Trigger

Example of Instead of Update Trigger

-- Create trigger on table Employee_Demo for Update statement
CREATE TRIGGER trgInsteadOfUpdate ON dbo.Employee_Demo
INSTEAD OF Update
AS
declare @emp_id int, @emp_name varchar(55), @emp_sal decimal(10,2), @audit_action varchar(100);
select @emp_id=i.Emp_ID from inserted i;
select @emp_name=i.Emp_Name from inserted i;
select @emp_sal=i.Emp_Sal from inserted i;
BEGIN
 BEGIN TRAN
if(@emp_sal>=1000)
 begin
 RAISERROR('Cannot Insert where salary < 1000',16,1); ROLLBACK; end
 else begin 
 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());
 COMMIT;
 PRINT 'Record Updated -- Instead Of Update Trigger.'; END
--Output will be 

This SQL script adds an "INSTEAD OF UPDATE" trigger to the "Employee_Demo" table, blocking updates if the salary is less than 1000 and reporting the activity in the "Employee_Demo_Audit" table with a timestamp.

<

--Now try to upadte data in Employee_Demo table
update Employee_Demo set Emp_Sal = '1400' where emp_id = 6
update Employee_Demo set Emp_Sal = '900' where emp_id = 6
--Output will be 

These SQL commands look to change the salary of an employee with ID 6 from 1400 to 900 in the "Employee_Demo" table. The first update will be successful, but the second will fail due to salary constraints, resulting in no change.

--now select data from both the tables to see trigger action
select * from Employee_Demo
select * from Employee_Demo_Audit
--Output will be 

These SQL queries extract data from both the "Employee_Demo" and "Employee_Demo_Audit" databases to investigate the trigger's impact. The result will include the current data in both tables, as well as any audit entries generated by previous operations.

Trigger has inserted the updated record to the Employee_Demo_Audit table for the update statement. In this way, we can apply business validation on the data to be updated, Instead of the trigger and can also trace an update activity on a table.

3. Instead of Delete Trigger

Example of Instead of Delete Trigger

-- Create trigger on table Employee_Demo for Delete statement
CREATE TRIGGER trgAfterDelete ON dbo.Employee_Demo
INSTEAD OF DELETE
AS
declare @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100); select @empid=d.Emp_ID FROM deleted d;
select @empname=d.Emp_Name from deleted d;
select @empsal=d.Emp_Sal from deleted d;
BEGIN TRAN if(@empsal>1200) begin
 RAISERROR('Cannot delete where salary > 1200',16,1);
 ROLLBACK;
 end
 else begin
 delete from Employee_Demo where Emp_ID=@empid;
 COMMIT;
 insert into Employee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
 values(@empid,@empname,@empsal,'Deleted -- Instead Of Delete Trigger.',getdate());
 PRINT 'Record Deleted -- Instead Of Delete Trigger.' end END
--Output will be 

This SQL script adds an "INSTEAD OF DELETE" trigger to the "Employee_Demo" table. It blocks deletion if the salary exceeds $1200, rolls back the transaction, logs the activity in "Employee_Demo_Audit," and prints a message confirming the deletion.

--Now try to delete data in Employee_Demo table
DELETE FROM Employee_Demo where emp_id = 1
DELETE FROM Employee_Demo where emp_id = 3
--Output will be 

These SQL commands attempt to delete data from the "Employee_Demo" table, wherein the employee ID is 1, followed by 3. The output will show if each delete action was successful or failed.

--now select data from both the tables to see trigger action
select * from Employee_Demo
select * from Employee_Demo_Audit
--Output will be 

These SQL queries extract data from both the "Employee_Demo" and "Employee_Demo_Audit" databases to look for any actions caused by earlier operations. The output will include the current data in both tables, as well as any audit items that have been triggered.

Trigger has inserted the deleted record to the Employee_Demo_Audit table for the delete statement. In this way, we can apply business validation on the data to be deleted using Instead of the trigger and can also trace a deleted activity on a table.

Read More:

• Top SQL Server Interview Questions and Answers

Summary

In this article I try to explain the After Trigger and Instead of Trigger with examples. I hope after reading this article your sql triggers concepts will be strong. I would like to have feedback from my blog readers. Please post your feedback, questions, or comments about this article.

Take our Sqlserver skill challenge to evaluate yourself!

In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.

GET FREE CHALLENGE