OraclePL/SQL trigger learning notes

A trigger is also a named PLSQL block. Triggers are similar to processes and functions, because they are all named PLSQL blocks with declarations, executions, and exception handling processes. Similar to the package,

A trigger is also a named PL/SQL block. Triggers are similar to processes and functions, because they are all named PL/SQL blocks with declarations, executions, and exception handling processes. Similar to the package,

1. Concepts of triggers

A trigger is also a named PL/SQL block. Triggers are similar to processes and functions, because they are all named PL/SQL blocks with declarations, executions, and exception handling processes. Similar to the package, triggers must be stored in the database and cannot be declared locally by blocks.

When a trigger event occurs, the trigger is executed explicitly, and the trigger does not accept parameters.

The syntax for creating a trigger is as follows:

View plaincopy to clipboardprint?

CREATE [or replace] TRIGGER trigger_name

{BEFORE | AFTER | instead of} triggering_event

[Referencing_clause]

[WHEN trigger_condition]

[For each row]

Trigger_body;

CREATE [or replace] TRIGGER trigger_name

{BEFORE | AFTER | instead of} triggering_event

[Referencing_clause]

[WHEN trigger_condition]

[For each row]

Trigger_body;

The referencing_clause uses a different name to reference data in the currently updated record row. Trigger_condition in the when clause-If yes, the trigger's Subject code will be executed only WHEN the condition value is true.

2. DML trigger activation sequence

1) execute the before statement-Level Trigger-if such trigger exists

2) records of each row affected by the statement

Execute the before row-Level Trigger-if this trigger exists

Execute the statement itself

Execute the after row-level trigger -- if such trigger exists

3) execute the after statement-level trigger -- if such trigger exists

The ignition sequence of the same type of trigger is not defined. If this order is important, we recommend that you combine all these operations into one trigger.

3. Association identifier in a row-Level Trigger

The row-Level Trigger is activated once every row of data is processed by the trigger activation statement. You can access the data in the record row being processed within this row-Level Trigger. This is achieved through two associated identifiers --: old and: new. The Association identifier is also a special binding variable of PL/SQL. The colon Before the identifier indicates that both are bound variables, and that they are not common PL/SQL variables. PL/SQL Compiler will regard them as the following types of records:

Triggering_table % ROWTYPE

Triggering_table is the name of the table on which the trigger is defined. Therefore, the following reference

: New. field

It is valid only when the field in the trigger table is the field name.

Trigger statement

: Old

: New

INSERT

Undefined-all fields are NULL

Value to be inserted when the trigger statement is complete

UPDATE

Update the original value of the corresponding record row

Value to be updated when the trigger statement is complete

DELETE

Delete the original value of the corresponding record row

Undefined-all fields are NULL

Note: the "old" identifier is not defined in the INSERT statement, and the "new" identifier is not defined in the DELETE statement. If you use the: old identifier in the INSERT statement or the: new identifier in the DELETE statement, PL/SQL does not produce errors, but the values of both fields are NULL.

Pseudo record

Although the syntax structure regards: new and: old as records of the triggering_table % ROWTYPE type, they are not actually records. Therefore, operations that can be normally performed on records cannot be executed on: new and: old. For example, you cannot assign values as a whole. You can only assign values to each field.

View plaincopy to clipboardprint?

Create or replace trigger TempDelete

Before delete on temp_table

FOR EACH ROW

DECLARE

V_TempRec temp_table % ROWTYPE;

BEGIN

/* This is not a legal assignment, since: old is not truly

A record .*/

V_TempRec: =: old;

/* We can accomplish the same thing, however, by assigning

The fields inpidually .*/

V_TempRec.char_col: =: old. char_col;

V_TempRec.num_col: =: old. num_col;

END TempDelete;

/

Create or replace trigger TempDelete

Before delete on temp_table

FOR EACH ROW

DECLARE

V_TempRec temp_table % ROWTYPE;

BEGIN

/* This is not a legal assignment, since: old is not truly

A record .*/

V_TempRec: =: old;

/* We can accomplish the same thing, however, by assigning

The fields inpidually .*/

V_TempRec.char_col: =: old. char_col;

V_TempRec.num_col: =: old. num_col;

END TempDelete;

/

REFERENCING clause

You can also use the REFERENCING clause to change the names of old and new. This clause appears after the event is triggered and before the WHEN clause. The syntax is as follows:

