Oracle Trigger Development ____oracle

The purpose of trigger development: to ensure that databases meet specific business rules and enterprise logic, you can use triggers, constraints, subroutines. Because
Constraints are the simplest and performance, the selected constraints, if the constraints are not satisfied with the trigger, if the trigger can not meet the selection subroutine.

First, trigger function:
1, the control of data security, at the server level to control data security is done through authorization and reclaim object permissions, such as the AMS user to Scott/tiger users of the table DML operations,
Grant SELECT, Delete,update,insert on EMP to AMS;
But in order to implement more complex security patterns, such as limiting the data to be modified on a table, triggers need to be used, for example, only 9:00 to 17:00 to change the database table operation of the EMP table
Create or Replace Trigger Triger_stat_before
Before insert or UPDATE or delete on EMP

Declare
--Local variables
Begin
if (To_char (sysdate, ' HH24 ') not between ' 9 ' and ') then
Raise_application_error (-20101, ' nonworking days ');
End If;
End Triger_before;

2, the implementation of data audits, monitoring of illegal and suspicious data activities, Oracle itself provides audit capabilities. For example, after the EMP DML operation,
Writes the SQL operation information (user, time, etc.) to the dictionary. However, only SQL operations can be audited and data changes cannot be recorded. If you want to record data
Changes must use DML triggers. such as wage changes written to the data dictionary table.
--Create a dictionary table
drop table Emp_change;
CREATE TABLE Emp_change (
CNAME VARCHAR2 (200),
Oldsal number,
Newsal number,
Change_time TIMESTAMP (6)
)
--Creating triggers
Create or Replace Trigger Triger_row_after
After update of SAL on EMP
For each row
Declare
--Local variables
V_temp number;
Begin
Select COUNT (*) into the v_temp from Emp_change where emp_change.cname=:old.ename;
If V_temp =0 Then
INSERT into Emp_change values (: old.ename,:old.sal,:new.sal,sysdate);
Else
Update Emp_change Set oldsal=:old.sal,newsal=:new.sal,change_time = Sysdate
where emp_change.cname =: old.ename;
End If;
End Triger_row_after;

Execute sql:update emp Set sal = sal*1 where deptno=30
Will write the employee's salary changes to the Emp_change table

3, the implementation of data integrity, write check can not do when the trigger, such as the new salary can not be lower than the original wages can not be higher than the original salary of 20%.
Create or Replace Trigger Triger_row_before
Before update of Sal on EMP
For each row
When (new.sal <old.sal or new.sal >old.sal*1.2)
Declare
--Local variables
Begin
Raise_application_error (-20931, ' The new salary can not be lower than the original wage, and the increase can not be higher than 20% ');
End Triger_row_before;
Execute sql:update emp Set sal = sal*0.3 where deptno=30
Will complain: The new salary can not be lower than the original salary, and the increase can not be higher than 20%

4, to achieve referential integrity, referential integrity refers to two tables related to the relationship, the primary foreign key relationship, when the main table data deleted from the table data also deleted
When you modify primary key column data for a primary table, cascading updates are modified from the table-related data columns. Cascading deletes can be by constraint on DELETE CASCADE keyword
Alert table EMP Add constraint Fk_deptno foreign key (DEPTNO) References Dept (deptno) on DELETE cascade;
However, cascading updates cannot be implemented.
such as: Update dept set dept.deptno=50 where dept.deptno=20
Illegal constraint condition, child record already found
Triggers can implement cascading update capabilities.
Create or Replace Trigger Triger_up_casecade
After update of DEPTNO on dept
For each row
Declare
--Local variables
Begin
Update emp Set Emp.deptno=:new.deptno where Emp.deptno =: old.deptno;
End Triger_up_casecade;
Executes sql:update dept set Dept.deptno =50 where Dept.deptno =10
A cascade updated the employee department Number 10 for the EMP table to 50.

Second, triggers are implicitly executed stored procedures, allowing for table and attempted DML operations and system based events (enabling the database, shutting down the database, logging on)
1 various DML triggers:
Composition of triggers: trigger events, trigger conditions, and trigger actions
A trigger event is the SQL that triggers the trigger, database events, user events, as follows
Start shutdown Routine
Oracle error messages
User logon and Disconnect sessions
DML operations on a table or view
DDL operations on any scenario
The B trigger condition is the WHERE clause that specifies an expression
C trigger action is the execution of an SQL statement or Pl/sql block

The emphasis is on DML triggers.
Trigger time before means that triggers are triggered before a DML operation is performed, after which the trigger is triggered after a DML operation is performed
Trigger type triggering event occurs several times after triggering, and if the statement trigger (default), only one triggering code is executed
If the row firing type is specified, the trigger code is executed once on each action line.
Attention:
For the Oracle row-level trigger (for each row), you cannot do anything with this table, including reading
Principle: To prevent dirty reading
In a before insert trigger, access to this table can be achieved;
Access to this table cannot be achieved in the after insert trigger;
Access to this table is not possible in before/after update/delete triggers.

Statement triggers are used primarily to audit DML operations or to ensure that DML operations are performed securely, by using a statement trigger, such as: Before updating an employee table
Check that the current date is not Saturday, or Sunday, if it is not updated.

Before statement triggers: Ensure that DML statements are normally executed
After statement triggers: Auditing DML operations, or summarizing operations after a DML operation
Before row triggers: Make sure the data is logical, use constraints to restrict input conditions, such as the employee's new salary can not be lower than the original salary
After-line triggers: Audit data changes, such as a change in employee pay, the change of employee information to the audit table

Limit row triggers: In a row trigger, not all rows are triggered but conditional, formatting
Create or Replace Trigger Triger_test
Before update or delete on Trigger_tab
For each row--The row triggers are added to this statement
WHERE (: Old.name= ' Chen ')
Declare
V_name VARCHAR2 (200);
Begin
End

Raise_application_error Method Introduction:
The error code and content in it are all customized. The description is custom, and it is not, of course, an error class that has already been named in the system, and is a custom transaction error type that calls this function.
Error_number_in is allowed from 20000 to 20999 so that it does not conflict with any of ORACLE's error codes.
The length of the error_msg_in can not exceed 2K, otherwise intercept 2K.

: Old.name and: New.name introduction
: New.value is the new value for name.
: Old.value is the value before name modification.
: Old only for update or delete, for INSERT, it has no data before, so it can only be used: new
When old and new are referenced in the trigges body, the prefix must be prefixed: As with the binding variable, the declaration part is not required.

SQL scripts
CREATE TABLE Trigger_tab (
UserID number NOT NULL primary key,
UserName VARCHAR2 (200),
Age number,
Address VARCHAR2 (200)
)
Insert into Trigger_tab (userid,username,age,address) VALUES (1, ' Chen ', 20, ' Chaling ');
Insert into Trigger_tab (userid,username,age,address) VALUES (2, ' Li ', 15, ' leisurely ');
Insert into Trigger_tab (userid,username,age,address) VALUES (3, ' en ', 29, ' Beijing ');

Creating triggers
Create or Replace Trigger Triger_test
Before update or delete on Trigger_tab
For each row--The row triggers are added to this statement
Declare
--Local variables
Begin
if (: new.age<18) then--:old.age is the value before the record is updated: New.age is the value after the update is recorded
Raise_application_error (-20001, ' age less than 18 years old, cannot be deleted and modified ');
End If;
End Triger_test;

Command Window window executes SQL
Update Trigger_tab t set t.address= ' Changsha ' where t.username= ' Li ';
Print Spooler
ORA-20000: Age less than 18 years old, cannot modify and delete
ORA-06512: In "SCOTT." Triger_test ", line 5
ORA-04088: Trigger ' SCOTT. Triger_test ' Error during execution

called in Java
public class Testtriger {
public static void Main (String []args) {
String sqlstr = "Update Trigger_tab t set t.address= ' Changsha ' where T.username= ' Li '";
Connection conn = Connector.getconn ();
PreparedStatement pstmt;
try {
pstmt = Conn.preparestatement (SQLSTR);
int rownum = Pstmt.executeupdate ();
catch (SQLException e) {
if (E.geterrorcode () ==20001) {
String errorm = E.getmessage ();
System.out.println (Errorm);
System.out.println (Errorm.split ("ORA") [1].replace ("-" +e.geterrorcode () + ":", ""));
}
}
}
}
Caught exception is an exception code, this code is in Raise_application_error (-20001, ' age is less than 18 years old, can not delete and modify ');
Set-20001, remove the front minus sign.
Exception information gets
ORA-20000: Age less than 18 years old, can not modify and delete and modify
ORA-06512: In "SCOTT." Triger_test ", line 5
ORA-04088: Trigger ' SCOTT. Triger_test ' Error during execution
After the format is to think of the exception information, "Age is less than 18 years old, can not modify and delete and modify."