Oracle Learning Note 19-flip-flop and Packet analysis (PL/SQL)

A trigger is a special type of subroutine that resides in a database. cannot be called directly by the user, but is initiated by the system when a particular event or action occurs

Tune with the holder line. The trigger cannot accept the number of parameters. So executing a trigger is called triggering or ignition. An Oracle event refers to an INSERT, UPDATE,delete operation, or similar operation on a view of a database table.

Triggers are a technique that is provided by many relational database systems. In Oracle systems, triggers are similar to procedures and functions, with declarations, runs, and exceptions

have processed the PL/SQL block of the process.

The composition of the trigger:

Trigger event: In which case triggers: INSERT, UPDATE, DELETE.

Trigger time: Whether it is triggered before or after the triggering event, that is, the sequence of actions that trigger the event and trigger.

The trigger itself: the purpose and intent of trigger after being triggered is exactly what the trigger itself will do.

Trigger frequency: The number of times the action defined within the trigger is run. There are statement-level triggers and row-level triggers. Statement-level triggers run only once when a triggering event occurs. Row-level triggers each row is run once when the triggering event occurs.

The 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 triggered timing is a pre-trigger or a post-trigger. A pre-trigger is run before the event is triggered, and the trigger is run after the triggering event. The For each row indicates that the trigger is a row trigger. The When clause describes the constraint that is triggered.

Example: Write a simple trigger that outputs HelloWorld when the update operation is run.

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

Rows updated

In the above test, there is no trigger, the trigger is the system to run its own initiative. and cannot be called directly to the user. The For each row assumes that a statement-level trigger is not written.

: New and: Old modifiers

: The new modifier visits the value of the column after the operation has completed.

: Old modifier to access the value at the top of the operation.

Example: Use the: New and: Old operators to get the value before the update, and the value after it, respectively.

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

Rows updated

Delete Trigger

The basic syntax

DROP TRIGGER trigger_name;

when creating a trigger, assume that an error occurs by using the Show Errors command to view the error message.

Package

The combination of data and subroutines constitutes a package. Similar to the classes in Java, it is wrapped in PL/SQL programming to implement object-oriented techniques.

The composition of the package is divided into two parts, one is the description part, the other is the realization part.

Defining packages

Create or replace package body Package_name

is | As

Begin

--pl/sql statement;

End;

The invocation format for common-owning elements within a package is: Package name. class name.

Delete Package

DROP package package_name;