How to Create INSTEAD OF Trigger in SQL
In this article I am going to explain, use INSTEAD OF trigger, .
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
INSTEAD OF Trigger in SQL executes or fires before action query executed. We use CREATE TRIGGER with INSTEAD OF clause to create an INSTEAD OF Trigger in SQL.
A Statement that create mcninvoices table
create table mcninvoices
(
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 mcninvoices table
insert into mcninvoices values (20,'e001',100,100,0.00)
insert into mcninvoices values (21,'e002',200,200,0.00)
insert into mcninvoices values (22,'e003',500,0.00,100)
insert into mcninvoices values (23,'e004',1000,100,100)
insert into mcninvoices values (24,'e005',1200,200,500)
|
A Statement that is used to fetch data from mcninvoices table
A Statement that create mcnvendors table
create table mcnvendors
(
vendorid int,
vendorname varchar(15),
vendorcity varchar(15),
vendorstate varchar(15)
)
|
Statements that insert data in mcnvendors table
insert into mcnvendors values (20,'vipendra','noida','up')
insert into mcnvendors values (21,'deepak','lucknow','up')
insert into mcnvendors values (22,'rahul','kanpur','up')
insert into mcnvendors values (23,'malay','delhi','delhi')
insert into mcnvendors values (24,'mayank','noida','up')
|
A Statement that is used to fetch data from mcnvendors table
A Statement that is used to create a trigger
create trigger vip_inv
on vipendra_mcninvoices
instead of insert
as
declare
@invoiceno varchar(15),
@invoicetotal money,
@vendorid int,
@rowcount int
select @rowcount = count(*) from inserted
if @rowcount = 1
begin
select @invoiceno = invoiceno,@invoicetotal = invoicetotal
from inserted
if(@invoiceno is not null and @invoicetotal is not null)
begin
select @vendorid = vendorid from mcnvendors
where vendorname = 'vipendra'
insert into mcninvoices (vendorid,invoiceno,invoicetotal)
values (@vendorid,@invoiceno,@invoicetotal)
end
end
else
raiserror('Limit insert to a single row.',1,1)
|
A Statement that is used to create a trigger