How to Work With an AFTER 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 executed when an event occurs in the database server. We can create a Data Manipulation Language (DML) trigger and 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

AFTER Trigger

An AFTER Trigger in SQL Server 2012 fires after the action query is executed. An After trigger in SQL Server 2012 never fires if the action query causes an error. To implement the referential integrity we can use an AFTER trigger in SQL Server 2012.

If we want to reverse or rollback a transaction then we use a ROLLBACK TRAN statement in SQL Server 2012. 

Statement that creates a copymcnvendors table in SQL Server 2012:

createtable copymcnvendors  (  vendorid int,  vendornamevarchar(15),  vendorcityvarchar(15),  vendorstatevarchar(15)  )

Statements that insert data into a copymcnvendors table in SQL Server 2012:

insertinto copymcnvendors values (20,'vipendra','noida','up') insertinto copymcnvendors values (21,'deepak','lucknow','up') insertinto copymcnvendors values (22,'rahul','kanpur','up') insertinto copymcnvendors values (23,'malay','delhi','delhi') insertinto copymcnvendors values (24,'mayank','noida','up') insertinto copymcnvendors values (25,'shiva','delhi','delhi') insertinto copymcnvendors values (26,'praveen','noida','up')

A statement to fetch data from a copymcnvendors table in SQL Server 2012:

Statement that creates a copymcninvoices table in SQL Server 2012:

createtable copymcninvoices ( invoiceidint notnull identityprimary key, vendoridint notnull, invoicenovarchar(15), invoicetotalmoney, paymenttotalmoney, creadittotalmoney )

Statements that inserts data into a copymcninvoices table in SQL Server 2012:

insertinto copymcninvoices values (20,'e001',100,100,0.00) insertinto copymcninvoices values (21,'e002',200,200,0.00) insertinto copymcninvoices values (22,'e003',500,0.00,100) insertinto copymcninvoices values (23,'e004',1000,100,100) insertinto copymcninvoices values (24,'e005',1200,200,500)

A Statement that is used to fetch data from a copymcninvoices table in SQL Server 2012:

A Statement that is used to create an AFTER trigger in SQL Server 2012:

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

createtrigger copymcnvendors_del_up oncopymcnvendors afterdelete,update as ifexists ( select* fromdeleted joincopymcninvoices ondeleted.vendorid=copymcninvoices.vendorid) begin  raiserror('Vendor ID is in Use in other table.',1,1) rollbackTRAN  END

 

A Trigger is fired in statements given below:

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

Here we create an after trigger to be executed on an insert or update operation of the copymcninvoices table.  This trigger is executed if we try to an insert or update in the copymcninvoices table. This trigger ensures the vendor is is valid or not and is used in an insert or update operation. If the vendor exists in copymcnvendors then it allow insert operation otherwise it does not allow this operation and instead shows error.

CREATETRIGGER copymcninvoices_ins_up ON copymcninvoices AFTERINSERT,UPDATE as  IFnot exists( SELECT* FROMcopymcnvendors WHERE vendoridIN (SELECTvendorid FROMinserted)) BEGIN  RAISERROR('Vendor is invalied',1,1) ROLLBACKTRAN END

 

A Trigger is fired in statements given below: