How Oracle triggers use

Triggers are blocks of code that are executed automatically when a particular event occurs. Similar to stored procedures, but users cannot call them directly. Triggers are a technology provided by many relational database systems. In Oracle systems, triggers are similar to processes and functions, with pl/sql blocks for declaration, execution, and exception handling processes.

1. Notes

1 The trigger is a special kind of stored procedure, the trigger is usually triggered by the event and cannot accept the parameter, the memory is called by the statement block

2) Trigger classification:

1.DML triggers: Created on a table, raised by a DML event

2.instead of Trigger: Created on view and can only be triggered at the row level, replace Insert,delete, etc. (since Oracle cannot direct a DML operation on a view that has more than two tables, an alternate trigger is given. It is a processing method specifically for view operations.

3.DDL triggers: Database object creation and modification when an event is triggered

4. Database event triggers: defined on a database or schema, triggered by a database event

3) Composition:

1. Trigger event: The event DML statement that triggers the trigger (INSERT, UPDATE, DELETE statement performs data processing on the table or view), DDL statements (such as Create, alter, drop statements, creates, modifies, deletes schema objects in the database), Database system Events

(such as system startup or exit, abnormal error), user events (such as logging on or exiting the database).

2. Trigger time: Whether the trigger is triggered before (before) or after the triggering event (after)

3. Trigger action: What to do after trigger trigger

4. Trigger object: Include table, view, schema, database. Triggers are executed only if a trigger event occurs that matches the trigger condition on these objects.

5. Trigger Condition: Specify a logical expression by the When clause. The trigger action is automatically performed when the expression evaluates to true only if a trigger event is encountered.

6. Trigger frequency: Describes the number of times the action defined within the trigger is executed. The statement level (STATEMENT) trigger and the row-level (row) trigger. (such as delete multiple data, row-level triggers may execute multiple times, statement-level triggers will only fire once)

2. Grammar

1) Description

Different types of triggers, such as DML triggers, Instead of triggers, and system triggers differ in syntax formats

2) General grammar

CREATE [OR REPLACE] Tigger Trigger name trigger Time trigger Event

On table name/view name

[For each row]//plus for each row is a row-level trigger, no overtime for a statement-level trigger BEGIN Pl/sql statement

End

create [or Replace] trigger [schema.] Trigger_name

{before | after | instead of}

{Delete [or insert][or update [of Column,... n]]}

on [schema.] table_name | View_name

[For each row [when (condition)]]

sql_statement[,... N]

For example:

CREATE OR REPLACE TRIGGER trigger_name

< before | After | Instead of > < insert | Update | Delete> on table_name

[For each ROW] When (condition)

DECLARE BEGIN end;

3) Instead OF trigger syntax

Grammar:

CREATE [OR REPLACE] TRIGGER trigger_name

INSTEAD of{insert| delete| UPDATE [of COLUMN ...]} [OR {insert| delete| UPDATE [of COLUMN ...]}] On View_name[refferencing{old [as] old | NEW [as] new| Parent as Parent}]//You can specify the relevant name, the current default correlation name is old and new,

The associated name needs to be added: [For each ROW]//instead of triggers can only be triggered at the row level, because it is not necessary to specify [when Condition]declare

Beginend;

Description: INSTEAD of a DML trigger for the view, which may be joined by multiple tables, so that not all joins are updatable and the INSTEAD of triggers are used to perform the appropriate action.

3. Examples

To create a test table:

CREATE TABLE "Hnzc". Triggertest "

(

"ID" VARCHAR2 (BYTE),

"NAME" VARCHAR2 (BYTE),

"SCORE" number

); CREATE table TAB1 select * from Triggertest;

1 DML triggers/row-level triggers

Triggers are as follows:

CREATE OR REPLACE TRIGGER TRIGGER1

After insert on Triggertest//insert to trigger for each row//row-level trigger begin

INSERT into Tab1 (id,name) VALUES (' 22 ', ' 33 '); End;

Execute statement:

INSERT into triggertest (ID) VALUES (' AABBCC ');

End of statement execution, add a new piece of data to the table TAB1

2 limit modifications to the table (for example, non-working hours cannot modify certain tables)

Triggers are as follows:

CREATE OR REPLACE TRIGGER TRIGGER1

After INSERT on Triggertest

For each ROW

BEGIN

IF (To_char (sysdate, ' Day ') in (' Wednesday ', ' Sunday ')) THEN raise_application_error (-20001, ' not working time, cannot modify form triggertest '); End IF; End;

