Trigger basics-trigger learning 01

Trigger
A trigger is a special stored procedure. Its execution is not called by a program, nor is it manually started, but triggered by an event, for example, when you perform operations (insert, delete, update) on a table, it is activated for execution. Triggers are often used to enhance data integrity constraints and business rules. The trigger can be found in the dba_triggers and user_triggers data dictionary.

Triggers can query other tables and contain complex SQL statements. They are mainly used to force complex business rules or requirements. For example, you can control whether to insert a new order based on the current account status of the customer.

Triggers can also be used to force reference integrity so that the relationships defined between these tables are retained when rows are added, updated, or deleted in multiple tables. However, the best way to force reference integrity is to define the primary key and foreign key constraints in the relevant table. If you use a database relationship diagram, you can create a relationship between tables to automatically create a foreign key constraint.

Role of a trigger
A trigger is a special stored procedure that triggers execution when inserting, deleting, or modifying data in a specific table, it has more refined and complex data control capabilities than the standard functions of the database itself. Database triggers have the following functions:
* Security. Users can have certain rights to operate databases based on database values.
# User operations can be restricted based on time, for example, database data cannot be modified after work or holidays.
# User operations can be restricted based on the data in the database. For example, the stock price cannot be increased by more than 10% at a time.
* Audit. You can track your database operations.
# Audit user statements for database operations.
# Write updates to the database into the audit table.
* Implement complex data integrity rules. # Implement non-standard data integrity check and constraints. Triggers can generate more complex limits than rules. Unlike rules, triggers can reference columns or database objects. For example, a trigger can be used to roll back any attempt to eat futures that exceed its deposit.
# Provides variable default values.
* Implement complex non-standard database integrity rules. Triggers can be used to update related tables in the database. For example, the deletion trigger on the author_code column of the auths table can cause the row to be deleted from other tables.
# Cascade the modification or deletion of matched rows in other tables.
# When modifying or deleting a table, set the matched rows in other tables to null values.
# When modifying or deleting a table, set the row cascade matching the table to the default value. # The trigger can reject or roll back the changes that undermine the integrity of the relevant, and cancel the transactions that attempt to update the data. This trigger works when an external key that does not match its primary key is inserted. For example, in books. author_code
An Insert trigger is generated for the column. If the new value does not match a value in the auths. author_code column, the insertion is rolled back.
* Synchronize the data in the table in real time.
* Automatically calculates the data value. If the value meets certain requirements, specific processing is performed. For example, if the company account has funds less than 50 thousand yuan, the company immediately sends a warning data to the finance staff.

 

Create trigger tr_name On Table/View {For | after | instead of} [update] [,] [insert] [,] [delete] [With encryption] As {batch | if Update (col_name) [{and | or} Update (col_name)]} Note: 1 tr_name: trigger name 2 On Table/view: the table to which the trigger applies. A trigger can only act on one table 3 For and after: Synonymous 4 after and instead of: SQL 2000 new project afrer and instead of difference After It is activated only after the trigger event occurs. It can only be created on a table. Instead Instead of triggering the event, it can be created on a table or view. 5. Insert, update, and delete: three operations for activating a trigger can be performed simultaneously. 6 if Update (col_name): indicates whether the operation has an impact on the specified column. If so, the trigger is activated. In addition, because the delete operation only affects rows, Therefore, if you use the delete operation, you cannot use this statement (although there is no error in use, it does not make sense to activate the trigger ). 7. Two Special Tables Used for trigger execution: deleted and inserted Deleted and inserted are a special temporary table that is automatically generated by the system when the trigger is activated. Its structure and the table structure of the trigger are Only the data stored is different. The following table describes the differences between deleted and inserted data. Differences between deleted and inserted data Inserted Store data after insert and update operations Deleted Store data before the delete and update operations Note: The update operation is equivalent to performing the delete operation before the insert operation. Therefore, when performing the update operation, copy the data before the modification to the deleted table. After the modification When the data is stored in the table to which the trigger acts, a copy is also generated to the insered table.