How Oracle triggers use 2

A trigger is a block of code that executes automatically when a particular event occurs. Similar to stored procedures, but users cannot call them directly. Triggers are a technique that is provided by many relational database systems. In Oracle systems, trigger-like procedures and functions have a PL/SQL block that declares, executes, and processes exceptions.

1. Description

1) trigger is a special kind of stored procedure, trigger is usually triggered by the event and cannot accept parameters, memory is called by Statement block

2) Trigger classification:

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

2.instead of triggers: Created on the view and can only be triggered at the row level, in place of actions such as Insert,delete (because Oracle does not directly perform DML operations on views that have more than two tables, the alternative triggers are given. It is a processing method specifically for the operation of the view)

3.DDL Triggers: Creation and modification of database objects when triggering events

4. Database Event trigger: defined in database or schema, triggered by database events

3) Composition:

1. Trigger event: An event DML statement (INSERT, UPDATE, DELETE statement that performs data processing on a table or view) that raises a trigger, DDL statements (such as Create, alter, DROP statements created in the database, modify, delete schema objects), Database system Events

(such as system startup or exit, exception errors), user events (such as logging in or exiting the database).

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

3. Trigger action: What to do after trigger triggers

4. Trigger object: Includes table, view, schema, database. The trigger does not trigger until a trigger event that matches the trigger condition occurs on these objects.

5. Trigger condition: A logical expression is specified by the When clause. Triggering events are triggered automatically only when the value of the expression is true.

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

2. Syntax

1) Description

Different types of triggers such as DML triggers, Instead of triggers, system trigger syntax formats differ greatly

2) General syntax

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

On table name/view name

[For each row]//plus the For each row is a row-level trigger, no overtime is 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 > < inserts | 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}]//Can specify a correlation name, the current default correlation name is old and new,

When applying the relevant name, add: [For each row]//instead of triggers can only be triggered at the row level because there is no need to specify [when Condition]declare

Beginend;

Description: INSTEAD of DML is used to trigger a view, because views may have multiple tables joined together, so not all junctions can be updated, using the INSTEAD of trigger can do the corresponding operation.

3. Example

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 trigger/row level trigger

The triggers are as follows:

CREATE OR REPLACE TRIGGER TRIGGER1

After insert on triggertest//insert triggers for each row/row level trigger begin

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

Execute statement:

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

Statement execution ends with a new data addition in table TAB1

2) Restrict changes to the table (for example, some tables cannot be modified during non-working hours)

The 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 hours, cannot modify form triggertest '); END IF; END;

Execute statement:

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

Today Wednesday thus output results for:

Error starting command execution on line 1: INSERT into triggertest (ID) VALUES (' AABBCC ')

Error Reporting:

SQL Error: ORA-20001: Not working hours, cannot modify table Triggertest

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

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

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:00am ' and ' 18:00 ') then Raise_ Application_Error (-20001, ' not working hours, cannot modify form triggertest '); END IF; END;

3) Add restrictions (such as the inability to change records for an employee)

　　The trigger is as follows: (the following can only increase the number of months)

CREATE OR REPLACE TRIGGER TRIGGER1

Before INSERT or DELETE or UPDATE on triggertest

For each ROW

When (old.name= ' month ') BEGIN

Case if UPDATING (' score ') then

If:new. Score<:old. Score then Raise_application_error (-20001, ' The score of the month can only ascend cannot be lowered '); END IF; END case; END;

The current month's 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 fraction of a month can only ascend and cannot be lowered

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

When modified to 30 o'clock success

UPDATE "HNZC". " Triggertest "SET score = ' WHERE ROWID = ' Aaadezaapaaaah+aab ' and ORA_ROWSCN = ' 47685303 ' Commit succeeded

4) Call the stored procedure in the trigger

　　The triggers are:

CREATE OR REPLACE TRIGGER TRIGGER1

Before INSERT or DELETE or UPDATE on triggertest

For each ROW

BEGIN

TESTPRO1 (); END;

　　The stored procedure is:

Create or Replaceprocedure TESTPRO1 as BEGIN

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

Add a piece of data to the TAB1 after execution is complete

5) Cascade Update

The triggers are as follows (the name in the Triggertest table is modified while the name is modified in the TAB1)

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 ' where name= ' month son ';

Results: In Tab1, the name of the month is also changed to water.

6) Instead of trigger

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 Result:

Error Reporting:

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

Workaround: Create a 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 rows have been deleted.

4. Precautions

1) only DML statements (SELECT, INSERT, UPDATE, DELETE) can be used in the execution portion 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 commit and rollback with the triggering event (Insert,update,delete);

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

4) maximum trigger is 32K

How Oracle triggers use 2