Trigger

1. Overview

A trigger is a stored procedure in a database that automatically invokes whenever a special event in the database occurs. For example, a trigger can be invoked when a row is inserted into a specified table or when certain table columns are being updated.

2. Description

2.1 Usages

• automatically generate derived column values
• prevent invalid transactions
• enforce complex security authorizations
• enforce referential integrity across nodes in a distributed database
• enforce complex business rules
• provide transparent event logging
• provide sophisticated auditing
• maintain synchronous table replicates
• gather statistics on table access

2.2 Pros

• Triggers can be used as an alternative method for implementing referential integrity constraints.
• By using triggers, business rules and transactions are easy to store in database and can be used consistently even if there are future updates to the database.
• It controls on which updates are allowed in a database.
• When a change happens in a database a trigger can adjust the change to the entire database.
• Triggers are used for calling stored procedures.

2.3 Cons

•  
• Triggers execute invisible to client-application application. They are not visible or can be traced in debugging code.
• It is hard to follow their logic as it they can be fired before or after the database insert/update happens.
• It is easy to forget about triggers and if there is no documentation it will be difficult to figure out for new developers for their existence.
• Triggers run every time when the database fields are updated and it is overhead on system. It makes system run slower.

3. Reference

https://docs.oracle.com/cd/A57673_01/DOC/server/doc/SCN73/ch15.htm

https://www.geeksforgeeks.org/sql-trigger-student-database/

https://www.quora.com/What-are-the-advantages-and-disadvantages-of-using-Database-Triggers