Friday, June 14

Triggers(DML) in SQL Server

A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view. These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected. 

Triggers can be assigned to tables or views.
Basically, triggers are classified into two main types:- 
(i) After Triggers (For Triggers) 
(ii) Instead Of Triggers 

An INSTEAD OF trigger is the one that is usually associated with a view, and runs on an UPDATE action placed on that view. 

An AFTER trigger fires after an insert, update or delete on a table. They are not supported for views. 

AFTER TRIGGERS can be classified further into three types as: 
(a) AFTER INSERT Trigger. 
(b) AFTER UPDATE Trigger. 
(c) AFTER DELETE Trigger. 

Syntax of a Trigger

CREATE TRIGGER name ON table
   [WITH ENCRYPTION]
   [FOR/AFTER/INSTEAD OF]
   [INSERT, UPDATE, DELETE]
   [NOT FOR REPLICATION]
AS
BEGIN
--SQL statements
...
END 

We will create a simple table on which we can perform these triggers.

CREATE TABLE Games
(

G_ID INT Identity,

Country Varchar(100),
Game Varchar(100),
Country_Code INT 
);
INSERT INTO Games 
VALUES
('Brazil','Football',6),
('India','Hockey',2),
('Spain','Football',9),
('India','Cricket',2),
('Australia','Cricket',3);

so the Games table will look like this:


For creating an AFTER INSERT TRIGGER which will insert the rows inserted into the Games table into another Trackchanges table. The main purpose of this Trackchanges table is to record the changes in the main table. 

Now, create Trackchanges table;

CREATE TABLE Games_Trackchanges
(
G_ID INT Identity,
Country Varchar(100),
Game Varchar(100),
Country_Code INT ,
Track_Action Varchar(100),
Track_Timestamp datetime
);

a) AFTER INSERT TRIGGER

This trigger is fired after an INSERT is done on the table. Let’s create the trigger as:-

CREATE TRIGGER gamestrigger 
ON [dbo].[games] 
FOR INSERT 
AS 
    DECLARE @G_ID INT; 
    DECLARE @Country VARCHAR(100); 
    DECLARE @Game VARCHAR(100); 
    DECLARE @Country_Code INT; 
    DECLARE @Track_Action VARCHAR(100); 

    SELECT @G_ID = i.g_id  FROM   inserted i; 

    SELECT @Country = i.country  FROM   inserted i; 
    SELECT @Game = i.game FROM   inserted i; 
    SELECT @Country_Code = i.country_code FROM   inserted i; 
    SET @Track_Action = 'Inserted Record -- After Insert Trigger.'; 

    INSERT INTO games_trackchanges 

                (
                 country, 
                 game, 
                 country_code, 
                 track_action, 
                 track_timestamp) 
    VALUES     (
                @Country, 
                @Game, 
                @Country_Code, 
                @Track_Action, 
                Getdate()); 

    PRINT 'AFTER INSERT trigger fired.' 


go


The CREATE TRIGGER statement is used to create the trigger. THE ON clause specifies the table name on which the trigger is to be attached. The FOR INSERT specifies that this is an AFTER INSERT trigger. In place of FOR INSERT, AFTER INSERT can be used. Both of them mean the same. The table named inserted has been used. This table is a logical table and contains the row that has been inserted. I have selected the fields from the logical inserted table from the row that has been inserted into different variables, and finally inserted those values into the Games_Trackchanges.


Whenever a trigger is created two temporary table is created called Inserted and Deleted.Old values or deleted values will be stored in Deleted logical table and new values or inserted values will be stored in Inserted  logical table.

To see After Insert Trigger in action, Insert values into Games table;

 Insert into Games values('Australia','Hockey',3);












To see the data in Games_Trackchanges,

Select * from Games_Trackchanges;





b) AFTER UPDATE TRIGGER

This trigger is fired after an UPDATE is done on the table. Let’s create the trigger as:-

CREATE TRIGGER [dbo].[GamesupdateTrigger]
ON [dbo].[games]
after UPDATE 
AS
    DECLARE @G_ID INT;
    DECLARE @Country VARCHAR(100);
    DECLARE @Game VARCHAR(100);
    DECLARE @Country_Code INT;
    DECLARE @Track_Action VARCHAR(100);

    SELECT @G_ID = i.g_id FROM   inserted i;
    SELECT @Country = i.country FROM   inserted i;
    SELECT @Game = i.game FROM   inserted i;
    SELECT  @Country_Code = i.country_code  FROM   inserted i;

    IF UPDATE(country)
      SET @Track_Action='Updated Country Record -- After Update Trigger.';

    IF UPDATE(game)
      SET @Track_Action='Updated Game Record -- After Update Trigger.';

    IF UPDATE(country_code)
      SET @Track_Action='Updated Country_Code Record -- After Update Trigger.';

    INSERT INTO games_trackchanges
                (country,
                 game,
                 country_code,
                 track_action,
                 track_timestamp)
    VALUES     (@Country,
                @Game,
                @Country_Code,
                @Track_Action,
                Getdate());

    PRINT 'AFTER Update trigger fired.' 

The AFTER UPDATE Trigger is created in which the updated record is inserted into the Games_Trackchanges table. IF UPDATE(country)  to check if the column country as been updated. We can similarly check other columns also for an update. 

To see this trigger in action, do an update.

Update Games set Country ='France' where Country_Code=7;















To see the data in Games_Trackchanges,

Select from Games_Trackchanges;







c) AFTER DELETE TRIGGER

This trigger is fired after an DELETE is done on the table. Let’s create the trigger as:-

CREATE TRIGGER gamesdeletetrigger ON [dbo].[games]
after delete
AS
    DECLARE @G_ID INT;
    DECLARE @Country VARCHAR(100);
    DECLARE @Game VARCHAR(100);
    DECLARE @Country_Code INT;
    DECLARE @Track_Action VARCHAR(100);

    SELECT @G_ID = i.g_id  FROM   deleted i; 
   
    SELECT @Country = i.country  FROM   deleted i; 
    SELECT @Game = i.game FROM   deleted i;    
    SELECT @Country_Code = i.country_code FROM   deleted i; 
    SET @Track_Action = 'Deleted Record -- After Deleted Trigger.';

    INSERT INTO games_trackchanges
                (
                 country,
                 game,
                 country_code,
                 track_action,
                 track_timestamp)
    VALUES     (
                @Country,
                @Game,
                @Country_Code,
                @Track_Action,
                Getdate());

    PRINT 'AFTER 
Deleted trigger fired.'

go 

Here when this trigger runs, the deleted record(s) is taken from the logical deleted table and inserted into the Games_Trackchanges. 

To see this trigger in action, do a Delete.

Delete from Games where Country_Code=7 and Country='france';



A record has been inserted into the Games_Trackchanges table as:- 

Select from Games_Trackchanges;