Oracle Study Notes 19

A trigger is a special type of subroutine stored in a database. It cannot be called directly by users, but is automatically triggered by the system when a specific event or operation occurs.

Call execution. The trigger cannot accept parameters. Therefore, the trigger is called trigger or ignition. Oracle events refer to the insert, update, and delete operations performed on database tables or similar operations performed on views.

Triggers are a technology provided by many relational database systems. In the Oracle system, triggers are declared, executed, and abnormal in similar processes and functions.

The PL/SQL block of the processing process.

Trigger composition:

Trigger event: under which circumstances the event is triggered, such as INSERT, UPDATE, and DELETE.

Trigger time: whether the trigger is triggered before or after the trigger event occurs, that is, the Operation Sequence of the trigger event and trigger.

Trigger itself: the purpose and intent of a trigger after it is triggered is exactly what the trigger itself is going to do.

Trigger frequency: the number of times the action defined in the trigger is executed. There are statement-level triggers and row-level triggers. Statement-level triggers are executed only once when an event is triggered. When a row-Level Trigger triggers an event, each row is executed once.

Basic Syntax:

Create or replace trigger trigger_name

Before | after

Insert | delete | update on table_name

For each row

When condition

Before and after indicate whether the trigger sequence is pre-triggered or post-triggered. The trigger is performed before the event is triggered, and the trigger is performed after the event is triggered. For each row indicates that the trigger is a row trigger. The when clause specifies the constraints that are triggered.

For example, compile a simple trigger and output helloworld when performing the update operation.

Create or replace trigger update_emp_trigger
After
Update on emp
For each row
Begin
Dbms_output.put_line ('Hello World ');
End;

Test:

SQL> update emp set sal = sal + 10;
 
Hello World
Hello World
Hello World
Hello World
Hello World
Hello World
Hello World
Hello World
Hello World
Hello World
Hello World
Hello World
Hello World
Hello World
 
14 rows updated

In the above test, no trigger is called, and the trigger is automatically executed by the system. It cannot be called directly for users. For each row if not written, it is a statement-Level Trigger by default.

: New and: old Modifiers

: The value of the column after the new modifier is accessed.

: The value at the top of the access completed by the old modifier.

For example, use the: new and: old operators to obtain the values before and after the update.

Create or replace trigger update_emp_trigger1
After
Update on emp
For each row
Begin
Dbms_output.put_line (: old. sal | ',' |: new. sal );
End;

Test:

SQL> update emp set sal = sal + 10;
 
902,912
1717.44, 1727.44
1346.13, 1356.13
3115.19, 3125.19
1346.13, 1356.13
2985.14, 2995.14
2568.98, 2578.98
3141.2, 3151.2
5120.5, 5130.5
1611.35, 1621.35
1186.99, 1196.99
2100.8, 2110.8
3141.2, 3151.2
1399.17, 1409.17
 
14 rows updated

Delete trigger

Basic syntax

Drop trigger trigger_name;

If an error occurs during trigger creation, run the show errors command to view the error message.

Package

Combine data and subroutines to form a package. Similar to classes in java, packages are used in PL/SQL Programming to Implement object-oriented technology.

The package consists of two parts: one is the description part and the other is the implementation part.

Definition package

Create or replace package body package_name

Is |

Begin

-- Pl/SQL statement;

End;

The call format for common elements in the package is package name. Class Name.

Delete package

Drop package package_name;