Oracle Trigger Learning

Triggers using tutorials and naming conventions

Directory
Triggers using tutorials and naming conventions 1
1, Trigger Introduction 1
2, trigger Example 2
3, trigger syntax and features 3
4, example one: row level trigger 14
5, example two: row level trigger 24
6, example three: INSTEAD of Trigger 6
7, example four: statement-level Trigger 18
8, example five: statement-level Trigger 29
9, example six: Package package trigger Code 10
10, trigger naming specification 11

1, Introduction to triggers
Trigger (Trigger) is a kind of database object, encoded like a stored procedure, associated with a table (table), when there are DML statements to operate the table, can cause the execution of the trigger, to achieve the consistency of the insert record, The purpose of correctness and normative control. When the C/S era prevailed, because the client directly connected to the database, can ensure that database consistency only the database itself, at this time the primary key (Primary key), foreign key (Foreign key), Constraints (Constraint) and triggers become the necessary control mechanism. And the implementation of the trigger is more flexible, programmable, and naturally become the most popular control mechanism. To the B/s era, developed into a 4-tier architecture, clients can no longer directly access the database, only the middleware can access the database. To control the consistency of the database, it can be controlled either in the middleware or at the database side. A lot of Java developers, with the database as a black box, the majority of data control work in the servlet execution. Doing so does not require much knowledge of the database and reduces the complexity of database programming, but it also increases the workload of servlet programming. From the view of architecture design, the function of middleware is to check the correctness of business and execute the business logic, if the consistency check of data is put into the middleware, it needs to check the data consistency in all the areas involving data writing. Since database access is a remote invocation relative to the middleware, it is not easy to write uniform data consistency check codes, which are generally used to add similar inspection steps in multiple places. Once the consistency check process has been adjusted, it will inevitably result in multiple local modifications, not only increasing the workload, but also not guaranteeing the correctness of each inspection step. The application of triggers should be placed on the key, multi-initiator, high-frequency access data tables, excessive use of triggers, will increase the database burden, reduce database performance. Discarding the use of triggers can lead to problems in the design of the system architecture and affect the stability of the system.

2, the trigger example
trigger code resembles a stored procedure, written in PL/SQL scripting. The following is an example of a trigger:
New Employee Payroll SALARY
CREATE TABLE SALARY
(
  employee_id number, employee ID
   MONTH       VARCHAR2 (6),--wage month
  AMOUNT       number--Salary amount
)

Create a trigger associated with salary Salary_trg_rai
1   create or replace trigger Salary_trg_rai
2    after Insert on Salary
3   for each row
4 declare
5   begin
6      dbms_output.put_line (' Employee ID: ' | |: new.employee_id);
7     dbms_output.put_line (' Wage month: ' | |: New.month);
8     dbms_output.put_line (' Wages: ' | |: New.amount);
9     dbms_output.put_line (' Trigger has been executed ');
10   end;
Open a SQL window windows (using the PL/SQL Developer tool), or enter in Sqlplus:
Insert into salary (employee_id, month, amount) VALUES (1, ' 200606 ', 10000);
can be seen in Sqlplus, or in the output of the SQL Window window
Employee id:1
Payroll Month: 200606
Payroll: 10000
Trigger executed

In the first line of the code, the type of the database object is defined as trigger, and the name of the definition trigger is Salary_trg_rai
The second line shows that this is an after trigger that executes after the DML operation is implemented. The following insert shows that this is a trigger for an insert operation, and each insert operation on the table executes the trigger.
The third line shows that this is a row-level trigger, and when the inserted record has n, the trigger is executed at every insert operation, with a total of n times.
Declare is followed by the local variable definition section, which can be empty if there is no local variable definition
The code in Begin and end, which is the execution part of the trigger, typically checks the insert record for consistency, and in this case prints the inserted record and "trigger executed."
Where: The new object represents the inserted record, and you can refer to each field value of the record by: New.column_name

3, trigger syntax and features
The syntax for a trigger is as follows
CREATE OR REPLACE TRIGGER trigger_name
<before | After | instead of> <insert | Update | Delete> on table_name
[For each ROW]
When (condition)
DECLARE
BEGIN
--Trigger code
END;