REFERENCING [old as old_name] [new as new_name]

In the trigger body, you can use old_name and new_name instead of old and new.

Note that the associated identifiers in the REFERENCING clause do not contain colons.

As shown in the following example:

View plaincopy to clipboardprint?

Create or replace trigger GenerateAuthorID

Before insert or update on authors

REFERENCING new AS new_author

FOR EACH ROW

BEGIN

/* Fill in the ID field of authors with the next value from

Author_sequence. Since ID is a column in authors,: new. ID

Is a valid reference .*/

SELECT author_sequence.NEXTVAL

INTO: new_author.ID

FROM dual;

END GenerateAuthorID;

/

Create or replace trigger GenerateAuthorID

Before insert or update on authors

REFERENCING new AS new_author

FOR EACH ROW

BEGIN

/* Fill in the ID field of authors with the next value from

Author_sequence. Since ID is a column in authors,: new. ID

Is a valid reference .*/

SELECT author_sequence.NEXTVAL

INTO: new_author.ID

FROM dual;

END GenerateAuthorID;

/

4. WHEN clause

The WHEN clause can only be used in row-level triggers. If the WHEN clause is provided in the definition of a row-Level Trigger, the trigger body executes only the record rows that meet the conditions defined by the WHEN. The basic form of the WHEN clause is as follows:

WHEN trigger_condition

Trigger_condition is a Boolean expression. Each time a record is processed, the value of this expression is re-determined.

You can also use: new and: old records in trigger_condition, but like the REFERENCING clause, the colon is not required when: new and: old are used in trigger_condition. The colon is only required in the trigger body.

5. Trigger predicates

You can use three Boolean functions inside a trigger to determine the operations that trigger the trigger. The three predicates are INSERTING, UPDATING, and DELETING.

The usage is shown in the following example.

View plaincopy to clipboardprint?

Create or replace trigger LogInventoryChanges

Before insert or delete or update on inventory

FOR EACH ROW

DECLARE

V_ChangeType CHAR (1 );

BEGIN

/* Use 'I' for an INSERT, 'D 'for DELETE, and 'U' for UPDATE .*/

IF INSERTING THEN

V_ChangeType: = 'I ';

ELSIF UPDATING THEN

V_ChangeType: = 'U ';

ELSE

V_ChangeType: = 'D ';

End if;

/* Record all the changes made to inventory in

Inventory_audit. Use SYSDATE to generate the timestamp, and

USER to return the userid of the current user .*/

Insert into inventory_audit

(Change_type, changed_by, timestamp,

Old_isbn, old_status, old_status_date, old_amount,

New_isbn, new_status, new_status_date, new_amount)

VALUES

(V_ChangeType, USER, SYSDATE,

: Old. isbn,: old. status,: old. status_date,: old. amount,

: New. isbn,: new. status,: new. status_date,: new. amount );

END LogInventoryChanges;

/

Create or replace trigger LogInventoryChanges

Before insert or delete or update on inventory

FOR EACH ROW

DECLARE

V_ChangeType CHAR (1 );

BEGIN

/* Use 'I' for an INSERT, 'D 'for DELETE, and 'U' for UPDATE .*/

IF INSERTING THEN

V_ChangeType: = 'I ';

ELSIF UPDATING THEN

V_ChangeType: = 'U ';

ELSE

V_ChangeType: = 'D ';

End if;

/* Record all the changes made to inventory in

Inventory_audit. Use SYSDATE to generate the timestamp, and

USER to return the userid of the current user .*/

Insert into inventory_audit

(Change_type, changed_by, timestamp,

Old_isbn, old_status, old_status_date, old_amount,

New_isbn, new_status, new_status_date, new_amount)

VALUES

(V_ChangeType, USER, SYSDATE,

: Old. isbn,: old. status,: old. status_date,: old. amount,

: New. isbn,: new. status,: new. status_date,: new. amount );

END LogInventoryChanges;

/

6. INSTEAD-OF trigger

The INSTEAD-OF triggers can only be defined on The View (relational or object), and they can be ignited in place OF the DML statements for ignition. The INSTEAD-OF Trigger must be Row-level.

7. Trigger restrictions

The trigger is subject to a PL/SQL block. All statements that can be used in PL/SQL blocks are valid in the trigger body, but are subject to the following restrictions:

