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

create table vendors_info
(
    vendorid int,
    vendorname varchar(15),
    vendorcity varchar(15),
    vendorstate varchar(15)
)

Statements that insert data in vendors_info table in SQL Server 2012

 

insert into vendors_info values (20,'vipendra','noida','up')
insert into vendors_info values (21,'deepak','lucknow','up')
insert into vendors_info values (22,'rahul','kanpur','up')
insert into vendors_info values (23,'malay','delhi','delhi')
insert into 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:

CREATE TRIGGER vendor_trigger
ON vendors_info
AFTER INSERT,UPDATE
AS
UPDATE dbo.vendors_info
SET vendorcity =UPPER(vendorcity)
WHERE vendorid IN(SELECT vendorid FROM INSERTED)

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

INSERT INTO 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.