The

Trigger_name is the name of the trigger. <before | After | Instead of> can choose before or after or instead of. Before means that the trigger is executed before the DML statement is implemented, and after means that the trigger is executed after the DML statement is implemented, and the instead of trigger is used on the update of the view. <insert | Update | Delete> can select one or more DML statements, and if multiple are selected, separate with or, such as insert or update. TABLE_NAME is the name of the table associated with the trigger.
[For each row] is optional, and if a for every row is noted, the trigger is a row-level trigger, and the DML statement handles each record executing the trigger; otherwise, it is a statement-level trigger that fires once per DML statement. The condition followed by the
when is the response condition of the trigger, only valid for row-level triggers, and is not executed if the record of the operation satisfies condition. The condition can be referenced by the new object and the old object (note that it differs from the previous: New and: Old, in code that references a colon) to refer to the record of the operation. The
trigger code can include three types: the database transaction code is not involved, the database transaction code is involved in the associated table (table_name in the syntax above), and the database transaction code is involved in addition to the associated table. The first type of code only makes simple calculations and judgments about the data, there are no DML statements, and this type of code can be executed in all triggers. The second type of code involves data manipulation of the associated table, such as querying the total number of records in the associated table or inserting a record into the associated table, which can only be used in statement-level triggers, and will report ORA-04091 errors if used in a row-level trigger. The third type of code involves database transactions in addition to the associated table, and this code can be used in all triggers.

From the function of the trigger, it can be divided into 3 categories:
? Rewrite columns (before triggers only)
? Take action (any trigger)
? Deny transaction (any trigger)
The override column is used to verify the table field, and when the insertion value is empty or the insertion value does not meet the requirements, the trigger is replaced with a default value or a different value, in most cases the same as the default property of the field. This functionality can only be performed in a row-level before trigger. "Take action" for the characteristics of the current transaction, the related table operation, such as according to the current table inserted records update other tables, the general ledger in the bank and the total score relationship between the account can be maintained through this trigger function. The "Deny transaction" is used to verify the legality of the data, and when the updated data does not satisfy the consistency requirements of the table or system, the transaction is rejected by throwing an exception, and the code on its upper layer can catch the exception and operate accordingly.

The following is an example of how the trigger body syntax is described in the examples, in which the reader can understand the function of the trigger.

4, example one: one of the row level triggers
CREATE OR REPLACE TRIGGER salary_raiu
After INSERT OR UPDATE of amount on salary
For each ROW
BEGIN
IF inserting Then
Dbms_output.put_line (' Insert ');
elsif updating Then
Dbms_output.put_line (' Update Amount column ');
END IF;
END;
The above is a row-level trigger for after insert and after update. In the second row of amount on salary means that the update trigger will be valid only if the amount column is updated. Therefore, the following statement will not execute the trigger:
Update Salary Set month = ' 200601 ' Where month = ' 200606 ';
In an If statement expression in the body of the trigger, inserting, updating, and deleting can be used to distinguish which DML operation is currently being done, and can act as a property that combines multiple similar triggers in a trigger to discriminate the triggering event.

5, example two: row-level trigger two
New Employee Table Employment
CREATE TABLE EMPLOYMENT
(
employee_id number,--employee ID
Maxsalary number--salary cap
)
inserting two records
Insert into employment values (1, 1000);
Insert into employment values (2, 2000);

CREATE OR REPLACE TRIGGER salary_raiu
After INSERT OR UPDATE of amount on salary
For each ROW
When (New.amount >= + (Old.amount is NULL OR old.amount <= 500))
DECLARE
V_maxsalary number;
BEGIN
SELECT maxsalary
Into V_maxsalary
From employment
WHERE employee_id =: new.employee_id;
IF:NEW.amount > V_maxsalary Then
Raise_application_error (-20000, ' wage overrun ');
END IF;
END;

