Triggers in SQL
Before vs instead of vs after
In this article, we will talk about triggers, their types, use cases, pros and cons of each one.
what are triggers in general?
Triggers are special types of stored procedures that automatically execute when certain events occur in the database
----------------------------------------------------------------------------------------------------------------------------
Before Trigger
Is a trigger executed before specific events [insert-update-delete]
Pros
1-Prevent invalid data from being inserted or updated by raising exceptions.
2-Modify values to be inserted or updated before the actual operation occurs.
Cons
1 it can cause additional processing overhead before the main DML operations
2-it can be complex to manage, especially with multiple triggers on a table.
Use Cases
- Validating or modifying the data before main DML operations
----------------------------------------------------------------------------------------------------------------------------
Instead of Trigger
Is a trigger that can Replace the event with the code inside.
Pros
1-Allow complex views to be updatable by intercepting the DML operation and replacing it with custom logic.
2-Useful for handling operations on views that do not directly support insert, update, or delete.
Cons
1-Also can add complexity and overhead to the database.
2-If you don't document these triggers well the maintenance will be challenging.
Use Cases
1-Making non-updatable views updatable.
2-Handling complex logic that cannot be performed directly with standard DML operations.
----------------------------------------------------------------------------------------------------------------------------
After Trigger
Is a trigger executed after a certain event [insert-update-delete].
Pros
1-Ensure that the main operation has been successfully completed before the trigger fires.
2-Useful for actions that depend on the successful completion of the DML operation, such as logging changes or updating related tables.
Cons
1-Cannot prevent invalid data from being inserted or updated.
2-Also can add additional processing overhead after the main DML operation.
3-If you don't document these triggers well the maintenance will be challenging.
Use Cases
- Tracking changes to data.
----------------------------------------------------------------------------------------------------------------------------
At the end
Choosing between these triggers depends on the specific requirements of your database operations.
Use before triggers for validation, instead of triggers for handling complex
logics, and after triggers for Tracking activities happened on the database.
Check This link for further information about triggers