How to Use AFTER Trigger in SQL

In this article I am going to explain how to use AFTER trigger in SQL.
  • 2770

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

An After trigger in SQL executes or fires after the action query is executed. It is used to implement the concept of referential integrity. We use CREATE TRIGGER with AFTER clause to create a after trigger.

A Statement that create copymcninvoices table

create table copymcninvoices

(

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 copymcninvoices table

insert into copymcninvoices values (20,'e001',100,100,0.00)

insert into copymcninvoices values (21,'e002',200,200,0.00)

insert into copymcninvoices values (22,'e003',500,0.00,100)

insert into copymcninvoices values (23,'e004',1000,100,100)

insert into copymcninvoices values (24,'e005',1200,200,500)

 A Statement that is used to fetch data from copymcninvoices table
 
Clipboard02111.jpg

A Statement that create copymcnvendors table

create table copymcnvendors

 (

 vendorid int,

 vendorname varchar(15),

 vendorcity varchar(15),

 vendorstate varchar(15)

 )

Statements that insert data in copymcnvendors table

insert into copymcnvendors values (20,'vipendra','noida','up')

insert into copymcnvendors values (21,'deepak','lucknow','up')

insert into copymcnvendors values (22,'rahul','kanpur','up')

insert into copymcnvendors values (23,'malay','delhi','delhi')

insert into copymcnvendors values (24,'mayank','noida','up')

A Statement that is used to fetch data from copymcnvendors table

Clipboard0411.jpg

A Statement that is used to create a trigger

create trigger copymcnvendors_del_up

on copymcnvendors

after delete,update

as

if exists ( select * from deleted join copymcninvoices

on deleted.vendorid=copymcninvoices.vendorid )

begin

raiserror('Vendor ID is in Use in other table.',1,1)

rollback tran

end


Trigger copymcnvendors_del_up executed in this Statement

Clipboard08.jpg

A Statement that is used to create a trigger

create trigger copymcninvoices_ins_up

on

copymcninvoices

after insert,update

as

if not exists ( select * from copymcnvendors where vendorid IN (select vendorid from inserted))

begin raiserror('Vendor is invalied',1,1)

rollback tran

end

Trigger copymcninvoices_ins_up executed in this Statement

Clipboard10.jpg

Clipboard12.jpg


© 2020 DotNetHeaven. All rights reserved.