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:
pragadesh sivajiPosted Aug 29, 2012, 2:12 AM
Thanks Verma!!!