[Oracle] trigger usage tutorial and naming rules

Trigger usage tutorial and naming rules


Contents
Trigger usage tutorial and naming convention 1
1. Trigger Introduction 1
2. Trigger Example 2
3. Trigger syntax and function 3
4. Example 1: one row-Level Trigger 4
5. Example 2: Row-Level Trigger 2 4
6. Example 3: instead of trigger 6
7. Example 4: Statement-Level Trigger 8
8. Example 5: Statement-Level Trigger 2 9
9. Example 6: Code 10 for encapsulating a trigger with a package
10. Trigger naming Rules 11

1. Trigger Introduction
A trigger is a type of database object. It is encoded in a similar way as a stored procedure and associated with a table. When a DML statement is used to operate a table, trigger execution can be triggered to achieve consistency, correctness, and normative control of the insert record. In the C/S era, when the client directly connects to the database, only the database itself can ensure Database Consistency. At this time, the primary key (Primary Key), foreign key (foreign key ), constraints and triggers become necessary control mechanisms. Trigger implementation is flexible and highly programmable, and naturally becomes the most popular control mechanism. In the B/S era, it has evolved into a layer-4 architecture. The client can no longer directly access the database, and only the middleware can access the database. To control Database Consistency, you can control both in the middleware and on the database. Many developers who prefer Java will regard the database as a black box and put most of the data control work in servlet for execution. In this way, you do not need to know much about the database, but also reduce the complexity of database programming, but also increase the workload of servlet programming. From the perspective of architecture design, the middleware function is to check the service correctness and execute the business logic. If you put the data consistency check in the middleware, the data consistency check must be performed in all areas involving data writing. Since database access is a remote call relative to middleware, it is not easy to compile a unified data consistency check code. Generally, similar check steps are added in multiple places. Once the consistency check process is adjusted, it will inevitably lead to modifications in multiple places, which not only increases the workload, but also cannot ensure the correctness of each check step. Trigger applications should be placed on key, multi-party-initiated, frequently accessed data tables. using too many triggers will increase the burden on the database and reduce database performance. Abandoning the trigger will lead to system architecture design problems and affect system stability.


2. Trigger example
The trigger code is similar to the stored procedure and written in PL/SQL scripts. The following is an example of a trigger:
New Employee Payroll salary
Create Table salary
(
Employee_id number, -- employee ID
Month varchar2 (6), -- month of salary
Amount number -- wage amount
)

Create the trigger salary_trg_rai associated with salary
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 ('salary month: '|: New. month );
8 dbms_output.put_line ('salary: '|: New. amount );
9 dbms_output.put_line ('trigger executed ');
10 end;
Open an SQL window (using the PL/SQL developer tool), or enter:
Insert into salary (employee_id, month, amount) values (1, '20140901', 200606 );
After execution, it can be seen in sqlplus or in output of SQL window.
Employee ID: 1
Monthly salary: 200606
Salary: 10000
Trigger executed

The first line of the Code defines that the database object type is trigger, and the trigger name is salary_trg_rai.
The second line shows that this is an after trigger, which is executed after the DML operation is implemented. The following insert statement indicates that this is a trigger for insert operations. This trigger is executed for each insert operation on the table.
The third row shows that this is a row-Level Trigger. When N records are inserted, the trigger is executed for each insert operation, and N times in total.
Declare is followed by the definition of local variables. If there is no definition of local variables, this part can be blank
The Code enclosed by begin and end is the execution part of the trigger. The consistency check is generally performed on the insert record. In this example, the inserted record and "trigger executed" are printed ".
The new object indicates the inserted record. You can use new. column_name to reference each field value of the record.


3. Trigger syntax and functions
The trigger syntax 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;