Execute statement:

INSERT into triggertest (ID) VALUES (' AABBCC ');

Today Wednesday thus output results for:

Error starting executing command on row 1: INSERT into triggertest (ID) VALUES (' AABBCC ')

Error Reporting:

SQL Error: ORA-20001: Not working time, can not modify the table Triggertest

ORA-06512: In "HNZC." TRIGGER1 ", line 3ora-04088: Trigger ' HNZC. TRIGGER1 ' Error during execution

Changes to the table are usually limited as follows (i.e., from Monday to Friday 9--18 can modify the form).

CREATE OR REPLACE TRIGGER TRIGGER1

Before INSERT or DELETE or UPDATE on triggertest

For each ROW

BEGIN

IF (To_char (sysdate, ' Day ') in (' Saturday ', ' Sunday ')) OR (To_char (sysdate, ' Hh24:mi ') is not BETWEEN ' 9:00 ' and ' 18:00 ') THEN Application_Error (-20001, ' not working time, can not modify the form triggertest '); End IF; End;

3 Increase the restriction condition (if the record of an employee cannot be changed)

Triggers are as follows: (the following implementation of the monthly score can only increase)

CREATE OR REPLACE TRIGGER TRIGGER1

Before INSERT or DELETE or UPDATE on triggertest

For each ROW

When (old.name= ' month son ') BEGIN

case when updating (' SCORE ') THEN

If:new. Score<:old. SCORE THEN raise_application_error (-20001, ' monthly score can only increase can not be reduced '); End IF; End case; End;

The current monthly score is 20.

Error when modifying to 10 o'clock

UPDATE "HNZC". Triggertest "SET SCORE = ' WHERE ROWID = ' Aaadezaapaaaah+aab ' and ORA_ROWSCN = ' 47685303 ' ORA-20001: The monthly score can only ascend and not fall

ORA-06512: In "HNZC." TRIGGER1 ", line 4ora-04088: Trigger ' HNZC. TRIGGER1 ' Error during execution

When modified to 30 o'clock successful

UPDATE "HNZC". Triggertest "SET SCORE = ' WHERE ROWID = ' Aaadezaapaaaah+aab ' and ORA_ROWSCN = ' 47685303 ' submitted successfully

4 Invoking the stored procedure in the trigger

Triggers are:

CREATE OR REPLACE TRIGGER TRIGGER1

Before INSERT or DELETE or UPDATE on triggertest

For each ROW

BEGIN

TESTPRO1 (); End;

Stored procedures are:

Create or Replaceprocedure TESTPRO1 as BEGIN

Insert into TAB1 (id,name,score) VALUES (' AAA ', ' BBB ', 200); End TESTPRO1;

Add a piece of data to tab1 after execution

5) Cascade Update

Triggers are as follows (modify name in TAB1 while name is modified in Triggertest table)

Create or Replaceprocedure TESTPRO1 as BEGIN

Insert into TAB1 (id,name,score) VALUES (' AAA ', ' BBB ', 200); End TESTPRO1;

Execute statement:

Update triggertest set name= ' water son ' where name= ' month son ';

Result: In Tab1, the name of the moon was changed to water.

6) instead OF triggers

Table Student Tabular data is as follows

Create a View Student_view

CREATE OR REPLACE VIEW Studnet_view

As SELECT classid,avg (SCORE) Average_score from Studentgroup by CLASSID;

The view data is as follows:

View Student_view do the following:

DELETE from Studnet_view WHERE classid= ' 111 ';

Execution results:

Error Reporting:

SQL Error: ORA-01732: The data manipulation operation of this view is illegal 01732. 00000-"Data manipulation operation not legal in this view"

Workaround: Create instead of view

CREATE OR REPLACE TRIGGER student_view_delete

INSTEAD of DELETE on Studnet_view

For each Rowbegin

DELETE from STUDENT WHERE classid=:old. CLASSID; End Student_view_delete;

Execute DELETE statement

DELETE from Studnet_view WHERE classid= ' 111 ';

Execution Result: Delete succeeded

1 lines have been deleted.

4. Matters needing attention

1 only DML statements (SELECT, INSERT, UPDATE, DELETE) can be used in the execution part of the trigger, and DDL statements (CREATE, ALTER, DROP) cannot be used

2 A COMMIT statement cannot be used in a trigger, and the action of the trigger is performed with a trigger event (Insert,update,delete) to commit and rollback;

3 The more triggers on a table, the greater the impact on the performance of a table's DML operations

4 The maximum trigger is 32K