Difference between a BEFORE trigger and an AFTER trigger

In most general explanations, whatever happens before the data manipulation language (DML) operations, i.e., INSERT, UPDATE, DELETE, is considered BEFORE trigger, and whatever happens after these are considered AFTER triggers. In database management systems, like SQL databases, BEFORE triggers and AFTER triggers are essential components employed to automate and manage the execution of specific actions, typically SQL statements, triggered by events like INSERT, UPDATE, or DELETE operations on a table. Nevertheless, their primary distinction lies in the timing of their execution relative to the triggering event.

Let’s create a table and see the difference between the before and after triggers. The code below creates an Author table. We are assuming that the authors are working for some company.

Explanation

• Lines 1–7: We create an Author table which has the author_id, author_first_name, author_last_name, author_rank, author_salaray variables.

• Lines 9–13: We create a table for TriggerLog which has the login_id, last_modified_data, and created_by variables.

Now, let’s first understand what BEFORE and AFTER triggers are and code the example to understand the difference.

The BEFORE Trigger

• Use cases: The BEFORE triggers are commonly used for data validation, transformation, and cleansing. They can prevent incorrect or inconsistent data from being inserted or updated in a table.

  • Data validation: When inserting, updating, or deleting data and performing validation on it, the validation process typically occurs within an event.

  • DML timing: If we want to perform DML on the current record, i.e., the same record, we must use it before the event.

  • System field: These include, ID or Created by, are automatically generated in the AFTER region. If your trigger logic involves these fields, they will have null values in the BEFORE region. When using these system fields, ensure that you access them before the relevant event.

Now, let’s understand before triggering with the help of code examples. In the example below, we want to give the newly employed author a 20 percent bonus on his salary before the insertion occurs.

Explanation

• Line 1: We set the delimiter to // for the trigger definition.

• Line 2: We begin the creation of a trigger named before_author_insert.

• Line 3: We specify that the trigger is activated before an INSERT operation on the Author table.

• Lines 5–7: We mark the beginning of the trigger, then add the query to evaluate the bonus assigned and then mark the end of the trigger.

The AFTER trigger

• Use cases: The AFTER triggers are often used for logging, auditing, or actions that depend on the final state of the data after the change has occurred.

• DML Timing: When executing DML operations on records of another object and the current record is in a read-only state during the AFTER event, remember to always choose the AFTER event in such cases.

• System Field: If we use system fields in our trigger logic, go for AFTER events. System fields mean the same fields we discussed, like ID, Created by, and Last modified, because these values are populated only in the after event.

Explanation

• Line 1: We set the delimiter to // for the trigger definition.

• Line 2: We begin the creation of a trigger named after_author_insert.

• Line 3: We specify that the trigger is activated after an INSERT operation on the Author table.

• Lines 5–8: We mark the beginning of the trigger, then give the query and then mark the end of the trigger.

Now, let’s check what happens AFTER and BEFORE the insertion of an author:

Explanation

• Lines 1–2: We insert the values in the Author table.

• Line 4: We display the Author table.

• Line 6: We display the TriggerLog table.

Conclusion

The key differences between both triggers are explained with the help of the table below.

Note: To learn about SQL statements in detail see this Answer.