How to Create INSTEAD OF Trigger in SQL

In this article I am going to explain, use INSTEAD OF trigger, .
  • 2300

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

  Clipboard2222222.jpg

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

Clipboard24444444.jpg

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

Clipboard14.jpg

Clipboard20000.jpg


© 2020 DotNetHeaven. All rights reserved.