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