How to Work With INSTEAD OF Trigger in SQL Server 2012

Introduction

Trigger in SQL server 2012 is a special kind of stored procedure that automatically fired, invoked or executes when an event occurs in the database server. We can create DML (Data Manipulation Language) trigger and DDL (Data Definition Language) trigger in SQL server 2012.

There are three type of trigger in SQL Server 2012.

AFTER Trigger
INSTEAD OF Trigger
FOR Trigger

INSTEAD OF Trigger

INSTEAD OF Trigger in SQL server 2012 executed instead of an action query that cases it to fire. INSTEAD OF trigger in SQL server 2012 is used with view to make it updateable. To prevent error in SQL server 2012 we use INSTEAD OF trigger.

RAISERROR is used to show any error in SQL server 2012.

Statement that create a table

create table mcninvoices
(
invoiceid int not null identity primary key,
vendorid int not null,
invoiceno varchar(15),
invoicetotal money,
paymenttotal money,
creadittotal money
)

Statement that insert data into 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)
insert into mcninvoices values (20,'e007',150,100,0.00)
insert into mcninvoices values (21,'e008',800,200,0.00)
insert into mcninvoices values (22,'e009',900,0.00,100)
insert into mcninvoices values (23,'e010',6000,100,100)
insert into mcninvoices values (24,'e011',8200,200,500)

Statement that show all data of mcninvoice table

Statement that create mcnvendors table in SQL server 2012

create table mcnvendors

(

vendorid int,

vendorname varchar(15),

vendorcity varchar(15),

vendorstate varchar(15)

)

Statements that insert data in mcnvendors table in SQL server 2012

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 in SQL server 2012

Statement that create invoices_vipendra table in SQL server 2012

CREATE TABLE invoices_vipendra

(

invoiceno VARCHAR(15),

invoicetotal MONEY

)

A Statement that is used to create a INSTEAD OF trigger in SQL server 2012

Here we create a INSTEAD OF trigger which are executed on delete and update operation on copymcnvendors table. This trigger is executed if we try to delete or update any vender data and it is used in other table. Trigger is executed on this type of query and show error that this id is used in other table and it does not allow this operation.

create trigger vipendra_inv

on invoices_vipendra

instead of insert

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

else

raiserror('Limit insert to a single row.',1,1)

Trigger is fired in statements which are given below

Statement that show all data of mcninvoicetable