Be it to implement business protocols or perform validation or enforce data modifications, triggers are the best means to serve the purposes in comparison with the other methods which are not sufficient enough. Generally, triggers are utilized in two cases namely, during the creation of audit records so as to reflect the changes as decisive business tables and also to validate the changes against the business protocols.

In this article, you would learn the nitty-gritty of triggers, syntax, and the need to use them.

Introducing Triggers

In a database, triggers are referred to as stored procedures or algorithms that are invoked automatically upon the occurrence of a predefined event. Database administrators are allowed to create new and more relationships between distinct databases through triggers.

An alternate, well-suited definition to trigger is as follows: An operation that is executed automatically, before or after an update, insert or delete operation, either once after the modification of a row or once after the execution of a query.  When a triggering event occurs, the said event is tackled at the apt time by the trigger function.

Trigger functions can be applied to both tables and views. It must be noted that there are two basic types of triggers:

• Instead Of
• After

Nonetheless, of these two forms of triggers, only the Instead Of trigger can be applied to views, upon the execution of an Update action. On tables, both the forms of triggers are plausible. And the After the trigger is fired upon the execution of a modification action.

In the light of performance, however, lesser the triggers, better the invoking process. It must, therefore, be understood that a single trigger action can degrade the performance. This is because, the main overhead of the triggers is referencing two special tables which are existing in triggers, either inserted or deleted or updated. Also, triggers are associated with only one table. When triggers are being fired in two or more tables, it is not plausible to generate triggers that fire then.

Syntax of Triggers

create trigger trigger name on table name
[with encryption]
[for / after / instead of]
[insert / update / delete]
[not for replication]
as
begin
----SQL queries----
…
end

The Necessity to Use Triggers

Triggers shall predominantly be utilized to enhance data integrity. Through triggers, firms can cross-check if, upon the execution of action on data, the resulting manipulated data yet abides by the fundamental business rules to eliminate erroneous and flawed entries.

For instance, consider a scenario where a business protocol calls for shipping a free item to a client who shops over 10,000 INR. So, a trigger is built to check whether the total amounts to the required figure upon the completion of placing the orders.

Similarly, in a banking instance, consider that a requisition is processed to debit cash. The trigger here would generate a record for withdrawal on the requester’s statement table. The trigger then automatically reduces the balance amount in the bank in concordance with the debited amount. Further, it can also be functioned with a check to verify that there exists a balance on the client’s end. Thus, having a trigger in the banking sector, we would garner confidence that crediting and debiting cash would be duly recorded in the statement table, and shall be thoroughly processed in one fundamental unit.

Besides these two aforementioned real-time trigger use cases, triggers can also be utilized to process action upon satisfying the required criteria. A case in point is where an email sends the list of items that require delivery.

Nonetheless, one needs to be careful while inserting data to another table from inside a trigger, so that the target table wouldn’t already host a trigger that shall fire upon firing the first trigger. It is plausible to create triggers that make an endless loop. For instance, consider a trigger acting on Table A, which has to insert values to Table B, so that the trigger in Table B would update values in Table A. Such confusing, and endlessly looped triggers are bound to cause errors. Therefore, coding such triggers is not advisable.

Recapitulation

• Triggers – Procedures that are executed automatically upon the execution of a predefined action
• Trigger Operations – Insert, Update, Delete
• Trigger Types – Instead Of, After
• Use Cases – Business, Banking, etc.
• Note: Refrain from generating triggers that result in endless loops

Related Blogs

1. SQL Joins
2. Schema in SQL
3. Decode in SQL
4. CASE Statement in MySQL
5. Normalization in SQL
6. SQL ORDER BY Clause
7. LIKE Operator in SQL
8. SQL Views
9. SQL Concatenate Function
10. What are the Manipulation Functions in SQL
11. Primary Key In SQL
12. SQL DateTime
13. SQL Functions