Introduction of Trigger in SQL Server 2012

Introduction

Triggers in SQL Server 2012 are a special kind of stored procedure that fires automatically; they are invoked or executed when an event occurs in the database server. We can create Data Manipulation Language (DML) triggers and Data Definition Language (DDL) triggers in SQL Server 2012.

When the user wants to modify data using a DML event then the DML trigger is executed. In other words, a DML trigger is used for INSERT, DELETE and UPDATE statements of a table or view. 

When the user attempts to perform an operation using DDL then the DDL trigger is executed. In other words, a DDL trigger is executed for CREATE, ALTER and DROP statements of a table or view.

There are three types of triggers in SQL Server 2012:

• AFTER Trigger
• INSTEAD OF Trigger
• FOR Trigger

Statement that create vendors_info table in SQL Server 2012

createtable vendors_info  (  vendoridint,  vendornamevarchar(15),  vendorcityvarchar(15),  vendorstatevarchar(15)  )

Statements that insert data in vendors_info table in SQL Server 2012

insertinto vendors_info values (20,'vipendra','noida','up') insertinto vendors_info values (21,'deepak','lucknow','up') insertinto vendors_info values (22,'rahul','kanpur','up') insertinto vendors_info values (23,'malay','delhi','delhi') insertinto vendors_info values (24,'mayank','noida','up')

A Statement that is used to fetch data from vendors_info table in SQL Server 2012

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

This trigger is executed for INSERT and UPDATE statements and it converts the name of the vendor city to uppercase:

CREATETRIGGER vendor_trigger ONvendors_info AFTERINSERT,UPDATE  AS UPDATEdbo.vendors_info SETvendorcity =UPPER(vendorcity) WHEREvendorid IN(SELECTvendorid FROMINSERTED)

 

Statement that inserts data into the vendors_info table and uses a trigger in SQL Server 2012

 

INSERTINTO dbo.vendors_info        (vendorid ,          vendorname ,          vendorcity ,           vendorstate         ) VALUES ( 25, -- vendorid - int          'neha' ,-- vendorname - varchar(15)          'lucknow' ,-- vendorcity - varchar(15)          'up'  -- vendorstate - varchar(15)         )

 

A Statement that is used to fetch data from the vendors_info table after using a trigger in SQL Server 2012

 

In this table we saw that, before using the trigger, the vendorcity name is in lower case but after creating the trigger, the vendorcity name is updated to use all upper case.