How to Use AFTER Trigger in SQL
In this article I am going to explain how to use AFTER trigger in SQL.
Introduction
Triggers in SQL are a special type of procedure that are executed, fired or invoked automatically in response to an action query are executed on a view or table. We can set trigger on insertion, deletion and updating or combination of these operation.
There are three type of trigger in SQL.
- AFTER Trigger
- INSTEAD OF Trigger
- FOR Trigger
An After trigger in SQL executes or fires after the action query is executed. It is used to implement the concept of referential integrity. We use CREATE TRIGGER with AFTER clause to create a after trigger.
A Statement that create copymcninvoices table
|
create table copymcninvoices
(
invoiceid int not null identity primary key,
vendorid int not null,
invoiceno varchar(15),
invoicetotal money,
paymenttotal money,
creadittotal money
)
|
Statements that insert data in copymcninvoices table
|
insert into copymcninvoices values (20,'e001',100,100,0.00)
insert into copymcninvoices values (21,'e002',200,200,0.00)
insert into copymcninvoices values (22,'e003',500,0.00,100)
insert into copymcninvoices values (23,'e004',1000,100,100)
insert into copymcninvoices values (24,'e005',1200,200,500)
|
A Statement that is used to fetch data from copymcninvoices table
A Statement that create copymcnvendors table
|
create table copymcnvendors
(
vendorid int,
vendorname varchar(15),
vendorcity varchar(15),
vendorstate varchar(15)
)
|
Statements that insert data in copymcnvendors table
|
insert into copymcnvendors values (20,'vipendra','noida','up')
insert into copymcnvendors values (21,'deepak','lucknow','up')
insert into copymcnvendors values (22,'rahul','kanpur','up')
insert into copymcnvendors values (23,'malay','delhi','delhi')
insert into copymcnvendors values (24,'mayank','noida','up')
|
A Statement that is used to fetch data from copymcnvendors table
A Statement that is used to create a trigger
|
create trigger copymcnvendors_del_up
on copymcnvendors
after delete,update
as
if exists ( select * from deleted join copymcninvoices
on deleted.vendorid=copymcninvoices.vendorid )
begin
raiserror('Vendor ID is in Use in other table.',1,1)
rollback tran
end
|
Trigger copymcnvendors_del_up executed in this Statement
A Statement that is used to create a trigger
|
create trigger copymcninvoices_ins_up
on
copymcninvoices
after insert,update
as
if not exists ( select * from copymcnvendors where vendorid IN (select vendorid from inserted))
begin raiserror('Vendor is invalied',1,1)
rollback tran
end
|
Trigger copymcninvoices_ins_up executed in this Statement
