Simply put, where is the event trigger.
For example, if you insert or delete a table in the database, you want the program to automatically perform other operations when you are about to perform or complete the operation. For example, you want to check the inserted data or calculate the total number of deleted data.
Initially, you can write this job in your own program, that is, write the check before the insert operation or write the statistics after the delete operation. The problem is that if you want to insert or delete these codes, you have to write them, which is easy to miss.
As for the trigger, you define it in an operation, such as defining the workflow of that inspection as the pre-trigger of insertion and defining the statistical work as the post-trigger, so when you insert and delete, the program on the database side will automatically do these jobs for you.
What is the main function: I feel
One is integrity (to prevent your own programming from missing),
Second, simplicity.
Third, database programs (such as Oracle) do this work, not your own programs, which is efficient.
Here are some tutorials, but the reason is simple. You can use the examples of several databases mentioned later to write one yourself.
————————————————————————————————————————
Introduce trigger
A trigger is a special stored procedure that inserts, deletes or modifies a specific table.
It is more elaborate and complex than the standard function of the database itself.
Data control ability. Database triggers have the following functions:
* Security. According to the value of the database, users can be granted some rights to operate the database.
Li.
# You can restrict users' actions based on time, such as not allowing non-working hours and holidays.
Modify database data.
# You can restrict the user's operation according to the data in the database, such as not allowing stocks.
The price of our products has gone up by more than 10% at one time.
:: Audit. You can track users' operations on the database.
# Audit the statement of the user who operates the database.
# Write user updates to the database into the audit table.
:: Implementing complex data integrity rules.
# Implement non-standard data integrity check and constraint. Triggers can generate ratio rules.
More complicated restrictions. Unlike rules, triggers can refer to columns or database pairs.
Elephant. For example, a trigger can take back any futures that try to eat more than its own margin.
# Provide a variable default value.
:: Implementing complex non-standard database-related integrity rules. Flip-flops can be logarithmic
According to the related tables in the library, serial update is carried out. For example, on the author_code column of the Authors table.
Deleting a trigger will result in the corresponding deletion of matching rows in other tables.
# When modifying or deleting, modify or delete matching rows in other tables in cascade.
# When modifying or deleting, set the matching rows in other tables to null values.
# When modifying or deleting, set the matching row cascade in other tables as the default value.
# Triggers can reject or return those changes that destroy the integrity of the correlation and cancel the test.
Data updates the number of transactions. When inserting a foreign key that does not match its primary key.
The trigger will be set in the. For example, it can be found in books.author_code.
Column, if the new value is the same as the auths.author_code column.
When the values in do not match, the insert operation will be rolled back.
* Real-time synchronization and replication of data in the table.
* automatically calculate the data value. If the data value meets certain requirements, it is special.
Fixed processing. For example, if the fund in the company account is less than 50,000 yuan, give it to the financial person immediately.
Member sends warning data.
There are some differences between the triggers of ORACLE and SYBASE databases, which will be described separately below.
The function and writing of these two database triggers.
Two ORACLE triggers
The syntax for ORACLE to generate database triggers is:
Create [or replace] trigger trigger name trigger time trigger event.
About table name
[each line]
Pl/sql statement
These include:
Trigger Name: the name of the trigger object. Because the trigger is automatically executed by the database
So the name is just a name and has no substantive use.
Trigger Time: Indicates when the trigger is executed. This value is acceptable:
Before-indicates that the trigger is executed before the database operation;
After-indicates that the startup program is executed after the database operation.
Trigger Event: Indicates which database operations will trigger this trigger:
Insert: database insert will trigger this trigger;
Update: Database modification will trigger this trigger;
Delete: Deleting the database will trigger this trigger.
Table Name: the table where the database trigger is located.
For each row: trigger once for each row of the table. Without this.
Option, which is executed only once for the entire table.
Example: The following trigger was triggered before updating table auths, so it is not allowed in.
Weekend modification table:
Create Trigger Authentication _ Security
Triggered before insert or update or delete//before updating the whole table.
About authorization
begin
if(to_char(sysdate，' DY')='SUN '
RAISE_APPLICATION_ERROR(-20600,' table auths cannot be modified on weekends.
End if;
end
Ternary database trigger
The functions of SYBASE database triggers are very similar to those of ORACLE, with only minor differences.
The syntax for SYBASE to generate triggers is:
Create trigger trigger name
About table name
Used to insert, update, delete
be like
SQL _ statement |
For insert, update
be like
IF UPDATE(column _ name)[AND | OR UPDATE(column _ name)] ...
SQL _ statement
The above FOR clause is used to specify which data update commands on the trigger can be activated.
Trigger. The IF UPDATE clause checks the type of operation on the specified column, and in the IF UPDATE clause
You can specify multiple columns in.
Unlike ORACLE, triggers are executed only once for each SQL statement. trigger
The data update statement is executed immediately after completion. The trigger and the statement that starts it are considered as a whole.
Transactions, which can be rolled back in triggers.
The following example illustrates the writing of SYBASE triggers.
Create a trigger for insert_books
In the book
Used to insert
be like
if(select count(*) from auths，inserted
Where auths. author _ code = insert. author _ code)! =@@rowcount
begin
Rollback transaction
"The value of the author_code column in the books table does not exist in the authors table."
end