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:
- Row Level Trigger – An event is triggered whenever a row is inserted, updated or deleted.
- 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.