Modern databases are so complex and the data in them are so interconnected that special procedures are needed to protect them from accidental violation. Triggers allow you to keep all data intact, even if an inexperienced user accidentally presses the wrong button.
The main purpose of a trigger is to preserve the referential integrity of the data. This means that even if the database changes, there is always the option to revert everything back. They can also be used to make cascading changes in linked tables, in which links change at the same time and do not break the links.
The trigger itself is a stored procedure that runs automatically when data changes by a person or an application program. It "turns on" as soon as the data change is complete. The data change and the triggered trigger are considered as one transaction (action), so when an error occurs or is detected, everything can be returned back, this is called rolled back.
Trigger operation
- Cascading changes in related data tables. For example, if you want to delete a specific column or row in all tables at once, a trigger is used.
- Reverting to the original table data
- Tracking various matches. For example, a trigger schema might prohibit the price of an item from decreasing below the purchase price.
- Analysis of various options for changes. The trigger is very convenient for calculating options before and after modification. For example, you can calculate what will happen if all prices are reduced by 5% or by how much the cost of all goods will increase with an increase in transportation costs. After analysis, all data can be returned to its original form.
Create a trigger
Triggers are created in the current database, but you can specify objects located in other databases in them. The trigger owner name must be the same as the table owner name. Create a trigger in the Creat clause. The For field specifies data change statements after activation of which the trigger should be turned on. For example, it could be insert, update, or delete on a table.
Next, you should specify the trigger actions or trigger conditions. These are the actions that must be followed in response to the insertion, deletion, or update of data.