The above example introduces a new table employment, the Maxsalary field in the table represents the maximum salary that the employee can allocate per month. The following trigger detects the employee's maximum monthly wage in the employment table based on the employee_id of the inserted or modified record, and if the inserted or modified amount exceeds this value, the error is reported.
The When clause in the code indicates that the trigger is only for modified or inserted amount values that exceed 1000, and the amount value before the modification is less than 500 of the record. The new object and the old object represent the Record object before and after the action, respectively. For insert operations, all properties in the old object are null because the current operation records no historical objects, and for delete operations, all properties in the new object are null because the current action record does not update the object. In both cases, however, the references to the old and new objects are not affected and used in the body of the trigger, and the normal null values are treated the same way.
In the trigger body, first through: new.employee_id, get the employee's salary limit, and then in the IF statement to determine whether the updated employee salary is overrun, if the overrun error code is-20000, the error message is "wage overrun" custom error. The raise_application_error contains two parameters, the previous one is a custom error code, and the latter is a custom error code message. Where the custom error code must be less than or equal to-20000. After executing the statement, an exception is thrown, and if there is a exception clause at the previous level, the exception is caught. As in the following code:
DECLARE
code number;
Msg VARCHAR2 (500);
BEGIN
INSERT into salary (employee_id, amount) VALUES (2, 5000);
EXCEPTION
When OTHERS Then
Code: = SQLCODE;
msg: = substr (SQLERRM, 1, 500);
Dbms_output.put_line (code);
Dbms_output.put_line (msg);
END;
After execution, the following information is seen in output or in the Sqlplus window:
-20000
ORA-20000: Pay exceeds limit
ORA-06512: In "SCOTT." Salary_rai ", line 9
ORA-04088: Trigger ' SCOTT. Salary_rai ' Error during execution

The raise_application_error here is equivalent to refusing to insert or modify a transaction, and when the upper layer code accepts this exception, it is determined that the exception code equals-20000, which can be used to rollback the transaction or proceed with other transactions.

