Database Trigger Use Complete Example

Database Trigger Use Complete Example

Trigger is a database function which is automatically gets executed in response to certain events like insert, update or delete on a particular table or view. Mostly trigger used for maintaining integrity of the information on database and it can not be explicitly invoked to achieve the output. Let’s say for example we have an employee table where data’s are normalized in many other table Salaries, Performance so whenever new record inserted for new employee in employee table all data should also get populated in Salaries and Performance table.

Types of Trigger: 

  • AFTER Triggers
  • BEFORE Triggers
  • INSTEAD OF Triggers
  • Triggers on System Events and User Events
  • Row Triggers and Statement Triggers

AFTER Triggers:  This triggers execute whenever any DML statement fired against the table like insert, update or delete. These trigger only supported for table not the views. Below are its sub types:

  • AFTER INSERT Trigger
  • AFTER UPDATE Trigger
  • AFTER DELETE Trigger

To understand these triggers by example we will have to create the table and insert some sample data’s: 

  • Check if data is inserted or not:

Database Trigger Use Complete Example

  • Now create Employee helper table name Employee_History so whenever new employee record inserted into Employee table we could fire trigger to insert record in this table to keep history of record for audit purpose.

  • AFTER INSERT Trigger: Now we have test tables are in place, lets create after insert trigger which will insert the record in the table whenever data is inserted into Employee table:

  • As you see above we gave trigger name then “on dbo.Employee” means it will be fired against Employee table “for insert”. Then we have declared variable and mapped it with inserted data’s, once all data’s populated then insert statement will insert the data into Employee_History table. Now to test let’s insert one rows in Employee table:

  • Now check Employee_History table if trigger fired and data inserted or not and as you see below trigger is fired and data got inserted in the table:

Database Trigger Use Complete Example

  • AFTER UPDATE Trigger: This trigger works same way as insert trigger, only difference is this trigger will be fired only if any update statement executed on Employee table. Let’s create this trigger:

  • Execute update statement on Employee and see if data’s updated on Employee_History table or not:

  • Check the table if data updated or not. As you see trigger got fired and data’s is updated:

Database Trigger Use Complete Example

  • AFTER DELETE Trigger: It also work on same way as other two works let’s create delete trigger on Employee table:

  • Execute delete statement on table Employee and see if record is deleted or not:

  • Check if data in the table where Id=6 deleted or not. It’s got deleted:

Database Trigger Use Complete Example

Reference:

Leave a Reply

Your email address will not be published. Required fields are marked *