What is Trigger in SQL?
Triggers in Structured Query Language (SQL) are procedural code that executes automatically in response to certain events on a table in a database, helping enforce business logic and maintain data integrity. Triggers can be used for tracking changes made in tables as well as validating data before inserting or updating. They’re also extremely useful for enforcing complex security authorizations and exception handling; making them particularly well suited to DevOps environments to accelerate development processes and deployment times for database changes.
An event such as an INSERT, UPDATE or DELETE occurs on a table, setting off its trigger. With regard to an INSERT event, this trigger fires when the new record is created while for UPDATE or DELETE events this occurs when existing records are updated or deleted from the table or view respectively. Triggers can be created when creating either table or view as part of its definition – though typically created at that same time and dropped using the DROP TRIGGER command at any point later time.
An array of attributes can be set for a trigger in the DBMS dictionary to regulate its functionality, such as its name, function and table or view attachment. When an SQL statement causing it to fire is executed, its function executes within a PL/SQL subprogram which executes when that statement completes. It may make use of variable declarations or returns a value or raises an exception (with both values must match those specified in its triggering statement to avoid conflict).
WHEN clause of table-attached triggers specifies when they should be invoked. It may occur BEFORE or AFTER (before constraint checks have taken place, and an attempt made at inserting or updating); after (but before completion); or instead (INSERT OR UPDATE on table). Transition relations supplied to trigger specify which rows will be affected by operation – these should include both old and new columns referenced within WHEN clause, and in case of insert or update action being specified then list of table columns to insert or update before invoking or instead of iterating operation itself.
The FOR EACH ROW clause instructs triggers on whether to call their trigger for every row affected by an operation, or only once per SQL statement. This can help reduce trigger overhead when used to handle large scale operations involving many rows; however, inefficient FOR EACH ROW triggers may call back every time something changes on individual rows; alternatively implementing transaction-level rollback may be more suitable as discussed under “Using Transactions with Triggers”.