Oracle triggers update this table data

Function:
1. Allow/restrict the modification of the table
2. Automatically generate derived columns, such as self-increment fields
3. Enforce data consistency
4. Provide audit and logging

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
/
Part of the trigger:
1. Trigger Name

2. Trigger statement
3. Trigger Limit
4. Trigger action
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 the provision of department_id
, when you insert the Employees table
2, when the department_id column of the Employees table is update
3.
Trigger limit
When (new_value.department_id<>80)

Restrictions are not required. This example indicates that the trigger executes if the column department_id is not equal to 80.
The new_value is the value represented by the new one.

4. Trigger action
is the body of the trigger
Begin
: new_value.commission_pct: = 0;
End

The main body is simple, which is to set the updated commission_pct column 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,
' [email protected] ', 60,10000,.25);
Select commission_pct from Employees
where employee_id=12345;
The trigger does not notify the user and changes the user's input value.
Trigger type:
1. Statement Trigger

2, line Trigger
3. INSTEAD of Trigger
4. System Condition Trigger
5. User Event Trigger
1. Statement Trigger

is a specific statement or trigger on a statement group that executes on a view on a table or in some cases. Can be associated with insert, UPDATE,

Delete, or the association on the combination. However, regardless of the combination, each statement trigger will only be activated once for the specified statement

。 For example, the UPDATE statement trigger is called only once, regardless of how many rows are being update.
Example:

Users who perform DML operations on the table need to be checked to see if they have the 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,
' You don't have access to modify this table. ');
End if;
End;
/

Even Sys,system users cannot modify Foo table
Test
Log the time and character of the modified table.
1, establish the test form

CREATE TABLE employees_copy as select *from hr.employees
2. Create a log table

CREATE TABLE Employees_log (
Who Varchar2 (30),
When date);
3.
A statement trigger is established on the Employees_copy table, and the Employees_log table is populated 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 is the function?

Which one of the insert/update/delete triggers the trigger?
You can use the inserting/updating/
DELETING conditional predicates, as 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 so that the statement type is logged.
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, ' 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 ', ' [email protected] ', sysdate,12);
Select *from
Employees_log
Update employees_copy set salary=50000 where employee_id =
12345;
2, line trigger
is a trigger that is activated for the affected rows, and is defined similar to a statement trigger with the following two exceptions:

1. The definition statement contains the for each row clause
2, in before ... For each row trigger, the user can reference the affected row values.

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 of the value before executing the DML statement is: old, after which the value is: New

Insert operation only: New
The delete operation is only: old
The update operation has both

The referencing clause simply renames new and old to New_value and old_value to avoid confusion. For example, to manipulate a named
The new table.

Does not work very much.
[Test]: Generate a self-increment 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 Trigger 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 Trigger
User events: User login, Logout, create/alter/drop/analyze
/Audit/grant/revoke/
Rename/truncate/logoff
Example: Record Delete 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,--trigger-related functions

Ora_dict_obj_type,
Sysdate);
End
/
3. Testing

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 a trigger, you cannot use the commit/
Rollback
Because the DDL statement has an implicit commit, it is also not allowed to use the
View:
Dba_triggers

This article turns from http://www.jb51.net/article/18252.htm

Oracle triggers update this table data