Trigger is a special stored procedure that triggers execution when data in a specific table is inserted, deleted or modified. It has more precise and complex data control ability than the standard function of the database itself. Database triggers have the following functions:

* Security. According to the value of the database, users can be granted some rights to operate the database.

# You can restrict the user's operation based on time, for example, you are not allowed to modify the database data after work and holidays.

# You can restrict the user's operation according to the data in the database, for example, you are not allowed to raise the stock price by more than 10% at a 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 create restrictions that are more complex than rules. Unlike rules, triggers can refer to columns or database objects. 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. Triggers can update related tables in the database in the form of chains. For example, the delete trigger on the author_code column of the Authors table will cause 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 roll back changes that undermine the integrity of the correlation and cancel transactions that attempt to update data. This trigger works when inserting a foreign key that does not match its primary key. For example, you can generate an insert trigger on the books.author_code column if the new value does not match the value in authors. Author _ code column, the insertion 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, carry out specific processing. For example, if the fund in the company account is less than 50,000 yuan, it will immediately send early warning data to the financial personnel.

There are some differences between ORACLE and SYBASE database triggers, and the functions and writing methods of these two database triggers will be introduced respectively below.

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, 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, it is only executed once for the whole table.

Example: The following trigger is triggered before updating table auths, so it is not allowed to modify the table on weekends:

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 activate the trigger. The IF UPDATE clause checks the type of operation on the specified column, and multiple columns can be specified in the IF UPDATE clause.

Unlike ORACLE, triggers are executed only once for each SQL statement. The trigger is executed immediately after the data update statement is completed. The trigger and the statement that starts it are regarded as a transaction, which can be rolled back in the trigger.

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."