ARTICLE

How to Work With INSTEAD OF Trigger in SQL Server 2012

Posted by Vipendra Verma Articles | SQL Server August 24, 2012
In this article I am going to explain about the INSTEAD OF trigger in SQL Server 2012.
Reader Level:

Introduction

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

There are three types of triggers in SQL Server 2012:

  • AFTER Trigger
  • INSTEAD OF Trigger
  • FOR Trigger

INSTEAD OF Trigger

The INSTEAD OF Trigger in SQL Server 2012 is executed instead of the action query that causes it to fire. An INSTEAD OF trigger in SQL Server 2012 is used for the purpose of making it updateable. To prevent an error in SQL Server 2012 we use an INSTEAD OF trigger.

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

Statement that creates a table

createtable mcninvoices
(

invoiceid
int notnull identityprimary key,
vendorid
int notnull,
invoiceno
varchar(15),
invoicetotal
money,
paymenttotal
money,
creadittotal
money

)

 Statement that inserts data into a table

insertinto 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 shows all data of mcninvoicetable

Clipboard07.jpg

Statement that creates mcnvendors table in SQL Server 2012

createtable mcnvendors

 (

 vendoridint,

 vendornamevarchar(15),

 vendorcityvarchar(15),

 vendorstatevarchar(15)

 )

Statements that insert data into the mcnvendors table in SQL Server 2012

insertinto mcnvendorsvalues (20,'vipendra','noida','up')

insertinto mcnvendorsvalues (21,'deepak','lucknow','up')

insertinto mcnvendorsvalues (22,'rahul','kanpur','up')

insertinto mcnvendorsvalues (23,'malay','delhi','delhi')

insertinto mcnvendorsvalues (24,'mayank','noida','up')

A Statement that is used to fetch data from the mcnvendors table in SQL Server 2012

Clipboard08.jpg

Statement that creates the invoices_vipendra table in SQL Server 2012

CREATE TABLEinvoices_vipendra

(

invoiceno VARCHAR(15),

invoicetotalMONEY

)

A Statement that is used to create an INSTEAD OF trigger in SQL Server 2012

Here we create an INSTEAD OF  trigger to be executed on delete and update operations on copymcnvendors table.  This trigger is executed if we try to delete or update any vender data and it is used in another table. The trigger is executed on this type of query and shows the error that this id is used in another table and it does not allow this operation.

createtrigger vipendra_inv

oninvoices_vipendra

insteadof insert

as

declare

@invoicenovarchar(15),

@invoicetotalmoney,

@vendoridint,

@rowcountint

 

select@rowcount =count(*)from inserted

if@rowcount = 1

 

      begin

     select @invoiceno= invoiceno,@invoicetotal= invoicetotal

     from inserted

     if(@invoicenois notnull and@invoicetotal is not null)

            begin

                 select @vendorid= vendoridfrom mcnvendors

                 where vendorname = 'vipendra'

                 insert intomcninvoices(vendorid,invoiceno,invoicetotal)

                 values (@vendorid,@invoiceno,@invoicetotal)

            end

      end

else

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

 

The trigger is fired in statements which are shown below:


Clipboard02.jpg

Clipboard05.jpg


 

Statement that shows all data of mcninvoicetable


Clipboard06.jpg

COMMENT USING