PL/SQL triggers

Trigger

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.1 Trigger Type

Triggers are stored as separate objects in the database, unlike stored procedures, where stored procedures start or run directly through other programs, and triggers are run by an event. That is, a trigger is automatically run implicitly when an event occurs. Also, the trigger cannot receive parameters. So running the trigger is called trigger or ignition (firing). An ORACLE event refers to an INSERT,UPDATE , and DELETE operation on a table in a database or a similar operation on a view. Oracle extends the capabilities of triggers to trigger Oracle, such as database startup and shutdown.

1.2 Alternative triggers

Because in Oracle, it is not possible to operate directly on a view that is established by more than two tables. Therefore, an alternative trigger is given.

1.3 System triggers

It can be triggered in the event of an Oracle database system, such as the startup and shutdown of an Oracle system.

Triggers consist of:

Trigger event: In which case the trigger is triggered; For example: INSERT, UPDATE, DELETE.

Trigger time: That is, whether the trigger is triggered before the triggering event (before) or after (after), that is, the sequence of actions that triggered the event and the trigger.

The trigger itself: the purpose and intent of the trigger after it is triggered is exactly what the trigger itself is going to do. For example, PL/SQL blocks.

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.

Statement-level (STATEMENT) Trigger: The trigger executes only once when a trigger event occurs;

Row-level triggers: A trigger is executed once for each row of data affected by the operation when a triggering event occurs.

2 Creating a Trigger

The general syntax for creating triggers is

CREATE [OR REPLACE] TRIGGERtrigger_name

{before| after}

{INSERT| DELETE| UPDATE [of column [, Column ...]}

on [Schema.] table_name

[For eachROW]

[when condition]

Trigger_body;

Before and after indicate that the trigger's trigger timing is pre-and post-trigger, respectively, before triggering the trigger, which triggers the currently created trigger before executing the triggering event, triggering the trigger that is currently created after the trigger event is executed.

The For each row option describes the trigger as a row trigger. The difference between a row trigger and a statement trigger is that a row trigger requires that when a DML statement is manipulated to affect multiple rows of data in a database, the trigger is activated for each row of data, as long as they conform to the trigger constraint, and the statement trigger takes the entire statement action as a trigger event, and when it meets the constraints, Activates the trigger once. When the For each ROW option is omitted, the before and after triggers are statement triggers, while the instead OF triggers are row triggers.

The When clause describes the triggering constraint condition. When Condition is a logical expression, it must contain a correlation name, not a query statement, or a PL/SQL function. The trigger constraint specified by the When clause can only be used in before and after row triggers, not in instead of row triggers and other types of triggers.

The stored procedure to execute when a base table is modified (INSERT, UPDATE, DELETE) is automatically triggered based on the base table changes it is attached to, so it is independent of the application, and database triggers are used to guarantee the consistency and integrity of the data.

Each table can have up to a maximum of four types of triggers, which are:

Before INSERT

Before INSERT for each ROW

After INSERT

After INSERT for each ROW

Before UPDATE

Before UPDATE for each ROW

After UPDATE

After UPDATE for each ROW

Before DELETE

Before DELETE for each ROW

AfterDelete

After DELETE for each ROW

Create or Replace Trigger Update_emp_trigger

After

Update on employees

For each row

Begin

Dbms_output.put_line (' Hello World ');

End

the update will output Hello world when each row is updated

3 using : old:new modifier

Create or Replace Trigger Update_emp_trigger

After

Update on employees

For each row

Begin

Dbms_output.put_line (' Old salary ' | | old.salary | | ', ' | | ' New Salary: ' | | New.salary);

End

Write a trigger to back up the corresponding record in the My_emp_bak table when the my_emp record is deleted

1). Preparatory work:

CREATE TABLE my_emp as select employee_id ID, last_name name, salary sal from employees

CREATE TABLE My_emp_bak as select employee_id ID, last_name name, salary sal from employees where 1 = 2

2).

Create or Replace Trigger Bak_emp_trigger

Before delete on my_emp

For each row

Begin

INSERT into My_emp_bak values (: old.id,: Old.name,: Old.sal);

End

Example:

a HelloWorld -level trigger

Create or Replace Trigger Hello_trigger

After

Update on employees

--for each row

Begin

Dbms_output.put_line (' Hello ... ');

--dbms_output.put_line (' old.salary: ' | |: old.salary| | ', New.salary ' | |:new.salary);

End

then execute:Update employees Set salary = salary + 1000;

HelloWorld: Write a trigger that , when inserting records into the emp table , print ' HelloWorld '

Create or Replace Trigger Emp_trigger

After

Insert on EMP

For each row

Begin

Dbms_output.put_line (' HelloWorld ');

End

row-level triggers : each update to a record in the employees table will cause the trigger to Execute

Create or Replace Trigger Employees_trigger

After

Update on employees

For each row

Begin

Dbms_output.put_line (' modified a record ! ');

End

statement-level triggers : a update/delete/insert statement causes the trigger to execute only once

Create or Replace Trigger Employees_trigger

After

Update on employees

Begin

Dbms_output.put_line (' modified a record ! ');

End

use : New,: old modifier

Create or Replace Trigger Employees_trigger

After

Update on employees

For each row

Begin

Dbms_output.put_line (' Old salary: ' | |: Old.salary | | ', New salary: ' | | : New.salary);

End

write a trigger , when deleting the my_emp record , in the My_emp_bak table backup corresponding records

1). preparatory work :

CREATE TABLE my_emp as select employee_id ID, last_name name, salary sal from employees

CREATE TABLE My_emp_bak as select employee_id ID, last_name name, salary sal from employees where 1 = 2

2).

Create or Replace Trigger Bak_emp_trigger

Before delete on my_emp

For each row

Begin

INSERT into My_emp_bak values (: old.id,: Old.name,: Old.sal);

End

PL/SQL triggers