The trigger should not use the transaction control statement-COMMIT, ROLLBACK, or SAVEPOINT. The trigger is ignited as part of the execution of the trigger Statement, which is in the same transaction as the trigger statement. When a trigger statement is submitted or recalled, the trigger's work is also submitted.

No process or function called by the trigger subject can use transaction control statements.

The trigger body cannot declare any LONG or long raw variables. In addition,: new and: old cannot point to LONG and long raw columns in the table that defines the trigger.

The tables that the trigger subject can access are limited.

Trigger P-Code

When the package or subroutine is stored in the data dictionary, the source code of the object is stored and the compiled p-code is stored. But this is not the case for triggers. The data dictionary only stores the source code of the trigger, rather than the p-code. Result: each time you read the trigger from the data dictionary, you must compile the trigger. This will not affect the definition and usage of the trigger, but will affect the performance of the trigger.

8. system triggers

The DML trigger and INSTEAD-OF trigger we saw earlier are both based on DML events. On the other hand, the activation of system triggers is based on two different events: DDL events or database events. DDL events include CREATE, ALTER, or DROP statements. Database events include database server startup/shutdown events, user logon/disconnection events, and server errors. The syntax for creating a system trigger is as follows:

View plaincopy to clipboardprint?

CREATE [or replace] TRIGGER [schema.] trigger_name

{BEFORE | AFTER}

{Ddl_event_list | database_event_list}

ON {DATABASE | [schema.] SCHEMA}

[When_clause]

Trigger_body;

CREATE [or replace] TRIGGER [schema.] trigger_name

{BEFORE | AFTER}

{Ddl_event_list | database_event_list}

ON {DATABASE | [schema.] SCHEMA}

[When_clause]

Trigger_body;

Ddl_event_list is one OR more DDL events separated by the OR keyword, and database_event_list is one OR more database events separated by the OR keyword.

Note: you cannot create an INSTEAD-OF system-level trigger.

By using the clause ON {DATABASE | [schema.] SCHEMA}, we can specify whether the system trigger is defined at the DATABASE or Mode Level. If an activation event occurs, the database-Level Trigger is activated. Only when an activation event occurs in a specific mode, the corresponding mode-level trigger will be activated. If you do not define the name of a specific mode when using the SCHEMA keyword, the mode with this trigger is set by default.

9. Modify the trigger status and delete the trigger

Start or disable a trigger

Alter trigger trigger_name {DISABLE | ENABLE };

Delete trigger

Drop trigger trigger_name;

You can also use the alter table command and add the enable all triggers or disable all triggers clause to ENABLE or disable all triggers on a TABLE.

Alter table table_name {ENABLE | DISABLE} all triggers;

You can use user_triggers to view the trigger information.

10. Change table and limit table

The trigger body has some restrictions on tables and columns that can be accessed. Before defining these restrictions, Let's first look at two concepts: change tables and limit tables.

Mutating table is the table being modified by the DML statement. A trigger is a table that defines the trigger. Tables that need to be updated as the results of the delete cascade referential integrity constraints are also mutating ).

A constraining table is a table that may require read operations on reference integrity restrictions.

To better understand the definition, let's look at the example below

View plaincopy to clipboardprint?

Create table registered_students (

Student_id NUMBER (5) not null,

Department CHAR (3) not null,

Course NUMBER (3) not null,

Grade CHAR (1 ),

CONSTRAINT rs_grade

CHECK (grade IN ('A', 'B', 'C', 'D', 'E ')),

CONSTRAINT rs_student_id

Foreign key (student_id) REFERENCES students (id ),

CONSTRAINT rs_department_course

Foreign key (department, course)

REFERENCES classes (department, course)

);

--...

Create table registered_students (

Student_id NUMBER (5) not null,

Department CHAR (3) not null,

Course NUMBER (3) not null,

Grade CHAR (1 ),

CONSTRAINT rs_grade

CHECK (grade IN ('A', 'B', 'C', 'D', 'E ')),

CONSTRAINT rs_student_id

Foreign key (student_id) REFERENCES students (id ),

CONSTRAINT rs_department_course

Foreign key (department, course)

REFERENCES classes (department, course)

);

--...

Registered_students has two declared integrity constraints for reference.

Both Students and classes are restricted tables of registered_students.

SQL statements in the trigger body are not allowed:

Read or modify any change table of triggering statement. These tables include triggering tables themselves.

Read or modify the primary key, unique column value (unique), or foreign key column in the triggering table. If necessary, you can modify other columns.