Database Trigger

In database, trigger is a set of SQL statements or queries that automatically fires actions when a specific operation occurs. It is used to ensure the coordinated performance of related actions. All major relational databases support triggers, but their implementing is different. This article gives a brief introduction to Triggers in SQL. Summary of the article:

• What is Database Trigger?
• Trigger VS SP
• Types of Trigger
• Methods of Trigger
• Advantages of triggers
• Disadvantages of trigger

What is Database Trigger?
A database trigger or simply trigger is a special kind of Stored Procedure (SP) or SQL statements that executes automatically when a predefined event occurs (such as changing data in a table) in the database. A trigger consists of an event (INSERT, UPDATE, or DELETE statement) and an action. The triggers are event-driven specialized procedures and stored in and managed by the Database Management System (DBMS). Generally we can’t call or execute a trigger. The database system automatically fires them. Each trigger is attached to a single table of the database. Triggers can be viewed as stored procedures. Trigger acts on the table not one the views.

Trigger VS SP
Someone may confused on SQL stored procedure and trigger. Because, both are SQL statement. The basic difference between normal stored procedure (SP) and triggers are given bellow:

• No need to call it for execution
• It can execute automatically
• No parameter support

Types of Trigger
The database triggers has different types. The types are given bellow:

• Row Trigger – execute for each row of table
• Statement Trigger – execute once per statement like – insert, delete, update

Methods of Trigger
The database trigger has some methods. The methods of database trigger are as follows:

• Before Trigger
• After Trigger
• Instead of Trigger

Advantages of triggers
A SQL trigger provides lot of advantages. The benefits of SQL triggers are given bellow:

• It provides an alternative way to check the data integrity or reliability
• It offers to catch errors in business logic in the database layer
• It provides an alternative way to run scheduled tasks. We don’t have to wait to run the scheduled tasks Because, the triggers are executed automatically when any changes is mad on the table
• It can audit the changes of data in the table
• It offers to call stored procedure
• It provides the facility to adjust the entire database after any transaction

Disadvantages of trigger
A SQL triggers has some disadvantages. The drawbacks of SQL triggers are given bellow:

• It decreases in performance of the database. For any single modification the trigger executes. It makes the database system slower
• It increases the complexity of the database
• Sometimes programmers don’t have the full control over the database. In triggers every rule or logic’s are staying hidden
• Triggers are not visible and can’t be traced in debugging. They are executed invisibly to client applications
• If proper documents are not present then it is very difficult to recognize for new developers

It is very important to understand SQL trigger’s advantages and disadvantages so that one can use it appropriately. If there is now way then use trigger, unless use SQL stored procedure. It is recommended to keep the trigger as short as possible.