Shulou(Shulou.com)06/02 Report--

An ORACLE trigger syntax

Http://www.zw1840.com/oracle/translation/concepts/html/22.Triggers.htm

Http://www.zw1840.com/oracle/index.htm

A trigger is a block of code that executes automatically when a particular event occurs. Similar to stored procedures, triggers differ from stored procedures in that stored procedures are explicitly called by the user or application, while triggers cannot be called directly.

Features:

1. Allow / restrict changes to the table

2. Automatically generate derived columns, such as self-incrementing fields

3. Enforce data consistency

4. Provide audit and logging

5. Prevent invalid transactions

6. Enable complex business logic

There are two trigger times: after and before.

1. Syntax of trigger: CREATE [OR REPLACE] TIGGER trigger name trigger time trigger event

ON table name

[FOR EACH ROW]

BEGIN

Pl/sql statement

END

Where:

Trigger name: the name of the trigger object.

Because the trigger is executed automatically by the database, the name is just a name and has no real use.

Trigger time: indicates when the trigger is executed. This value is desirable:

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

After--- indicates that the issuer executes 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 where the database trigger is located.

For each row: execute once for each row trigger of the table. If this option is not available, it is executed only once for the entire table.

2. For example: the following trigger triggers before updating the table auths to disallow table modification on weekends:

Create trigger auth_secure before insert or update or delete / / triggered before updating a pair of entire tables

On auths

Begin

If (to_char (sysdate,'DY') = 'SUN'

RAISE_APPLICATION_ERROR (- 20600) 'cannot modify the table auths' on weekends)

End if

End

Example:

CREATE OR REPLACE TRIGGER CRM.T_SUB_USERINFO_AUR_NAME AFTER UPDATE OF STAFF_NAME

ON CRM.T_SUB_USERINFO

REFERENCING OLD AS OLD NEW AS NEW

FOR EACH ROW

Declare

Begin

If: new. Staf name name then

Begin

-- customer complaints

Update T_COMPLAINT_MANAGE set SERVE_NAME=:NEW.STAFF_NAME where SERVE_SEED=:OLD.SEED

-customer Care

Update T_CUSTOMER_CARE set EXECUTOR_NAME=:NEW.STAFF_NAME

Where EXECUTOR_SEED=:OLD.SEED

-- customer service

Update T_CUSTOMER_SERVICE set EXECUTOR_NAME=:NEW.STAFF_NAME

Where EXECUTOR_SEED=:OLD.SEED

End

End if

End T_sub_userinfo_aur_name

/

Detailed explanation of two Oracle triggers

Start:

Create trigger biufer_employees_department_id

Before insert or update of department_id on employees

Referencing old as old_value new as new_value

For each row

When (new_value.department_id80)

Begin

: new_value.commission_pct: = 0

End

/

1. The components of the trigger:

1. Trigger name

2. Trigger statement

3. Trigger limit

4. Trigger operation

1.1, trigger name

Create trigger biufer_employees_department_id

Naming habits:

Biufer (before insert update for each row)

Employees table name

Department_id column name

1.2. Trigger statement

For example:

DML statements on a table or view

DDL statement

Database shutdown or startup, startup shutdown, etc.

Before insert or update

Of department_id

On employees

Referencing old as old_value

New as new_value

For each row

Description:

1. Whether department_id is specified or not, when insert the employees table

2. When update the department_id column of the employees table

1.3, trigger limit

When (new_value.department_id80)

Restrictions are not necessary. This example indicates that if the column department_id is not equal to 80, the trigger executes.

Where new_value represents the updated value.

1.4. Trigger operation

Is the body of the trigger

Begin

: new_value.commission_pct: = 0

End

The body is simple: set the updated commission_pct column to 0

Trigger:

Insert into employees (employee_id,last_name,first_name,hire_date,job_id,email

Department_id,salary,commission_pct)

Values (12345 sysdate, 12, 'donny@hotmail.com',60,10000,.25)

Select commission_pct from employees where employee_id=12345

The trigger does not notify the user and changes the user's input value.

2. The types of triggers are:

Trigger type:

1. Statement trigger

2. Row trigger

3. INSTEAD OF trigger

4. System conditional trigger

5. User event trigger

2.1, statement-level trigger. (statement-level triggers execute once for each DML statement) are triggers on a specific statement or group of statements executed on a table or in some cases on a view. Can be associated with INSERT, UPDATE, DELETE, or combination. However, regardless of the combination used, each statement trigger is activated only once for the specified statement. For example, no matter how many lines of update, the update statement trigger is called only once.

Example:

Create or replace trigger tri_test

After insert or update or delete on test

Begin

If updating then

Dbms_output.put_line ('modify')

Elsif deleting then

Dbms_output.put_line ('delete')

Elsif inserting then

Dbms_output.put_line ('insert')

End if

End

2.2, row-level trigger. (row-level triggers execute once for each row affected by the DML statement) example 1:

-- trigger

-- row-level trigger

Create table test (sid number,sname varchar2 (20));-- create a table

Create sequence seq_test;-- creation sequence

Create or replace trigger tri_test-- create trigger

Before insert or update of sid on test

For each row-- triggers each line

Begin

If inserting then

Select seq_test.nextval into:new.sid from dual

Else

Raise_application_error (- 20020 'update ID value is not allowed!');-- interrupt the program

End if

End

-- Test, insert a few records

Insert into test values (0djinff`)

Insert into test values (0djinff`)

Insert into test values (0dint')

The output is shown in the following figure:

Example 2:

Create a trigger that converts the value of the user-specified job column to uppercase, whether the user inserts a new record or modifies the job column of the job table.

Create or replace trigger trig_job

Before insert or update of job

On emp

For each row

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.