Introduction to SQL Triggers

SQL trigger is an SQL statements or a set of SQL statements which is stored to be activated or fired when an event associating with a database table occurs. The event can be any event including INSERT, UPDATE  and DELETE.
Sometimes a trigger is referred as a special kind of stored procedure in term of procedural code inside its body. The difference between a trigger and a stored procedure is that a trigger is activated or called when an event happens in a database table, a stored procedure must be called explicitly. For example you can have some business logic to do before or after inserting a new record in a database table.

Before applying trigger in your database project, you should know its pros and cons to use it properly.

Advantages of using SQL trigger

• SQL Trigger provides an alternative way to check integrity.
• SQL trigger can catch the errors in business logic in the database level.
• SQL trigger provides an alternative way to run scheduled tasks. With SQL trigger, you don’t have to wait to run the scheduled tasks. You can handle those tasks before or after changes being made to database tables.
• SQL trigger is very useful when you use it to audit the changes of data in a database table.

Disadvantages of using SQL trigger

• SQL trigger only can provide extended validation and cannot replace all the validations. Some simple validations can be done in the application level.  For example, you can validate input check in the client side by using javascript or in the server side by server script using PHP or ASP.NET.
• SQL Triggers executes invisibly from client-application which connects to the database server so it is difficult to figure out what happen underlying database layer.
• SQL Triggers run every updates made to the table therefore it adds workload to the database and cause system runs slower.

Triggers or stored procedures? It depends on the the situation but it is practical that if you have no way to get the work done with stored procedure, think about triggers.