Introduction to Triggers

Trigger
Introduction to a Trigger

A trigger is a special stored procedure that inserts, deletes, or modifies a particular table

Data that triggers execution, it's more granular and complex than the database's own standard functionality

Data control capabilities. Database triggers have the following effects:

Security You can make the user have some right to manipulate the database based on the value of the database

Unmil

# can limit user actions based on time, such as not allowing after work and holidays

Modify database data.

# You can restrict user actions based on data in the database, such as not allowing stock

The price of an increase of more than 10%.

Audit You can track user actions on a database.

# The statement that audits the user to manipulate the database.

# Write user updates to the database to the audit table.

* Implement complex data integrity rules.

# Implementation of non-standard data integrity checks and constraints. Triggers can produce more than rules

more complex restrictions. Unlike rules, triggers can reference columns or databases to

Like. For example, triggers can roll back any futures that attempt to eat more than their margin.

# provides a variable default value.

* Implement complex non-standard database-related integrity rules. Triggers can be logarithmic

According to the related tables in the library for a serial update. For example, on the Auths table Author_code column

Deleting a trigger can result in a corresponding deletion of the rows that match in the other table.

# cascade Modify or delete rows that match in other tables when modified or deleted.

# Set the matching rows in other tables to null values when you modify or delete them.

# Set the matching rows in other tables to their default values when you modify or delete them.

# triggers can reject or rewind changes that destroy the associated integrity, cancel the test

Diagram for data update transactions. When inserting a foreign key that does not match its primary health

, this trigger will work. For example, you can books.author_code the

Generates an INSERT trigger on the column, if the new value and the Auths.author_code column

When a value in is not matched, the insertion is rolled back.

* Synchronize data in a table in real time.

* Automatic calculation of data values, if the value of the data to meet certain requirements, then the special

Fixed processing. For example, if the company's account number is less than 50,000 yuan, the financial person immediately

The member sends the warning data.

Oracle has a certain difference from the Sybase database triggers, which are described separately below

The role and writing of these two database triggers.



Two ORACLE triggers

The syntax for Oracle-generated database triggers is:

create [or replace] trigger trigger name trigger Time trigger Event

On table name

[For each row]

Pl/sql statement

which

Trigger Name: The name of the trigger object. Because a trigger is a database that is automatically executed

, so the name is just a name that has no real purpose.

Trigger time: Indicates when the trigger executes, and this value is preferable:

Before---indicates that the trigger executes before the database action;

After---indicates that the departure is performed after the database action.

Trigger event: Indicates which database actions 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 in which the database triggers are located.

For each row: executes once for every row of a table trigger. Without this

option, only the entire table is executed once.

Example: The following triggers are triggered before the table auths is updated, in order to not allow

Weekend Modification Table:

Create Trigger Auth_secure

Before insert or UPDATE or delete//to trigger before updating an entire table

On Auths

Begin

if (To_char (sysdate, ' DY ') = ' SUN '

Raise_application_error (-20600, ' cannot modify the table auths on weekends ');

End If;

End



Three Sybase database triggers

Sybase database triggers are very similar to Oracle's, with only minor differences.

The syntax for Sybase to generate triggers is:

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 (column_name)] ...

Sql_statements

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 action on the specified column, in the IF UPDATE clause

Multiple columns can be specified in.

Unlike Oracle, triggers are executed only once for each SQL statement. Trigger

Executes immediately after the data Update statement completes. The trigger and the statement that started it are treated as a

Transaction, the transaction can be rolled back in the trigger.

The following is an example of how Sybase triggers are written.

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