How to Work With INSTEAD OF Trigger in SQL Server 2012

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 ( invoiceidint notnull identityprimary key, vendoridint notnull, invoicenovarchar(15), invoicetotalmoney, paymenttotalmoney, creadittotalmoney )

 Statement that inserts data into a table

insertinto mcninvoices values (20,'e001',100,100,0.00) insertinto mcninvoices values (21,'e002',200,200,0.00) insertinto mcninvoices values (22,'e003',500,0.00,100) insertinto mcninvoices values (23,'e004',1000,100,100) insertinto mcninvoices values (24,'e005',1200,200,500) insertinto mcninvoices values (20,'e007',150,100,0.00) insertinto mcninvoices values (21,'e008',800,200,0.00) insertinto mcninvoices values (22,'e009',900,0.00,100) insertinto mcninvoices values (23,'e010',6000,100,100) insertinto mcninvoices values (24,'e011',8200,200,500)

Statement that shows all data of mcninvoicetable

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

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:

 

Statement that shows all data of mcninvoicetable