Shulou(Shulou.com)05/31 Report--

How to use triggers in Oracle? In view of this problem, this article introduces the corresponding analysis and answers in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

Trigger type

Triggers are stored as independent objects in the database. Unlike stored procedures and functions, stored procedures and functions require user display calls to execute, while triggers are initiated by an event. That is, triggers run automatically and implicitly when an event occurs. Also, the trigger cannot receive parameters. So running a trigger is called triggering or firing. ORACLE events refer to INSERT, UPDATE, and DELETE operations on tables in a database or similar operations on views. ORACLE extends the functions of triggers to trigger ORACLE, such as database startup and shutdown. Therefore, triggers are often used to complete the constraints of complex business rules which are difficult to complete by the integrity constraints of the database, or to monitor various operations on the database to achieve the function of audit.

Oracle triggers will certainly be used in the development, which will be explained in detail in this article.

The emp and dept tables under scott users in Oracle are mainly used in the example. The data are as follows.

I. the concept of trigger

1. Concept:

The essence of a trigger is a stored procedure, and as its name implies, the Oracle executes the code in the trigger when a particular event occurs. The subdivision of its composition can be divided into three parts: the first part is under what conditions the trigger will execute, that is, the event that the trigger is triggered. The second part is at what time the trigger is executed, that is, the occurrence event of the trigger, such as before,after. In the third part, what the trigger itself needs to do is the event that the trigger wants to express after it is triggered, the sql between begin and end.

Second, the classification of triggers:

1. Ddl trigger: the event triggered after the ddl operation is performed.

Common ddl operations are: grant (authorization), revoke (revoke authorization), create (create), drop (delete), alter (modify), comment (comment), audit (audit), and rename (rename). Before carrying out specific examples, let's explain another concept: user and schema in oracle:

Users in user:oracle have database objects and the right to add, delete, modify and query database objects. Schema: a collection of all database objects under this user, Collection. Similar to the relationship between the schema of the house in life and the owner of the house user, you are a user user you can view other people's house through alter session, but whether you can change the furniture in the house depends on whether the owner of the house grant you this permission, unless you are the highest authority person of all houses dba.

Ddl Example: disable all ddl operations for scott users

CREATE OR REPLACE TRIGGER scott_triggerBEFORE DDLON SCHEMABEGIN RAISE_APPLICATION_ERROR (- 20008 'disable all ddl operations of scott users'); END;create sequence myseq

You can see here that if the ddl operation is still used after the trigger is created, an error will be reported.

2. Dml trigger: based on dml operation, subdivision can be divided into row trigger and statement trigger.

A, statement trigger: the dml operation may affect many rows, mainly for data security.

Example: it is forbidden to modify emp table data on Thursday and Friday.

CREATE OR REPLACE TRIGGER emp_triggerBEFORE UPDATE OR DELETE OR INSERTON empBEGIN IF to_char (sysdate,'day') IN ('Thursday', 'Friday') THEN RAISE_APPLICATION_ERROR (- 20008 emp 'not allowed to modify the emp table on Thursday and Friday'); END IF;END;update emp set sal=800

After the trigger is set up here, there will be a trigger error when you want to change everyone's salary, and everyone's salary will affect a lot of lines.

B, row-level trigger: for the line that needs to be operated, the key word: for each row, used to

(1) realize the audit function of data:

Example: make a table to record deleted employee information. Record deleted employee information.

Here, in order not to change the data of the emp table in oracle, create a new emp_ new table

Create table emp_newasselect * from emp;create table emp_audit (name varchar2 (10), delete_time Date); CREATE OR REPLACE TRIGGER delete_triggerAFTER DELETE ON emp_newFOR EACH ROWBEGIN INSERT INTO emp_audit values (: old.ename,sysdate); END;delete from emp_new where empno='7499';select * from emp_audit

Here you can see that when creating the trigger, the for each row keyword is used,: old.*** is used to change the data in the previous table, and: new.*** is used to represent the data after the change, and there is a corresponding record in the log table after the data is deleted.

(2) to achieve data integrity:

Example: employees are required to raise their wages no less than the original salary, and the salary increase cannot be higher than the original 50%.

Here, in order not to change the data of the emp table in oracle, create a new emp_ new table

Create table emp_newasselect * from emp;CREATE OR REPLACE TRIGGER emp_triggerBEFORE UPDATE OF sal ON emp_newFOR EACH ROWWHEN (new.sal1.5*old.sal) BEGIN RAISE_APPLICATION_ERROR (- 20008 'wages only increase but not fall, and the increase should not be more than 50%'); END;update emp_new set sal = 1.6*sal where empno='7788'

