DML Triggers with example-Part-2

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

If you have read my previous blog on DML triggers then you must have all the pre-requisites required,

1. 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
END

–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

 

image

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

 

image

image

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

— 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;
set @audit_action = ‘Record Updated — Instead Of Update Trigger.’
BEGIN
BEGIN TRAN
if(@emp_sal<=1000)
begin
RAISERROR(‘Cannot Update where salary < = 1000’,16,1);
ROLLBACK;
end
else
begin
update Employee_Demo set Emp_Sal = @emp_sal where emp_id = @emp_id
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;
end
PRINT ‘Record Updated — Instead Of Update Trigger.’;
END

 

–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 = 7
–Output will be

 

image

 

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

image.

image

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

— Create trigger on table Employee_Demo for Delete statement
CREATE TRIGGER trgInsteadDelete 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

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

image

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

image

image

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.

In this article I try to explain the Instead of Trigger with example. I hope after reading this article your Instead of Trigger concepts will be strong.

I would like to have feedback from my blog readers. Please post your feedback, question, or comments about this article.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s