A Simple Example on SQL Trigger

Introduction

A trigger is a special kind of stored procedure, which is attached with a database table and gets executed automatically in response to certain action on the table like insertion, deletion or updation.

You can not invoke/call it manually, the only way to execute it is to do the required action on the table.

There are two types of Triggers

• After Triggers (For Triggers)
• Instead Of Triggers 

 

After Triggers 

These Triggers are fired just after insertion, deletion and updation of a table.

 

Instead Of Triggers 

These Triggers are fired when a user tries to do something like inserting/deleting/updating the table and performs what you have written inside it instead of what the user tries to do.

Example: If you have written an Instead Of Trigger for an Insertion operation on Sales table and someone is trying to insert into the Sales table then the code inside the Trigger gets executed instead of the Insertion query. Basically with the Instead Of Trigger you are overloading the actual functionality of the operators like INSERT, UPDATE, DELETE.

For more details on Triggers visit this link.

Behind The Scene

Here in this article we will see how to create a Trigger and how it works in real world.

Before we proceed we may need some basic knowledge on SQL.

Lets create a Table named Sales in our database.

CREATE TABLE Sales
  (
     id         INT IDENTITY(1, 1) PRIMARY KEY,
     totalsales VARCHAR(255) NOT NULL,
     solddate   VARCHAR(15)
  ) 

Now create another Table named TrigerResult

CREATE TABLE TrigerResult
  (
     id                 INT IDENTITY(1, 1) PRIMARY KEY,
     totalsalesoftheday INT NOT NULL,
     solddate           VARCHAR(15)
  ) 

Ok, below is what our aim to achieve with a Trigger.

• When any row is inserted into the Sales table check TriggerResult table 
• If it has a row with the same solddate column  value then update the row's value by adding the newly inserted totalsales column value of Sales table.
• If no row exists with the same solddate column  value then insert a new row into the TriggerResult table.

Here is the Trigger which will do our job perfectly.

CREATE TRIGGER UpdateTriggerResult
ON Sales  
AFTER INSERT
AS
  BEGIN
      DECLARE @currentDate VARCHAR(19)
      DECLARE @CurrentSoldAmmount INT

      SET @currentDate = CONVERT(VARCHAR(19), (SELECT Cast(Getdate() AS DATE)))
      SET @CurrentSoldAmmount = (SELECT TOP 1 totalsales
                                 FROM   Sales
                                 ORDER  BY id DESC)

      IF( (SELECT TOP 1 solddate
           FROM   TrigerResult
           ORDER  BY id DESC) = @currentDate )
        BEGIN
            UPDATE TrigerResult
            SET    totalsalesoftheday = totalsalesoftheday + @CurrentSoldAmmount
            WHERE  solddate = @currentDate
        END
      ELSE
        BEGIN
            INSERT INTO TrigerResult
                        (totalsalesoftheday,
                         solddate)
            VALUES     (@CurrentSoldAmmount,
                        CONVERT(VARCHAR(19), (SELECT Cast(Getdate() AS DATE))))
        END
  END
GO

This trigger will perform as the above said constraints. And inserts/updates the TrigerResult table in response of the INSERT statement on Sales table.

Now insert into Sales table by the below statement and see what happens.

INSERT INTO sales
            (totalsales,
             solddate)
VALUES      (100,
             CONVERT(VARCHAR(19), (SELECT Cast(Getdate() + 1 AS DATE)))) 

This will give you the below result.

One row inserted in both tables.

Now run the same insert statement again and you will find the below result.

One row inserted in Sales table and TriggerResult table is updated.

Now you got to know how this Trigger actually works. You can continue with the other types for Update and Delete operations.

Find out the video tutorial on this.

Happy Coding...