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