1. Use cases for AFTER triggers (DML)
In this lesson, we are going to see the most popular use cases for AFTER triggers in real-world scenarios.
2. Keeping a history of row changes
A common use for AFTER triggers is to store historical data in other tables.
In practice, this usually means having a history of changes performed on a table.
For example, here we have the "Customers" table containing information about existing customers.
The customers' details may change over time, and the information in the table will need to be updated.
It is considered a good practice to keep an overview of the changes for the most important tables in your database.
3. Keeping a history of row changes
To start with the "CustomersHistory" table holds exactly the same details as "Customer", but it keeps a record of any changes that are made.
4. Keeping a history of row changes
Suppose the phone number for the customer eFruits changes.
After the change, the tables will hold the following details for this customer.
The "Customers" table always shows the current information.
The "CustomersHistory" table shows all the changes that have occurred for the customer, along with the change date.
5. Keeping a history of row changes
The result you saw previously is obtained using a trigger.
The trigger will be fired for both INSERT and UPDATE statements. You can include both statements, comma-separated, as the firing actions.
This will ensure that when new rows are added to the "Customers" table they are copied to the "CustomersHistory" table, and any changes to the details of existing customers will also end up in the history table.
The INSERT statements will be taken care of using the "inserted" special table.
6. Table auditing using triggers
Another major use of AFTER triggers is to audit changes occurring in the database.
Auditing means tracking any changes that occur within the defined scope.
In this example, the scope of the audit is comprised of very important tables from the database.
A trigger will be created on the "Orders" table. It will fire for any DML statements.
Inside the trigger, we will declare two Boolean variables that will check the special tables "inserted" and "deleted".
When one of the special tables contains data, the associated variable will be set to "true".
The combination of values will tell us if the operation is an INSERT, UPDATE, or DELETE.
A table called "TablesAudit" will be used to track the changes.
The trigger will insert into that table information about the rows being modified, the user making the change, and the date and time of the change.
7. Notifying users
A simple and effective use case of triggers is to have them send notifications.
Most of the notifications will be about events happening in the database and will be sent to interested users.
For example, the Sales department must be notified when new orders are placed.
A trigger attached to the `Orders` table will execute a procedure that sends an email when INSERT statements are executed.
8. Let's practice!
Now it's time to practice what you've learned!