Oracle Triggers learning notes _oracle

Function:
1, allow/limit the modification of the table
2, automatically generate derived columns, such as self-added fields
3. Enforce data consistency
4. Provide audit and log records
5, prevent invalid transaction processing
6. Enable Complex business logic
Begin
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
/
Components of triggers:
1, Trigger Name
2. Trigger statement
3. Trigger Limit
4, Trigger the operation
1, Trigger Name
Create Trigger biufer_employees_department_id
Naming habits:
Biufer (before insert update for each row)
Employees table Name
DEPARTMENT_ID Column Name
2. Trigger statement
Like what:
DML statements on a table or view
DDL statements
Database shutdown or startup, 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, whether or not to specify the DEPARTMENT_ID, the Employees table to insert the time
2, on the Employees table department_id column to update the time
3. Trigger Limit
When (new_value.department_id<>80)
Restrictions are not necessary. This example indicates that if the column department_id is not equal to 80, the trigger executes.
The New_value is the value that represents the new post.
4, Trigger the operation
is the body of the trigger
Begin
: new_value.commission_pct: = 0;
End
The main body is simply that the updated commission_pct column is set 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, ' donny@hotmail.com ', 60,10000,.25);
Select commission_pct from Employees where employee_id=12345;
The trigger does not notify the user and then changes the user's input value.
Trigger type:
1. Statement triggers
2, row triggers
3. INSTEAD of Triggers
4. System Condition Trigger
5. User event triggers
1. Statement triggers
is a specific statement or a trigger on a statement group that executes on a table or on a view in some cases. The ability to insert, UPDATE,
Delete or associate on a combination. However, no matter what combination is used, the individual statement triggers are activated only once for the specified statement
。 For example, an UPDATE statement trigger is invoked only once, regardless of how many rows are being update.
Example:
Users who perform DML operations on a table need to be checked for appropriate privileges.
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, ' don ' t have access to modify this table. ');
End If;
End;
/
Even Sys,system users cannot modify the Foo table
Test
Log the time and character of the modified table.
1, the establishment of the test table
CREATE TABLE employees_copy as select *from hr.employees
2, the establishment of the log table
CREATE TABLE Employees_log (
Who Varchar2 (30),
When date);
3. Set up statement triggers on the Employees_copy table and 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)
Values (user, sysdate);
End;
/
4, testing
Update employees_copy set salary= salary*1.1;
Select *from Employess_log;
5, determine which statement to play a role?
Which one of the insert/update/delete triggers the trigger?
You can use the inserting/updating/deleting conditional predicate in a trigger to make a judgment:
Begin
If inserting then
-----
elsif updating Then
-----
elsif deleting Then
------
End If;
End
If updating (' COL1 ') or updating (' COL2 ') then
------
End If;
Test
1, modify the log table
ALTER TABLE Employees_log
Add (Action varchar2 (20));
2, modify the trigger to record the type of statement.
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, ' Your should never ever get this error. ');
Insert into Employees_log (
Who,action,when)
Values (user, l_action,sysdate);
End;
/
3, testing
Insert into Employees_copy (employee_id, last_name, email, hire_date, job_id)
VALUES (12345, ' Chen ', ' Donny@hotmail ', sysdate,12);
Select *from Employees_log
Update employees_copy set salary=50000 where employee_id = 12345;
2, row triggers
Is the trigger that is activated for the affected rows, and is defined like a statement trigger with the following two exceptions:
1, the definition statement contains a for each row clause
2. In before ... For each row trigger, the user can reference the affected row value.
Like what:
Defined:
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 clause:
The default name for the value before the DML statement is: old, and then the value is: New
Insert operation only: New
Delete operation only: old
Update operations both have
The referencing clause simply renames new and old to New_value and Old_value, in order to avoid confusion. For example, an operation called
The new table.
function is not very big.
[Test]: Generate a self-increasing serial number for the main health
drop table foo;
CREATE TABLE foo (ID number, data varchar2 (20));
Create sequence foo_seq;
Create or Replace Trigger BIFER_FOO_ID_PK
Before insert on Foo
For each row
Begin
Select Foo_seq.nextval into:new.id from dual;
End
/
INSERT into foo (data) VALUES (' Donny ');
INSERT into Foo VALUES (5, ' Chen ');
SELECT * from foo;
3. INSTEAD of triggers update view
Create or replace view Company_phone_book as
Select first_name| | ', ' | | last_name name, email, phone_number,
EMPLOYEE_ID emp_id
From Hr.employees;
Try updating email and name
Update Hr.company_phone_book
Set Name= ' Chen1, Donny1 '
where emp_id=100
Create or Replace Trigger Update_name_company_phone_book
INSTEAD of
Update on Hr.company_phone_book
Begin
Update hr.employees
Set employee_id=:new.emp_id,
FIRST_NAME=SUBSTR (: New.name, InStr (: New.name, ', ') +2),
Last_name= substr (: New.name,1,instr (: New.name, ', ')-1),
Phone_number=:new.phone_number,
Email=:new.email
where employee_id=:old.emp_id;
End
4. System event triggers
System events: Database startup, shutdown, server error
Create Trigger Ad_startup
After startup
On database
Begin
--Do some stuff
End
/
5. User event triggers
User events: User login, logoff, Create/alter/drop/analyze/audit/grant/revoke/
Rename/truncate/logoff
Example: Record deletion object
1. Log table
CREATE TABLE Droped_objects (
object_name VARCHAR2 (30),
Object_type VARCHAR2 (30),
dropped_on date);
2. Trigger
Create or Replace Trigger Log_drop_trigger
Before drop on Donny.schema
Begin
INSERT into droped_objects values (
Ora_dict_obj_name,--functions associated with triggers
Ora_dict_obj_type,
Sysdate);
End
/
3. Test
CREATE TABLE Drop_me (a number);
CREATE View Drop_me_view as select *from drop_me;
Drop View Drop_me_view;
drop table Drop_me;
Select *from droped_objects
Disabling and enabling triggers
ALTER TRIGGER <trigger_name> disable;
Alter trigger <trigger_name> enable;
Transaction processing:
In triggers, you cannot use the Commit/rollback
Because the DDL statement has an implicit commit, it is not allowed to use the
View:
Dba_triggers