Oracle triggers trigger detailed _oracle

Trigger-related concepts and syntax

Overview

This blog post focuses on the following topics:

What is a trigger
Application Scenarios for triggers
Syntax for triggers
Types of Triggers
Case

Data:

The concept of triggers and the first trigger

A database trigger is a stored Pl/sql statement that is associated with a table.

When a specific data action statement (insert Update delete) is emitted on a specified table, Oracle automatically executes the sequence of statements defined in the trigger.

For a simple example:

When a record is added to the employee table, auto print "Insert new employee successfully"

Create or replace trigger Insertstaffhint after insert in xgj_test for each 
 row
Declare-
 -local variable s here
begin

 Dbms_output.put_line (' New employee success ');

End Insertstaffhint;

Application Scenarios for triggers

Complex security Checks
Confirmation of data
Database Audit
Backup and audit of data

Syntax for triggers

CREATE [OR REPLACE] TRIGGER trigger_name
{before | After}
{INSERT | DELETE | UPDATE [of column [, Column ...]]}
[OR {INSERT | DELETE | UPDATE [of column [, Column ...]]} ...]
on [schema.] table_name | [Schema.] View_name
[referencing {old [as] old | NEW [as] new| Parent as parent} [for each
ROW]
[when condition]
Pl/sql_block | Call procedure_name;

which

before and after the triggers are triggered by triggering the trigger that is currently created before the triggering event, triggering the trigger that is currently being created after the triggering event.

The For each row option describes the trigger as a row trigger.

The difference between a row trigger and a statement trigger is shown in: Row triggers require that when a DML statement moves multiple rows of data in a database, the trigger is activated once for each row of data, as long as they conform to the trigger constraint, and the statement trigger takes the entire statement action as the triggering event, and when it meets the constraint, Activates a trigger once.
When the For each row option is omitted, the before and after triggers are statement triggers, and the instead of triggers can only be row triggers
。

The referencing clause describes the correlation name, which can be used in the Pl/sql block and the When clause of the row trigger to reference the current new and old column values, with the default related names old and new. When you apply a correlation name to a pl/sql block of a trigger, you must precede them with a colon (:), but you cannot add a colon in the When clause.

The When clause describes the trigger constraint. When Condition is a logical expression, it must contain the relevant name, not the query statement, or call the Pl/sql function. The trigger constraint specified by the When clause can only be used in before and after-row triggers and cannot be used 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 according to the base table changes it relies on, so it is not relevant to the application, and database triggers are used to guarantee the consistency and integrity of the data.

You can create up to 12 types of triggers per table:

Before insert
before insert for each row after insert to each
row


before UPDATE
before U Pdate for each row, after update, for each
row


before delete
before delete for each ROW
   after Delete after delete for each
ROW

Types of Triggers

Row triggers require that when a DML statement operation affects multiple rows of data in a database, the trigger is activated once for each row of data, as long as they conform to a trigger constraint; In a row-level trigger, use: Old and: New pseudo-record variables, identify the status of the value

The statement trigger takes the entire statement operation as the triggering event and activates the trigger once it meets the constraint.

When the For each ROW option is omitted, the before and after triggers are statement triggers.
The instead OF triggers can only be row triggers.

Other rules

The trigger name is not the same as the name of the procedure and the package, and it is a separate namespace, so the trigger name can have the same name as the table or procedure, but the trigger name cannot be the same in a pattern.

Limitations of DML triggers:

The character length of the CREATE trigger statement text cannot exceed 32KB;
The SELECT statement within the trigger body can only be select ... Into ... Structure, or the SELECT statement used to define the cursor.
Database transaction CONTROL Statement COMMIT cannot be used in triggers; ROLLBACK, Svaepoint statement;
A procedure or function invoked by a trigger cannot use a database transaction control statement;
Long, long RAW type cannot be used in triggers;
The value of the LOB type column can be referenced within the trigger, but it cannot be modified by: NEW to modify the data in the LOB column;

The basic points of DML triggers:

Trigger Timing: Specifies the trigger time of the trigger. If specified as before, the trigger is triggered before the DML operation is performed to prevent certain error actions from occurring or to implement certain business rules, or, if specified, to trigger after the DML operation is performed in order to record the operation or do some processing afterwards.
Trigger Event: The event that caused the trigger to be triggered, that is, the DML operation (INSERT, UPDATE, DELETE). Can be either a single trigger event or a combination of multiple triggering events (you can only use the or logical combination and not use the and logical combination).
conditional predicate: When a combination of multiple triggering events (INSERT, UPDATE, DELETE) is included in a trigger, the following conditional predicate provided by Oracle is required in order to perform different processing for different events separately.
1). Inserting: True if the trigger event is insert, false otherwise.
2). updating [(Column_1,column_2,..., column_x)]: When the trigger event is update, if the column_x column is modified, the value is true or false. Where the column_x is optional.
3). Deleting: True if the trigger event is delete, or false.
Extract object: specifies which table and view the trigger is created on.
Trigger Type: statement level or row-level trigger
Trigger Condition: Specifies a logical expression by the When clause, allowing only the trigger condition to be specified on the row-level trigger, specifying the list of columns after updating.

