trigger
The code block that is automatically executed when the specific event occurs. Similar to the stored procedure, but the user cannot call them directly.
Features:
1. Allow / limit the modification of the table
2, automatically generate the gap, such as self-increment field
3, forced data consistency
4, provide audit and logging
5, prevent invalid transaction processing
6, enabling complex business logic
Start create trigger biufer_employees_department_id before insert or update of department_id on employees referencing old as old_value new as new_value for each row when (new_value.department_id <> 80) begin: new_value.commission_pct: = 0; end; /
Part of the trigger:
1, trigger name
2, trigger statement
3, trigger limit
4, trigger operation
1, trigger name
CREATE TRIGGER BIUFER_EMPLOYEES_DEPARTMENT_ID
Name habits:
Biufer (Before INSERT UPDATE for Each ROW)
EMPLOYEES name
Department_id column name
2, trigger statement
such as:
DML statement on table or view
DDL statement
Database close or start, Startup Shutdown, etc.
Before insert or update
Of Department_ID
On Employees
Referencing Old As Old_Value
NEW as new_value
For Each ROW Description:
1. When the Department_ID is specified, INSERT is performed on the EMPLOYEES table.
2, when Update is performed on the department_id column of the EMPLOYEES table
3, trigger limit
When (new_value.Department_id <> 80)
Restrictions are not required. This example indicates that if the column department_id is not equal to 80, the trigger will execute.
Where new_value is the value represented by the new.
4, trigger operation
Is the main body of the trigger
Begin
: new_value.commission_pct: = 0;
END;
The main body is simple, it is to set the updated commission_pct to 0
trigger:
INSERT INTO Employees (Employee_ID,
Last_name, first_name, hire_date, job_id, email, department_id, salary, commission_pct)
Values (12345, 'chen', 'donny', sysdate, 12, 'donny @ Hotmail.com', 60, 10000, .25;
Select Commission_pct from Employees Where Employee_ID = 12345;
The trigger does not notify the user to change the user's input value.
Trigger type:
1, statement trigger
2, row trigger
3, INSTEAD OF trip
4, system condition trigger
5, user event trigger
1, statement trigger
Is a trigger on a specific statement or statement group executed on the table or in some cases. Ability to be associated with INSERT, UPDATE, DELETE or combination. But no matter what combination of use, each statement trigger will only activate the specified statement once. For example, regardless of how much Update, only the UPDATE statement trigger is called. example:
You need to perform security checks for users who perform DML operation on the table to see if there is appropriate privilege.
Create Table Foo (a Number);
Create Trigger Biud_foo Before Insert Or Update or Delete ON Foo
Begin if User Not in ('DONNY') THEN RAISE_APPLICATION_ERROR (-20001, 'You don't have it access to modify this table.'); endiff;
/ Even if SYS, System users cannot modify FOO tables
[test]
The time and character of the modified table are logged.
1. Establish a test list
CREATE TABLE Employees_copy as success * from hr.employees
2, establish a log list
Create Table Employees_log (WHO VARCHAR2 (30), WHEN DATE
3, establish a statement trigger on the Employees_copy table to populate the Employees_log table in the trigger.
Create or Replace Trigger biud_employee_copy before insert or update or delete on Employees_copy begin insert INTO Employees_log (WHO, WHEN); END; /
4, test
Update Employees_copy set salary = salary * 1.1; select * from Employess_log;
5. Is it determined which statement works?
That is, which of INSERT / UPDATE / DELETE triggers the trigger?
INSERTING / UPDATING / DELETING condition predicate can be used in the trigger:
Begin if inserting the ----- Elsif Updating the ----- Elsif Deleting the ------ Endiff;
IF updating ('col1') or updating ('col2') THEN ------ END IF;
[test]
1, modify the log list
ALTER TABLE Employees_log
Add (Action Varchar2 (20));
2. Modify the trigger to record the statement type.
Create or replace trigger biud_employee_copy Before insert or update or delete On employees_copy Declare L_action employees_log.action% type; Begin if inserting then l_action: = 'Insert'; elsif updating then l_action: = 'Update'; elsif deleting then l_action: = 'Delete '; else raise_application_error (-20001,' You should never ever get this error '.); Insert into employees_log (Who, action, when) Values (user, l_action, sysdate); End; / 3, the test insert into employees_copy (employee_id , last_name, email, hire_date, job_id) values (12345, 'Chen', 'Donny @ hotmail', sysdate, 12); select * from employees_logupdate employees_copy set salary = 50000 where employee_id = 12345; 2, row trigger
Refers to the triggers that are affected by each row, definition is similar to the statement trigger, and there are two exceptions:
1. Define the statement in the statement containing for each row clause
2, in the Before ... for Each ROW trigger, the user can reference the affected row value.
For example: Definition:
create trigger biufer_employees_department_id before insert or update of department_id on employees_copy referencing old as old_value new as new_value for each row when (new_value.department_id <> 80) begin: new_value.commission_pct: = 0; end; / Referencing clauses:
The default name for the value before executing the DML statement is: OLD, the following value is: New
INSERT operation only: New
Delete operation is only: OLD
UPDATE operations
