Shulou(Shulou.com)06/01 Report--

This article focuses on "how to use Oracle's trigger trigger". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let the editor take you to learn how to use Oracle's trigger trigger.

I. definition of trigger

Oracle trigger is a section of PL/SQL program encoder triggered by users when they do specific operations on objects in Oracle database. The events triggered include DML operations on the table, user DDL operations, database events, and so on.

II. Classification of triggers

Triggers are divided into statement-level triggers and row-level triggers.

Statement-level trigger: triggered before or after the execution of certain statements.

Row-level trigger: it is triggered once when the row data in the table in which the trigger is defined changes.

According to the type of user's specific operation event, it can be divided into five kinds of triggers.

Data manipulation (DML) trigger: this trigger is defined on the Oracle table and can be triggered when insert, update, or delete operations are performed on the table. If you trigger row-level data or statement-level trigger according to the table, it can be divided into row-level (row) trigger and statement-level trigger. According to the trigger before and after modifying the data, it can be divided into after trigger and before trigger.

Data definition operation (DDL) trigger: when performing create, alter, or drop operations on database objects, trigger triggers to save records, or qualify operations.

User and system event triggers: this type of trigger is used on the Oracle database system. When a database event is carried out, the trigger is triggered, which is generally used to record login-related information.

INSTEAD OF trigger: this type of trigger acts on the view and, when the user operates on the view, triggers the trigger to transform the related operation into an operation on the table.

Compound trigger: refers to the composition of multiple types of triggers in data manipulation (DML) triggers, for example, a trigger contains row-level triggers of after (or before) and statement-level triggers of after (or before) to perform more complex operations.

Third, trigger function

Oracle triggers can make specific calls to trigger blocks according to different database events, so it can help developers accomplish some problems that cannot be completed by PL/SQL stored procedures, such as:

Allow or to restrict modifications to the table

Automatically generate derived columns, such as self-incrementing fields (sequences)

Force data consistency

Provide audit and logging

Prevent invalid transactions

Enable complex business logic

However, it is not recommended to write business logic programs in triggers, because the maintenance of later data will greatly increase the cost.

Trigger syntax create [or replace] trigger trigger name trigger time trigger event on table name [for each row] begin PL/SQL statement block end

Grammatical explanation:

Trigger name: the name of the trigger object, which has no practical use.

Trigger time: specifies when the trigger is executed, with two values: before and after.

Before: execute triggers before database actions

After: execute triggers after database actions

Trigger event: indicates which database actions will trigger the trigger

The trigger is triggered when the insert database is inserted

The trigger is triggered when the update database is updated

The trigger is triggered when the delete database is deleted

Table name: the table where the database trigger is located

For each row: limit the execution scope of triggers, execute once for each row of triggers in the table, or once for the entire table without this option

Fifth, trigger use case 1: insert a piece of data into the job1 table and output the welcome join statement-preparation: copy the jobs table to the job11 table, and case 1 and case 2 are executed in the job1 table. CREATE TABLE JOB1 AS SELECT * FROM HR.JOBS;-- create trigger create or replace trigger trigger1 after inserton job1begin dbms_output.put_line ('I wish you an early raise!') ; end

After the trigger is created, a row of data validation results can be inserted into the job1 table.

Insert data to trigger the trigger's execution insert into job1 values ('ruirui','Prisident',15000,30000)

The effect is as follows:

Case 2: data validation, Tuesdays and Thursdays are not allowed to insert / update data into the emp1 table. Create or replace trigger trigger2 before insert or update on job1declare v_day varchar2 (20); begin-determine whether today is Tuesday or Thursday select to_char (sysdate,'day') into v_day from dual;-determine if v_day = 'Tuesday' then dbms_output.put_line ('Today is Tuesday, data cannot be inserted / updated!') Raise_application_error (- 2000); if v_day = 'Thursday' then dbms_output.put_line ('today is Thursday, you can't insert / update data!'); raise_application_error ('today is Thursday, you can't insert / update data!'); end if;end if;end

After the trigger is created, a row of data validation results can be inserted into the job1 table.

Insert data to trigger the trigger's execution insert into job1 values ('rui1','Prisident',15000,30000)

The effect is as follows:

Case 3: create trigger, record delete data of table-create job1_ log table to record delete record of job1 table create table job1_log as select * from job1 where 11 words-create trigger create or replace trigger trigger3 after delete on job1for each rowbegin insert into job1_log values (: old.job_id,:old.job_title,:old.min_salary,:old.max_salary); dbms_output.put_line ('record has been successfully deleted and logged to log') End

After the trigger is created, you can delete the data validation effect of one row of the job1 table.

-- Test delete from job1 where job_id='ruirui'

The effect is as follows:

Case 4: create triggers, record table updates-- create logging tables create table test_log (l_user varchar2 (15), l_type varchar2 (15), l_date varchar2 (20));-- create triggers create or replace trigger trigger4 after delete or insert or updateon job1declare v_type test_log.l_type%type;begin if deleting then v_type: = 'delete' Dbms_output.put_line ('the record has been successfully deleted and logged to the log'); elsif inserting then v_type: = 'insert'; dbms_output.put_line (' the record has been successfully inserted and logged to the log'); elsif updating then v_type: = 'update'; dbms_output.put_line (' the record has been successfully updated and logged'); end if Insert into test_log values (user,v_type,to_char (sysdate,'yyyy-mm-dd hh34:mi:ss')); end

After the trigger is created, the data validation effect in the job1 table can be updated.

-- Test insert into job1 values ('rui1','Prisident',15000,30000)

Update job1 set min_salary=20000 where job_id='rui1'

Delete from job1 where job_id='rui1'

The query test_log table records as follows:

Select * from test_log

Case 5: create a trigger to insert a piece of data into the record table before deleting it

-- create job1_ log table to record delete records of the job1 table create table test1_log as select * from job1 where 11 transfer-create trigger create or replace trigger trigger5 before delete on job1for each row-- row-level trigger begin insert into test1_log values (: old.job_id,:old.job_title,:old.min_salary,:old.max_salary); end

After the trigger is created, the data validation effect in the job1 table can be updated.

-- Test delete from job1 where job_id='rui2';commit

The query test1_log table records as follows:

Select * from test01_log

At this point, I believe you have a deeper understanding of "how to use Oracle's trigger trigger". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.