What are the advantages and disadvantages of triggers in SQL?

A trigger can be considered as a stored procedure that is run automatically by the database whenever some event (usually a table update) happens. Triggers can be classified in two categories depending on the level where it is triggered. They are:

  1. Row Level Trigger – An event is triggered whenever a row is inserted, updated or deleted.
  2. Statement Level Trigger – An event is triggered whenever a statement is executed.

Advantages of Triggers

  • Triggers provides an alternative way to check integrity.
  • Triggers can catch the errors in business logic in the database level.
  • With triggers, 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.

Disadvantages of Triggers

  • SQL trigger only can provide extended validation and cannot replace all the validations. Some simple validations can be done in the application level.
  • 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.

Leave a Comment

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