Trigger Application

Triggers apply a complex security check

Prohibit inserting data during a non-working time

/**
Non-working time (Planet six Sunday, not 9 points of the interval of ~18 points)

to prohibit the writing of data first to understand: the type of trigger--statement-level triggers. No
matter how many pieces of data are inserted, there is no need to verify each row of data, as long as it is not within that time period, it is not allowed to insert.
*/
Create or replace trigger Addstafffcheck
 before insert on Xgj_test 

declare
 --local variables Here

 to begin if To_char (sysdate, ' Day ') in (' Saturday ', ' Sunday ') or
 to_number (To_char (sysdate, ' hh24 ')) Between 9 and then-
 -Prohibit insert 
 raise_application_error (-20001, ' non-working time prohibit inserting data ');
 End If;
End Addstafffcheck;

raise_application_error is used to customize incorrect messages in the Plsql use program.
This exception has to be used in the database-side subroutines (processes, functions, packages, triggers), but not in anonymous blocks and client subroutines.
Syntax for Raise_application_error (Error_number,message[,[truefalse]); Where Error_number is used to define the incorrect number,
The incorrect number must be a negative integer between 20000 and 20999; The message is used to specify an incorrect message, and it cannot exceed 2048 bytes in length;

Trigger application two data validation

The wages cannot rise and the less

Analysis: First confirm the type of trigger, because each piece of data needs to be confirmed, so it is a row-level trigger. Need for each row

/** salary
can not be lower than the salary before the rise

1:old and: New represents the same record
2:old represents the value of this line before the row is manipulated
 : new represents the value of this line after the row is operated

on. or replace trigger checksalary
 before update on xgj_test for each 
 row
Declare-
 -local variables Here there is no variable declaration, declare can omit the
begin

 ---If the salary < The salary before the rise then how to say?
 If:new.sal <: Old.sal then

 raise_application_error (-20002, ' rising salary: ' | |: New.sal | | ' Less than the salary before the rise: ' | |:o Ld.sal);
 End If;
End Checksalary;

Three-database audit of trigger application

Creating a value-based trigger

CREATE TABLE Xgj_record (Info VARCHAR2 (256));
Create or replace trigger AddRecord after update in xgj_test for each 
 row
Declare-
 -local Variables here
begin

 If:new.sal > 6000 then 
 inserts into Xgj_record values (: New.sal | | -'|| : New.username | | ' -'|| : new.job);
 End If;

End AddRecord;

triggers to apply backup and synchronization of four data

Automatically back up to backup table when employees are paid

CREATE TABLE Xgj_test_bak as SELECT * from Xgj_test;

Create or replace trigger Databack after update in xgj_test for each
 row 

begin

 Update Xgj_test_bak Set sal =: new.sal where username =: new.username;

End Databack;

Oracle also uses snapshot backups, which are asynchronous. And the use of triggers, is synchronized.

Other

Create a trigger to write the deleted record to the Employee table delete log table when the Employee table EMP table is deleted a record

CREATE TABLE Emp_his as SELECT * from EMP WHERE 1=2;
Create OR REPLACE TRIGGER tr_del_emp
 before Delete--Specifies that the triggering time triggers on scott.emp for each row before the deletion-
 ---that the row-level trigger is created C5/>begin
 -Inserts the modified data into the logging table del_emp for oversight use.
 INSERT into Emp_his (Deptno, empno, ename, Job, Mgr, Sal, Comm, HireDate)
 VALUES (: Old.deptno,: Old.empno,: Old.ename,: Old.job,:old.mgr,: old.sal,: Old.comm,: old.hiredate);
End;
DELETE emp WHERE empno=7788;
DROP TABLE emp_his;
DROP TRIGGER del_emp;

Limit the time range for modifying the departments table (including Insert,delete,update), that is, the departments table is not allowed to be modified during a non-working time.

CREATE or REPLACE TRIGGER tr_dept_time
before INSERT or DELETE or UPDATE on
departments
BEGIN
 IF (to_ CHAR (sysdate, ' Day ') "in (' Saturday ', ' Sunday ')") OR (To_char (sysdate, ' Hh24:mi ') not BETWEEN ' 08:30 ' and ' 18:00 ') THEN raise_ap
 Plication_error (-20001, ' not working time, can not modify departments table ');
 End IF;
End;

Qualify a row trigger action only for records with department number 80.

CREATE OR REPLACE TRIGGER tr_emp_sal_comm
before UPDATE of salary, commission_pct
 or DELETE on
hr.employees< C4/>for each ROW when
(old.department_id = *)
BEGIN case when
 updating (' salary ') THEN
 IF: New.salary <: old.salary THEN

  raise_application_error (-20001, ' 80 of the staff's wages can not be lowered ');
 End IF;
 When updating (' commission_pct ') THEN

 IF:NEW.commission_pct <: old.commission_pct THEN
  Raise_ Application_Error (-20002, ' 80 of the personnel of the department's bonuses can not be lowered ');
 End IF;
 When deleting THEN
  raise_application_error (-20003, ' cannot delete department 80 's personnel Record ');
 End case;
End;
/* Instance:
UPDATE Employees SET salary = 8000 WHERE employee_id = 177;
DELETE from Employees WHERE employee_id in (177,170);
*/

Cascading updates are implemented with row triggers. After you have modified the region_id in the primary table regions (after), cascade, automatically update the region_id of the countries table in the country that was originally in the region.

CREATE OR REPLACE TRIGGER tr_reg_cou after update of region_id in regions for each
ROW
BEGIN
 dbms_ OUTPUT. Put_Line (' old region_id value is ' | |:o ld.region_id
   | | ', the new region_id value is ' | |:new.region_id ';
 UPDATE countries SET region_id =: new.region_id
 WHERE region_id =: old.region_id;
End;

Invokes a procedure in a trigger.

CREATE OR REPLACE PROCEDURE add_job_history
 (p_emp_id  job_history.employee_id%type
 , P_start_date job_ History.start_date%type
 , P_end_date job_history.end_date%type
 , p_job_id  job_history.job_id%type
 , p_department_id job_history.department_id%type
 )
is
BEGIN
 inserts into Job_history (employee_id, start_date, end_date,
    job_id, department_id)
 VALUES (p_emp_id, P_start_date, P_end_date, p_job_id, p_department_id);
End Add_job_history;

--Create trigger call stored procedure
... CREATE OR REPLACE TRIGGER update_job_history after
 update of job_id, department_id on employees for each
 row
   begin
 add_job_history (: old.employee_id,: Old.hire_date, Sysdate, old.job_id
   ,: old.department_id);
End;

The above is the entire content of this article, I hope to help you learn, but also hope that we support the cloud habitat community.