In SQL, a trigger is a special stored procedure that is run when a specific action occurs in the database. They are called triggers because their execution is fired by an event such as adding a record to a table. They are mostly designed to run when changes are made to data in a table. A trigger can be set to run after or instead of Data Manipulation Language (DML) actions like DELETE, UPDATE, and INSERT.
Triggers help a database designer perform essential functions like maintaining an audit file. They ensure that these actions are performed regardless of which user or program makes changes to data. Also, triggers and their implementation are database vendors –specific. In this content, we will be using the Microsoft SQL server to give examples and explain the concepts. However, you should not fret because the concepts are the same in both MYSQL and Oracle.
Like we mentioned above, a trigger can occur instead of or after a DML action. As a result, they are associated with DML database actions like DELETE, UPDATE and INSERT. Triggers will run when a database user executes these actions on a specific table.
The AFTER triggers
The AFTER triggers are executed after DML actions like INSERT completes. We have highlighted the following as some of the key characteristics of the AFTER triggers:
- AFTER triggers run after any ensuing referential cascade and constrain checks or DML actions have run.
- A database action using the AFTER trigger has already completed and cannot be canceled
- We can define one or more AFTER triggers per action on a table. However, it is recommended that you only define one.
- AFTER triggers on view cannot be defined
INSTEAD OF triggers
As their name suggests, INSTEAD OF triggers run in place of the DML action that caused them to execute. You should consider the following when using INSTEAD OF triggers:
- This type of trigger overrides the triggering action. For example, if we define an INSTEAD OF trigger to execute on an INSERT statement, then control will be immediately passed to the trigger once the INSERT statement attempts to run.
- We can define at most one INSTEAD OF trigger per action for a table.
Special Database Objects
Two special database objects; INSERTED and DELETED, are used by triggers to access rows affected by database actions. These objects have the same columns as the trigger’s table within the scope of a trigger.
All the new values are in the INSERTED table whereas, the old values are contained in the DELETED table. The tables are used in the following manner:
- INSERT – It uses the INSERTED table to know rows that have been added to the table.
- DELETE – It determines the rows removed from the table by using the DELETED table
- UPDATE – Inspects the updated or new values by using the INSERTED table. It also uses the DELETED table to see values before they are updated.
The syntax of a trigger is almost the same as that of a stored procedure. In fact, triggers use similar language as do stored procedures to implement its logic. Our SQL assignment help professionals say the following are some of the important parts of a trigger:
- The CREATE statement
This is the statement that defines the table that is associated with the trigger. Also, it specifies when a trigger executes.
- The program
It runs whenever an action is made in the tables of the database.
- Special database objects
Specially defined database objects like INSERTED or DELETED are used by triggers to access records that have been affected by the database action.
The uses of Triggers
- Intricate Auditing
Triggers are used to track changes made to tables. AFTER triggers are typically used to create audit trails. Although this may seem redundant as many changes are logged in the database journals, the logs are used for database recovery and cannot be accessed by user programs.
- Enforcing business rules
Triggers can inspect all data before a DML action is executed. For example, the database manager can use the INSTEAD if triggers to intercept the pending DML operation, apply the business rules and finally complete the transaction.
- Deriving column values
We can also use triggers to calculate column values, For example, a business entity may wish to maintain a TotalSales column of the customer record for each customer. For this to remain accurate, we will have to update it every time a sales is made. WE can do this using the AFTER trigger on the INSERT, UPDATE and DELETE statements for the sales table.