"Oracle" triggers the most systematic introductory learning guide __oracle

Preface: What can you learn through this study

Directory:

One, what is a trigger.

second, what is the application scenario of the trigger.

1. Complex security Checks

2, the data confirmation

3. Realize audit function

4, the completion of data backup and synchronization

third, what is the syntax of triggers.

Iv. What types of triggers are available.

1, statement-level triggers

2, row-level triggers

v. Introduction of the Case

----------------------------------------------------------------------------------------------

One, what is a trigger.

Whenever a particular data action statement (INSERT, UPDATE, delete) is emitted on a specified table, Oracle automatically executes the sequence of statements defined in the trigger.

Note that the SELECT statement is not within the scope of the above operation.

First trigger: Automatically print "Insert new employee successfully" whenever new employee information is successfully inserted.

How to create a trigger

CREATE TRIGGER savenewemp

After insert

on EMP

Declare

begin

Dbms_output.put_line (' Insert new employee successfully ');

End ;

/

The triggers in this oracle are very similar to the listeners in our familiar java.

second, what is the application scenario of the trigger.

1. Complex security Checks

When we need a security policy, such as a weekend ban on the operation of the database.

2, the data confirmation

For example, when we get a raise in wages, if wages become less, we can use triggers to confirm.

3. Realize Audit function

The data operations done on the tracking table are also implemented separately in Oracle or by triggers, which are called value based audits .

4, the completion of data backup and synchronization

third, what is the syntax of triggers.

Above we omitted to mention a syntax: [For eachrow[when (condition)]]

First we need to understand the type of trigger, and if there is this sentence [for each row[when (condition) ], then he is a row-level trigger.

Iv. What types of triggers are available.

Statement-level trigger: executes once before or after the specified action statement operation, regardless of how many rows the statement affects.

is for the table.

Row-level triggers: Every record that triggers the action of a statement is triggered, and in a row-level trigger we use: Old and: New pseudo record variables to identify the state of merit.

is for the line.

v. Application scenarios of Triggers

1. Complex security Checks

Implement complex security checks: Prevent inserting new employees during non working hours.

/*

1. Weekend: To_char (sysdate, ' Day ') in (' Saturday ', ' Sunday ')

2, before work (9), after work (): To_number (To_char (sysdate, ' hh24 ')) not between 9 and 18

*/

Create or Replace Trigger Securityemp

Before insert

On EMP

Declare

Begin

If To_char (sysdate, ' Day ') in (' Saturday ', ' Sunday ')

or To_number (To_char (sysdate, ' hh24 ')) not between 9 and 18

Then

--Prohibit Insert new employee

Raise_application_error (-20001, ' prohibits inserting new employees during non-working hours. ');

End

/

Note that the value range of the first parameter in the Raise_application_error function above is -20000~-29999

The example above is an operation on a table, so there is no [for each row[when (condition)]]

2, the data confirmation

The increase in wages can not be more and less, each record needs to check, it is inevitable that the trigger is a row-level trigger.

/*

A rising salary cannot be less than the salary before the rise

1,: Old \: New: Represents the same record.

2,: Old: Represents the value of this line before the row is manipulated.

3.: NEW: Mark the value of this row after the operation of the line.

*/

Create or Replace Trigger Checksalary

Before update

On EMP

For each row

Begin

--The salary rise cannot be less than the salary before the rise

--if Salary < salary rise before then

If:new.sal <: Old.sal Then

Raise_application_error (-20002, ' a rising salary cannot be less than the salary before the rise. ');

End If;

End

/

This example above is an action for every row, so there is [for each row[when (condition)]]

3. Realize audit function

Create a trigger based on merit

/*

Audit---of database based on the function of audit

Give the employee a raise, and audit the employee's information when the salary exceeds 6000 yuan.

*/

--Create a table to hold audit information

CREATE TABLE Audit_info

(

Information VARCHAR2 (200)

)

Create or Replace Trigger Do_audit_emp_salary

After update

On EMP

For each row

Declare

Begin

-When the salary is higher than 6000, insert the audit information

If:new.sal > 6000 Then

INSERT into Audit_info values (: new.empno| | '  '||  : new.ename| | ' ' | |:new.sal);

End If;

End

/

This example above is an action for every row, so there is [for each row[when (condition)]]

4, the completion of data backup and synchronization using triggers to achieve data backup and synchronization: synchronous backup, there is no delay.

Distributed databases.

/*

Backup and synchronization of data

Automatically backs up new wages to the backup table when the employee is paid.

This is a row-level trigger.

*/

Create or Replace Trigger Sync_salary

After update

On EMP

For each row

Begin

--Automatically update backup table when primary table is updated

Update Emp_back set sal=:new.sal where Empno=:new.empno;

End

/

This is a synchronous backup, without network pressure, there is no delay.

There is also a snapshot backup that we do not elaborate on.

This chapter is a summary of what you want to help:

A database trigger is a stored Pl/sql program associated with a table.

Four scenes:

1. Complex security Checks

2, the data confirmation

3, the audit of the database

4, data backup and synchronization