Trigger_name is the trigger name. <Before | after | instead of> You can select before, after, or instead. Before indicates that the trigger is executed before the DML statement is implemented, while "after" indicates that the trigger is executed after the DML statement is implemented, and "instead of" triggers are used to update the view. <Insert | update | Delete> You can select one or more DML statements. If you select multiple DML statements, use or to separate them, for example, insert or update. Table_name is the name of the table associated with the trigger.
[For each row] is optional. If for each row is specified, it indicates that the trigger is a row-level trigger, and the DML statement processes each record and runs the trigger; otherwise, it is a statement-Level Trigger. Each DML statement is triggered once.
The condition followed by when is the response condition of the trigger. It is only valid for Row-level triggers. When the operation record meets the condition, the trigger is executed. Otherwise, the trigger is not executed. In condition, you can reference the operation records through the new object and the old object (note that it is different from the previous one: New and: old, and you need to add a colon in the Code.
Trigger code can be divided into three types: Database Transaction code not involved, Database Transaction Code involving the associated table (table_name in the preceding syntax), and Database Transaction code other than the associated table. Among them, the first type of code only performs simple operations and judgment on data without DML statements. This type of code can be executed in all triggers. The second type of code involves data operations on the associated table, such as querying the total number of records of the associated table or inserting a record into the associated table. This type of code can only be used in statement-level triggers, if used in a row-Level Trigger, A ORA-04091 error is reported. The third type of code involves database transactions except associated tables, which can be used in all triggers.

The trigger function can be divided into three categories:
Override columns (only for before triggers)
Action taken (any trigger)
Transaction rejection (any trigger)
"Override column" is used for table field validation. When the inserted value is null or the inserted value does not meet the requirements, the trigger uses the default value or another value, in most cases, it is the same as the default attribute of the field. This function can only be executed in a row-level before trigger. "Take Action" refers to the features of the current transaction and operations on the relevant tables, such as updating other tables based on the records inserted in the current table, this trigger function can be used to maintain the total relationship between the general ledger and the sub-account in a bank. "Denial of transaction" is used to check the validity of data. When the updated data does not meet the consistency requirements of the table or system, the transaction is rejected by throwing an exception, the code at the upper layer can capture this exception and perform corresponding operations.

The following is an example to illustrate the syntax of the trigger subject in the example. You can understand the functions of the trigger in the example.

4. Example 1: one row-Level Trigger
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, the update trigger is valid only when the amount column is updated. Therefore, the following statement will not execute the trigger:
Update salary set month = '20140901' where month = '20160901 ';
In the if statement expression of the trigger body, inserting, updating, and deleting can be used to distinguish which DML operation is currently being performed, multiple similar triggers can be combined into one trigger to identify the attributes of the trigger event.

5. Example 2: Row-Level Trigger 2
Create employee table employee
Create Table employee
(
Employee_id number, -- employee ID
Maxsalary number -- maximum salary
)
Insert two records
Insert into employee values (1, 1000 );
Insert into employee values (2, 2000 );

Create or replace trigger salary_raiu
After insert or update of amount on salary
For each row
When (New. amount> = 1000 and (old. amount is null or old. Amount <= 500 ))
Declare
V_maxsalary number;
Begin
Select maxsalary
Into v_maxsalary
From employee
Where employee_id =: New. employee_id;
If: New. amount> v_maxsalary then
Raise_application_error (-20000, 'salary overrun ');
End if;
End;

The preceding example introduces a new table "employee". The maxsalary field in the table represents the highest salary that the employee can assign each month. The following trigger finds the highest monthly salary of the employee in the employee table based on the inserted or modified employee ID. If the inserted or modified amount exceeds this value, an error is returned.
The when clause in the Code indicates that the trigger only records the modified or inserted amount value that exceeds 1000, and the pre-modified amount value is less than 500. The new object and the old object represent the record objects before and after the operation, respectively. For insert operations, because there are no historical objects in the current operation record, all attributes of the old object are null. For delete operations, because the current operation record does not update objects, therefore, all attributes in the new object are null. However, in both cases, the reference of old and new objects and the use of the trigger subject are not affected, and the normal null values are processed in the same way.
In the trigger body, first pass: New. get the upper limit of the employee's salary, and then judge in the IF statement whether the updated employee's salary exceeds the upper limit. If the upper limit is exceeded, the error code is-20000, the error message is a custom error of "overpay. The raise_application_error contains two parameters: the first is the custom error code, and the second is the custom error code information. The custom error code must be smaller than or equal to-20000. After the statement is executed, an exception is thrown. If an exception clause exists at the previous layer, the exception will be caught. The following code is used:
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 displayed in the output or sqlplus window:
-20000
ORA-20000: wage exceeding limit
ORA-06512: In "Scott. salary_rai", line 9
ORA-04088: An error occurred while executing the trigger 'Scott. salary_rai'

Here, raise_application_error is equivalent to rejecting the insertion or modification of a transaction. When the upper-Layer Code accepts this exception, it determines that the exception code is equal to-20000. You can roll back the transaction or continue to process other transactions.

Inserting, updating, deleting, and raise_application_error used in the preceding two examples are all functions in the dbms_standard package. For details, refer to the Oracle 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 attribute values of the new and old objects are the same, mainly for the selection of execution triggers before or after the Oracle constraints (constraint. You can change the value in the new object in the before row trigger, but not in the after row trigger.

The following describes an instead of trigger, which is used to update the view. The following is the syntax of the instead of trigger:
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 other syntaxes are the same as the before and after syntaxes described earlier. The only difference is that the instead of keyword is used in the second line. For general views, insert and other operations are forbidden, because Oracle cannot know which table the operation field is. However, we can create an instead trigger to tell oracle in the trigger body what table fields should be updated, deleted, or modified. For example:

6. Example 3: instead of trigger
Create View
Create view employee_salary (employee_id, maxsalary, month, amount)
Select a. employee_id, A. maxsalary, B. Month, B. Amount
From Employee A, salary B
Where a. employee_id = B. employee_id

If you execute the insert statement
Insert into employee_salary (employee_id, maxsalary, month, amount)
Values (10,100 000, '000000', 200606 );
The system reports the following error:
ORA-01779: The columns corresponding to the non-key save table cannot be modified

We can create the following instead of stored procedure to insert the values of the inserted view into two tables respectively:
Create or replace trigger employee_salary_rii
Instead of insert on employee_salary
For each row
Declare
V_cnt number;
Begin
-- Check whether the employee information exists
Select count (*)
Into v_cnt
From employee
Where employee_id =: New. employee_id;
If v_cnt = 0 then
Insert into employee
(Employee_id, maxsalary)
Values
(: New. employee_id,: New. maxsalary );
End if;
-- Check whether the employee's salary 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 created, execute the insert operation, and the system will prompt that a record is inserted successfully.
However, it should be noted that "a record is successfully inserted" here, But Oracle does not find any exception thrown in the trigger, and makes a judgment based on the number of records involved in the insert statement. If the trigger has no subject, but it is only an empty statement, Oracle also reports "successfully inserted a record ". Similarly, even if ten records are inserted into multiple tables in the trigger body, Oracle returns the result of "successfully inserting a record ".




Row-level triggers can solve most of the problems, but if you need to scan and check the table, for example, to check whether the total salary is exceeded, row-level triggers won't work, because the row-Level Trigger subject does not contain transactions involving associated tables, you need to use a statement-Level Trigger. The syntax of a statement-Level Trigger is as follows:
Create or replace trigger trigger_name
<Before | after | instead of> <insert | update | Delete> On table_name
Declare
Begin
-- Trigger subject
End;

From the syntax definition, the row-Level Trigger does not have a for each row and cannot use the when clause to limit the entry condition. The rest are the same, including insert, update, both Delete and instead of can be used.


7. Example 4: one statement-Level Trigger
Create or replace trigger salary_saiu
After insert or update of amount on salary
Declare
V_sumsalary number;
Begin
Select sum (amount) into v_sumsalary from salary;
If v_samsung> 500000 then
Raise_application_error (-20001, 'total salary over 500000 ');
End if;
End;

The code above defines a statement-level trigger that checks whether all the wage records in the payroll are accumulated over 500000 after the insert and update amount fields are performed. If the sum is exceeded, an exception is thrown. From this example, we can see that a statement-Level Trigger can scan the associated table, and the scan results can be used as a marker for determining consistency. Note that the association table is scanned in the before statement trigger body and in the after statement trigger body, and the results are different. After scanning in the before statement trigger body, the scan results do not include newly inserted or updated records. That is to say, after the code is changed to the before trigger, the following statement will not report an error:
Insert into salary (employee_id, month, amount) values (2, '20140901', 200601)
This is because the v_sumsalary obtained in the subject does not include the newly inserted 600000 salary.
In addition, the new and old objects cannot be used in statement-level triggers, which is significantly different from row-level triggers. If you need to check the inserted or updated records, you can use the temporary table technology.
A temporary table is an Oracle database object. It is characterized by clearing the data created by the process after the data creation process ends. Processes and processes cannot access each other's data in the same temporary table, and operations on the temporary table do not generate undo logs, reducing database consumption. For more information about temporary tables, see related books.
To access the newly inserted modified records in a statement-level trigger, you can add a row-Level Trigger, insert the updated records into the temporary table, and then scan the temporary table in the statement-Level Trigger, obtain the modified record. The structure of a temporary table is generally the same as that of an associated table.


8. Example 5: Statement-Level Trigger 2
Purpose: To limit the total salary of each employee to no more than 50000; otherwise, the table is stopped.
Create temporary table
Create global temporary table salary_tmp
(
Employee_id number,
Month varchar2 (6 ),
Amount number
)
On commit Delete rows;

To insert operation records 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;
This trigger inserts the updated record information into the temporary table. If multiple records are updated, each record is saved in the temporary table.

Create a statement-Level Trigger:
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_samsung> 50000 then
Raise_application_error (-20002, 'cumulative employee salary exceeds 50000 ');
End if;
Delete from salary_tmp;
End loop;
End;

The trigger first reads updated or inserted records one by one from the temporary table salary_tmp using a cursor, obtains the employee ID, searches for the salary records of all identical employees in the associated table salary, and sums the records. If the total salary of an employee exceeds 50000, an exception is thrown. If the check succeeds, the temporary table is cleared to avoid the same records from being checked next time.
Run the following statement:
Insert into salary (employee_id, month, amount) values (7, '20140901', 200601 );
Insert into salary (employee_id, month, amount) values (7, '20140901', 200602 );
Insert into salary (employee_id, month, amount) values (7, '20140901', 200603 );
When the third sentence is executed, the system reports an error:
ORA-20002: the total wage of employees exceeds 50000
Query the salary table and find that the first two records are inserted normally. The third record is not inserted.


If the system structure is complex and the trigger code is large, writing too much code in the trigger body is difficult for maintenance. At this time, you can write all the trigger code to the same package. Different trigger codes are encapsulated in different stored procedures, and then the trigger body calls this part of code.

9. Example 6: Package the trigger code
Objective: To rewrite Example 5: encapsulate the trigger Subject Code
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_samsung> 50000 then
Raise_application_error (-20002, 'cumulative employee salary exceeds 50000 ');
End if;
Delete from salary_tmp;
End loop;
End check_salary;
End salary_trigger_pck;
The package salary_trigger_pck contains two stored procedures. load_salary_tmp is used to call a row-Level Trigger and load updates or insert records to the salary_tmp temporary table. Check_salary is used to check whether the cumulative salary of an employee exceeds the upper limit in a statement-Level Trigger.

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;

In this way, the main code is concentrated in salary_trigger_pck, and only one call function is implemented in the trigger body.

10. Trigger naming rules
To facilitate the trigger naming and understand the trigger meaning based on the trigger name, you need to define the trigger naming rules:
Trigger_name = table_name_trg _ <r | S> <A | B | I> <I | u | D>

The trigger name must be 30 characters long. The table name must be abbreviated to add trigger attribute information.
<R | S> row-level or statement-level triggers
<A | B | I> after, before, or instead trigger
<I | u | D> whether the trigger event is insert, update, or delete. If multiple trigger events exist, write them

For example:
The row-level after trigger for the salary_rai salary table. The trigger event is insert.
The statement-level before trigger of the employee_sbiud employee table. The trigger events are insert, update, and delete.