A trigger introduction
The trigger is a special stored procedure, which inserts, deletes, or modifies a specific table
Trigger execution when data is performed, it is more fine and more complex than the database itself.
Data control capabilities. The database trigger has the following role:
* safety. You can make the user have a certain weight of the database based on the value of the database.
Benefit.
# You can limit the user's operation based on time limit, for example, after get off work and holidays are not allowed.
Modify the database data.
# You can limit the user's operation based on data in the database, such as not allowing stocks.
The price is increased by more than 10%.
* Audit. You can track the user's operation on the database.
# Audit the statement of the user operating the database.
# Write the user's update to the audit table.
* Implement complex data integrity rules.
# Implement non-standard data integrity checks and constraints. Trigger can produce a comparative rule
More complicated restrictions. Unlike rules, triggers can reference columns or database pairs
Icon. For example, the trigger can fall back to any attempted to eat futures that exceed their own margin.
# Provides a variable default value.
* Implement complex non-standard database-related integrity rules. Trigger can log
According to the related table in the library, the serialization is updated. For example, on the auths table author_code column
Deleting triggers can cause the corresponding to delete the matching rows in other tables.
# Modify or delete time-class modifications or delete the matching lines in other tables.
# Seize the line in other tables into a null value when modifying or deleting.
# Connect the row in other tables to the default value when modifying or deleting.
# Trigger can reject or roll back the changes of destruction related integrity, cancel the test
The map performs data update transactions. When inserted into an external key that does not match its maintenance
This trigger will work. For example, you can be in books.author_code
A plug-in trigger is generated on the column, if the new value is column with the auths.author_code
When a value in a certain value does not match, insertion is rolled back.
* Synchronize the data in the table in real time.
* Automatically calculate the data value, if the value of the data reaches a certain request,
Treatment. For example, if the funds on the company's account are less than 50,000 yuan, they will give a finance.
Member sends a warning data.
Oracle has a certain difference from the trigger of the Sybase database, which will be described below
The role and way of writing of these two database triggers.
Two Oracle trigger
Oracle generates the syntax of the database trigger:
Create [or replace] Trigger trigger name trigger time trigger event
ON table name
[for Each Row]
PL / SQL statement
among them:
Trigger Name: The name of the trigger object. Since the trigger is automated
Therefore, the name is just a name, there is no substantive purpose.
Trigger time: Indicate when the trigger is executed, this value is available:
Before --- indicates that the trigger is executed before the database action;
After --- indicates the start of the start after the database action.
Triggering events: indicating which database moves trigger this trigger:
INSERT: Database insert triggers this trigger;
Update: Database modification triggers this trigger;
DELETE: Database deletion triggers this trigger.
Table name: The table where the database trigger is located.
For Each ROW: Performs each row trigger of the table once. If there is no such
Options, only the entire table is performed once.
Example: The following trigger is triggered before updating table auths, and the purpose is not allowed
Weekend modification table:
CREATE TRIGGER AUTH_SECURE
Before insert or update or delete / / Trigger before updating
On Auths
Begin
IF (TO_CHAR (Sysdate, 'DY') = 'Sun'
RAISE_APPLICATION_ERROR (-20600, 'can't modify table auths' on weekends;
end
Three Sybase Database Trigger
The role of the Sybase database trigger is very similar to Oracle, only a small difference.
Sybase generates the syntax of the trigger:
Create Trigger Trigger Name
ON table name
For INSERT, UPDATE, DELETE
AS
SQL_STATEMENT |
For Insert, Update
AS
If Update (Column_name) [and | or update] ...
SQL_STATEMENTS
The above for clause is used to specify which data update commands on the trigger can be activated
trigger. If the IF update clause checks the type of operation of the specified column, in the if update clause
Multiple columns can be specified.
Unlike oracle, for each SQL statement, the trigger is only executed once. trigger
Perform it immediately after the data update statement is completed. The trigger and the statement that starts it are treated as one
Transaction processing, transaction can fall back in the trigger.
The following is an example of the writing of the Sybase trigger.
Create Trigger forinsert_books
On books
For insert
AS
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 PRINT "Books table does not exist in the AuthS table."
end