Here, you can see that a trigger error will be triggered when you change the data. You can use UPDATE OF to modify a field in the table. In addition, if new and old are outside the PLSQL block, that is, BEGIN, you cannot add a colon.

(3) referential integrity:

Example: mainly used for cascading updates, for example, when updating the deptno in the dept table, the deptno of the emp table is also updated.

There are still two new tables that have the same data as the emp table and the dept table.

Create table emp_newasselect * from emp;create table dept_newasselect * from dept;CREATE OR REPLACE TRIGGER cascade_triggerAFTER UPDATE OF deptno ON dept_newFOR EACH ROWBEGIN UPDATE emp_new SET deptno=:new.deptno WHERE deptno=:old.deptno;END;update dept_new set deptno=15 where deptno=20;select * from dept_new

Select * from emp_new

With reference to the complete new refers to multiple tables with master-slave relationship, the relevant data of the slave table needs to be updated when the primary key of the master table is updated.

3. Alternative trigger:

Let's start with another concept: views with with check option:

If the definition of a view includes a condition, such as a where clause, and any INSERT or UPDATE statement applied to the view should include that condition, you must use WITH CHECK OPTION to define the view.

Example:

CREATE VIEW emp_view (ename,empno) AS SELECT ename,empno FROM emp WHERE deptno=20WITH CHECK OPTION

There is a conditional department number 20, then any statement that modifies this view must be aimed at the employees of department 20.

Continue to replace the concept of triggers: the keyword insteadof is mainly aimed at some complex views, because views generated by cascading tables cannot use keywords such as update,insert,delete, do not have keywords such as before,after, and cannot be built on views with with check option options. For example, if you create a new cascading view of emp table and dept table, you cannot add data to it. Now use triggers to solve this problem:

Example:

There are still two new tables with the same data as the emp table and the dept table.

CREATE TABLE emp_newASSELECT * FROM emp;CREATE TABLE dept_newASSELECT * FROM dept;CREATE VIEW emp_deptASSELECT d. Deptno. D. Dname. dept_new. Empno. enameFROM dre empties new eWHERE d.deptno=e.deptno

Here, scott users need to be authorized by sysdba before they can create a view:

Grant create view to scott;CREATE OR REPLACE TRIGGER insteadof_triggerINSTEAD OF INSERT ON emp_deptFOR EACH ROWDECLARE v_temp INT;BEGIN SELECT COUNT (*) INTO v_temp FROM dept_new WHERE deptno=:new.deptno; IF v_temp=0 THEN INSERT INTO dept_new (deptno,dname) VALUES (: new.deptno,:new.dname); END IF; SELECT COUNT (*) INTO v_temp FROM emp_new WHERE empno=:new.empno IF v_temp=0 THEN INSERT INTO emp_new (deptno,empno,ename) VALUES (: new.deptno,:new.empno,:new.ename); END IF;END;INSERT INTO emp_dept values (15MANRESOURCETHUMANRESOURCETHUMANRESOURCETHUMANRESOURCETHUMANRESOURCETHUMANRESOURCETHUMANRESOURCETHUMANRESOURE 7999); select * from emp_new

Select * from dept_new

Here, when the view is insert in the trigger, the corresponding emp_new and dept_new will be modified, so that the complex view will be modified.

4. System trigger: as the name implies, the event triggered by the system trigger, the commonly used system event startup,shutdown,db_roll_change,server error, etc.

Example: records the event and time when the database was started.

Because it is a system trigger, you need to log in with the authority of sysdba.

CREATE TABLE event_table (event VARCHAR2 (50), event_time DATE); CREATE OR REPLACE TRIGGER event_triggerAFTER STARTUP ON DATABASEBEGIN INSERT INTO event_table VALUES (ora_sysevent,sysdate); END

Select * from event_table

III. Comprehensive examples of triggers

Example: make a log to record some actions of scott users:

First, create log tables, sequences, and triggers under sysdba permissions:

CREATE TABLE object_log (logid NUMBER CONSTRAINT pk_logid PRIMARY KEY,operatedate DATE NOT NULL,objecttype VARCHAR2 (50) NOT NULL,objectowner VARCHAR2 (50) NOT NULL); CREATE SEQUENCE obj_log_seq;CREATE OR REPLACE TRIGGER object_triggerAFTER CREATE OR DROP OR ALTER ON DATABASEBEGIN INSERT INTO object_log VALUES (obj_log_seq.nextval,sysdate,ora_dict_obj_type,ora_dict_obj_owner); END

Create anything under the scott user:

CREATE SEQUENCE my_seq

Go back to sysdba permission to check whether there are any corresponding records in the log table:

SELECT * FROM object_log

This is the answer to the question on how to use triggers in Oracle. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.