The inserting, updating, deleting, and raise_application_error in the above two examples are all functions in the Dbms_standard package, which can be referenced in Oracle's help documentation.
Create or Replace package Sys.dbms_standard is
Procedure Raise_application_error (Num binary_integer, msg varchar2,
function inserting return boolean;
function deleting return boolean;
function updating return boolean;
function updating (Colnam VARCHAR2) return boolean;
End

For before and after row-level triggers, the property values for the new and: Old objects are the same, primarily for the selection of execution triggers before or after the Oracle constraint (Constraint). Note that the value in the new object can be changed in the before row trigger, but not in the after row trigger.

The following is a instead of trigger, which is primarily used on the update of a view, the following is the syntax of the instead OF triggers:
CREATE OR REPLACE TRIGGER trigger_name
INSTEAD of <insert | Update | Delete> on View_name
[For each ROW]
When (condition)
DECLARE
BEGIN
--Trigger code
END;

The rest of the syntax is the same as the before and after syntax described earlier, except that the instead of keyword is used on the second line. For normal views, it is forbidden to make inserts, for example, because Oracle has no way of knowing which fields are in which table the field is operating. But we can build instead OF triggers to tell Oracle in the trigger body which parts of the table should be updated, deleted, or modified. Such as:

6, example three: instead OF trigger
New View
CREATE VIEW employee_salary (employee_id, maxsalary, MONTH, amount) as
SELECT a.employee_id, A.maxsalary, B.month, B.amount
From employment A, salary B
WHERE a.employee_id = b.employee_id

If you execute an INSERT statement
INSERT into Employee_salary (employee_id, maxsalary, MONTH, amount)
VALUES (10, 100000, ' 200606 ', 10000);
The system will error:
ORA-01779: Cannot modify a column corresponding to a non-key-value Save table

We can insert the values of the Insert view into two tables by establishing the following instead of stored procedures:
Create or Replace Trigger Employee_salary_rii
Instead of insert on employee_salary
For each ROW
DECLARE
V_CNT number;
BEGIN
--Check if the employee information exists
SELECT COUNT (*)
Into v_cnt
From employment
WHERE employee_id =: new.employee_id;
IF v_cnt = 0 Then
INSERT into employment
(employee_id, Maxsalary)
VALUES
(: new.employee_id,: new.maxsalary);
END IF;
--Check whether the employee's payroll information exists
SELECT COUNT (*)
Into v_cnt
From salary
WHERE employee_id =: new.employee_id
and MONTH =: NEW. MONTH;
IF v_cnt = 0 Then
INSERT into salary
(employee_id, MONTH, amount)
VALUES
(: new.employee_id,: NEW. MONTH,: New.amount);
END IF;
END Employee_salary_rii;

After the trigger is established, the insert operation above will prompt the system to insert a record successfully.
It should be noted, however, that "a record is inserted successfully" here, except that Oracle does not find an exception thrown in the trigger, and makes a judgment based on the number of records involved in the INSERT statement. If the main body of the trigger has nothing but an empty statement, Oracle will also report a "successful insert of a record". Similarly, Oracle returns "successfully inserting a record" even if 10 records are inserted into multiple tables in the trigger body.

Row-level triggers can solve most of the problems, but if you need to scan the table, for example, to check whether the total wage is overrun, it is not possible to use a row-level trigger, because a row-level trigger body cannot have a transaction involving the associated table, then a statement-level trigger is required. The following is the syntax for statement-level triggers:
CREATE OR REPLACE TRIGGER trigger_name
<before | After | Instead of ><insert | Update | Delete > on table_name
DECLARE
BEGIN
--Trigger body
END;

From the syntax definition, row-level triggers are missing for each row, and you cannot use the When clause to qualify entry conditions, and the rest are the same, including INSERT, UPDATE, delete, and instead of.

7, Example IV: One of the statement-level triggers
CREATE OR REPLACE TRIGGER salary_saiu
After INSERT OR UPDATE of amount on salary
DECLARE
V_sumsalary number;
BEGIN
SELECT SUM (amount) to v_sumsalary from salary;
IF v_sumsalary > 500000 Then
Raise_application_error (-20001, ' total wages over 500000 ');
END IF;
END;

The code above defines a statement-level trigger that checks whether all payroll records in the payroll table accumulate more than 500000 after the Insert and update Amount fields, and throws an exception if it is exceeded. As can be seen from this example, a statement-level trigger can scan an associated table table, and the resulting scan can be used as a marker for consistency. It is important to note that the correlation table is scanned in the Before statement trigger body and the after statement trigger body, and the result is not the same. Scan in the Before statement trigger body, the scan results will not include the newly inserted and updated records, which means that when the above code is replaced with a before trigger, the following statement will not error:
INSERT into salary (employee_id, month, Amount) VALUEs (2, ' 200601 ', 600000)
This is because the v_sumsalary obtained in the body does not include the newly inserted 600000 salary.
In addition, you cannot use the: New and: Old objects in statement-level triggers, which are significantly different from row-level triggers. If you need to check for inserted or updated records, you can use temporal table technology. A
Staging table is an Oracle database object that is characterized by the fact that the data created by the process is purged when the process of creating the data is finished. Processes and processes cannot access each other's data in the same temporary table, and operations on the staging table do not produce an undo log, reducing the consumption of the database. For specific information on temporary tables, refer to the relevant books.
in order to access the newly inserted modified records in a statement-level trigger, you can increase the row-level trigger, insert the updated record into the staging table, and then scan the temporary table in the statement-level trigger to obtain the modified record. The table structure of a temporary table is generally consistent with the structure of the associated table.

8, example five: statement-level trigger two
Purpose: Limit the total salary of each employee to not exceed 50000, otherwise stop operation on the table.
Create a temporary table
Create global temporary table salary_tmp
(
employee_id number,
MONTH VARCHAR2 (6),
AMOUNT number
)
on commit delete rows;

To insert an action record into a temporary table, create a row-level trigger:
CREATE OR REPLACE TRIGGER salary_raiu
After INSERT OR UPDATE of amount on salary
For each ROW
BEGIN
INSERT into Salary_tmp (employee_id, month, amount)
VALUES (: new.employee_id,: NEW. MONTH,: New.amount);
END;
The purpose of this trigger is to insert the updated record information into the staging table, and if more than one record is updated, each record is saved in a temporary table.

Create statement-level triggers:
Create or REPLACE TRIGGER Salary_sai
after INSERT or UPDATE of amount on salary
DECLARE
 v_ Sumsalary number;
BEGIN
 for cur in (SELECT * from salary_tmp) LOOP
  select SUM (amount)
    into V_sumsalary
   from salary
   where employee_id = cur.employee_id;
  if v_sumsalary > 50000 then
   raise_application_error (-20002, ' employee cumulative salary exceeds 50000 ');
  end IF;
    delete from Salary_tmp;
 end LOOP;
END;

The trigger first reads the updated or inserted records from the salary_tmp temporary table with a cursor, takes employee_id, finds the payroll records for all the same employees in the associated table salary, and sums them up. If an employee's total wage exceeds 50000, an exception is thrown. If the check passes, the temporary table is emptied, avoiding the next check of the same record.
Execute the following statement:
INSERT into salary (employee_id, month, amount) VALUEs (7, ' 200601 ', 20000);
INSERT into salary (employee_id, month, amount) VALUEs (7, ' 200602 ', 20000);
INSERT into salary (employee_id, month, amount) VALUEs (7, ' 200603 ', 20000);
System error when executing the third sentence:
ORA-20002: Employee cumulative salary exceeds 50000
Query salary table, found that the first two records were properly inserted, the third record is not inserted.

If the system structure is more complex, and the code of the trigger is much more, it is difficult for maintenance to write too much code in the trigger body. You can then write the code for all the triggers into the same package, with different trigger codes encapsulated in different stored procedures, and then call this part of the code in the trigger body.

9, example six: Packaging trigger code with packages
Purpose: To rewrite example five, to encapsulate the trigger body code
To create a code package:
CREATE OR REPLACE Package BODY SALARY_TRIGGER_PCK is

 procedure load_salary_tmp (i_employee_id in number,
       i_month        in VARCHAR2,
       i_amount       in number) is
 begin
  insert into salary_tmp VALUES (i_ employee_id, I_month, I_amount);
 end load_salary_tmp;

 procedure check_salary is
  v_sumsalary number;
 begin
  for cur in (SELECT * from salary_tmp) LOOP
   select SUM (amount)
    into v_sumsalary
    from Salary
    where employee_id = cur.employee_id;
   if v_sumsalary > 50000 then
    raise_application_error (-20002, ' Employees ' cumulative salary exceeds 50000 ');
   end IF;
   delete from Salary_tmp;
  end LOOP;
 end check_salary;
END SALARY_TRIGGER_PCK;
There are two stored procedures in package SALARY_TRIGGER_PCK, load_salary_tmp are used for call in row-level triggers, to mount updates to salary_tmp temporary tables, or to insert records. Instead, Check_salary is used to check whether an employee's cumulative wage is overrun in a statement-level trigger.

To modify row-level triggers and statement-level triggers:
CREATE OR REPLACE TRIGGER salary_raiu
After INSERT OR UPDATE of amount on salary
For each ROW
BEGIN
Salary_trigger_pck.load_salary_tmp (: new.employee_id,: NEW. MONTH,: New.amount);
END;

CREATE OR REPLACE TRIGGER Salary_sai
After INSERT OR UPDATE of amount on salary
BEGIN
Salary_trigger_pck.check_salary;
END;

The main code is then concentrated in the SALARY_TRIGGER_PCK, and only one invocation function is implemented in the trigger body.

10, trigger naming specification
To make it easier to name triggers and understand trigger meanings based on trigger names, you need to define a naming convention for triggers:
Trigger_name = table_name_trg_<r| s><a| b| i><i| u| D>

A trigger name is limited to 30 characters. The table name must be abbreviated to attach the trigger property information.
<r| S> a trigger based on row-level (row) or statement-level (statement)
<a| b| I>after, before or instead OF triggers
<i| u| D> whether the trigger event is insert,update or delete. If there are multiple triggering events then write

For example:
Salary_rai the row-level after trigger of the Salary table, the trigger event is the insert
Statement-level before trigger for EMPLOYEE_SBIUD employee table, triggering event is insert,update and delete

Oracle Trigger Learning