What are SQL-Trigger

0
2081

In this article we will learn about Sql triggers which automatically triggers the corresponding event (DML/DDL) statement that is occurred in the database.

To understand the working of sql triggers kindly follow the points given below:

  1. Introduction :
    • Sql Triggers are the database system object which’s automatically shoots when corresponding event (DML/DDL) statement is occurred in database.
    • In this article I have explained the usages DML database triggers.
    • Following are the Action of DML Triggers that execute itself when DML statement is performed on its associated table:
      1. For Insert
      2. For Update
      3. For Delete

  2. Usages of Trigger :
    • Trigger is very useful to track the data manipulation in tables.
    • It is useful to maintain data history in tables.
    • No need to run the job scheduler. It automatically fire’s when corresponding event occur in tables.
    • Trigger is very useful to maintain the data integrity in tables

  3. DML Triggers :
    • DML Triggers execute itself when DML (Insert/Update/Delete) statement event occurs in database.
      1. It performs action in database according to behaviour of data modification. For example after or before data modification.
      2. DML Trigger provides data consistency, the entire records rollback if any server error detect.
      3. DML trigger perform action according to its virtual table which are called inserted and deleted. These tables store the value of before and after data modification.

    To understand the behaviours of these table let’s see following figure 1.

    table

DML Triggers Examples:

  • In this example I am creating an audit table of my original table Salary.
  • This audit table hold all the modification in salary table. So that we can use that information for audit purpose.
  • Let’s have look on salary table and Salary Audit Table:
  • DML Trigger example

    • You can see, salary audit table is empty. Now we perform Some DML Statement in Salary Table.
    • The DML trigger that associated with Salary table will fire automatically when corresponding DML event will fire.
    • Trigger hold modified data in virtual table (Deleted/Inserted) and store the record in Salary Audit Table.
    • It inserts new records in salary audit table for insert update and deletes action in main salary table.


    DML Triggers:

  • In this section I have made three examples below for Insert/Delete/Update statement.
  • Let’s see the following DML Trigger behaviour below:
    • For Insert (execute when insert statement is occur)
    • For Update (execute when update statement is occur)
    • For Delete (execute when Delete statement is occur)

    Trigger itself add a new records in salary audit table for every changes in salary table changes like (insert update and delete).

    So now let’s start with creating DML triggers.

    1. For Insert:
    2. For Update:
    3. For Delete:

    Let test DML trigger:

    • Here I am going to execute the Update statement in salary table figure 1.
    • You can see that NAND KISHORE whose employee code is 065dg Earning 5000 per month lets change his salary to 10000.
    • There’s no need to worry about salary audit table.
    • The data go in audit table automatically when corresponding event will occur.
      Let make update query in salary table now :


      Let’s check output:

    • This will change the basic pay of NAND KISHORE to 10000.
    • . And in case you want to find previous basic pay NAND KISHORE you can check that in salary Audit Table.
    • Salary audit table hold the previous state of salary table.

    Output: Now you can see salary audit table hold all the before and after modification.

    You can see previous basic pay was 5000 and latest basic pay is 10000.

    salary audit table

    Thus, we learn about Sql triggers and implemented DML Triggers along with examples for Insert/Delete/Update statement.

    LEAVE A REPLY

    Please enter your comment!
    Please enter your name here