Oracle trigger usage example details, oracle trigger instance

Oracle trigger usage example details, oracle trigger instance

This example describes how to use Oracle triggers. We will share this with you for your reference. The details are as follows:

I. Introduction to triggers

The trigger definition means that when a condition is set up, the statements defined in the trigger are automatically executed. Therefore, triggers cannot be called without human intervention. Then, you have set the trigger condition when defining the trigger. It should be noted that triggers can be divided into statement-level triggers and row-level triggers. For more information, see online materials. Simply put, statement-level triggers can be triggered before or after certain statements are executed. The row-Level Trigger is triggered once when the row data in the table is changed.

Example:

1. When a statement-Level Trigger defined in a table is deleted, the program automatically executes the operation procedure defined in the trigger. This is the condition for the trigger execution to delete the table.
2. A Row-Level Trigger is defined in a table. When a row of data in the table changes, such as deleting a row of records, the trigger is automatically executed.

Ii. Trigger syntax

Trigger Syntax:

Create [or replace] tigger trigger name trigger time trigger event on table name [for each row] begin pl/SQL statement end

Where:

Trigger name: name of the trigger object. Because the trigger is automatically executed by the database, the name is only a name and has no substantive purpose.
Trigger time: specifies when the trigger is executed. This value is optional:
Before: indicates that the trigger is executed before the database action;
After: indicates that the trigger is executed after the database action.
Trigger event: Specifies which database actions will trigger this trigger:
Insert: This trigger is triggered when the database is inserted;
Update: This trigger is triggered when the database is modified;
Delete: This trigger is triggered when a database is deleted.
Table Name: The table where the database trigger is located.
For each row: the trigger for each row of the table is executed once. If this option is not available, the entire table is executed only once.

The trigger can implement the following functions:

Function:

1. Allow/restrict table modifications
2. automatically generate a derived column, such as an auto-increment Field
3. Forced Data Consistency
4. provide audit and logging
5. Prevent invalid transaction processing
6. Enable complex business logic

Example

1) The following trigger is triggered before the table tb_emp is updated to prevent table modification on weekends:

Create or replace trigger auth_secure before insert or update or DELETEon tb_empbegin IF (to_char (sysdate, 'dy ') = 'sunday') THEN RAISE_APPLICATION_ERROR (-20600, 'table tb_emp' cannot be modified on weekends.); end if; END ;/

2) use the trigger to auto-increment the serial number

Create a test table:

create table tab_user(  id number(11) primary key,  username varchar(50),  password varchar(50));

Create a sequence:
Copy codeThe Code is as follows: create sequence my_seq increment by 1 start with 1 nomaxvalue nocycle cache 20;

Create a trigger:

Create or replace trigger MY_TGR before insert on TAB_USER for each row -- execute DECLARE NEXT_ID number for each row of the table TRIGGER; begin select MY_SEQ.NEXTVAL INTO NEXT_ID from dual;: NEW. ID: = NEXT_ID; --: NEW indicates the END of the newly inserted record;

Insert data to a table:

insert into tab_user(username,password) values('admin','admin');insert into tab_user(username,password) values('fgz','fgz');insert into tab_user(username,password) values('test','test');COMMIT;

Query table results: SELECT * FROM TAB_USER;

3) When you execute DML statements on the test table, record the relevant information to the log table.

-- CREATE test TABLE test (t_id NUMBER (4), t_name VARCHAR2 (20), t_age NUMBER (2), t_sex CHAR ); -- CREATE Record Test table create table test_log (l_user VARCHAR2 (15), l_type VARCHAR2 (15), l_date VARCHAR2 (30 ));

Create a trigger:

-- Create trigger create or replace trigger TEST_TRIGGER after delete or insert or update on testdeclare V_TYPE TEST_LOG.L_TYPE % TYPE; begin if inserting then -- INSERT triggers V_TYPE: = 'insert '; DBMS_OUTPUT.PUT_LINE ('records have been successfully inserted and logged to logs'); elsif updating then -- UPDATE triggers V_TYPE: = 'updat'; DBMS_OUTPUT.PUT_LINE ('records have been updated successfully, logged to the log'); elsif deleting then -- DELETE triggers V_TYPE: = 'delete'; DBMS_OUTPUT.PUT_LINE ('record deleted successfully and logged to log'); end if; insert into TEST_LOG VALUES (USER, V_TYPE, TO_CHAR (SYSDATE, 'yyyy-mm-dd hh24: mi: ss'); -- USER indicates the current USER name END; /-- Next we will execute the DML statement insert into test VALUES (101, 'zhao', 22, 'M'); UPDATE test SET t_age = 30 WHERE t_id = 101; DELETE test WHERE t_id = 101; -- then view the effect SELECT * FROM test; SELECT * FROM test_log;

The running result is as follows:

3) create a trigger that maps the total number of employees and total salaries of each department in the emp table.

-- CREATE a ing table create table dept_sal ASSELECT deptno, COUNT (empno) total_emp, SUM (sal) total_salFROM scott. empGROUP BY deptno; -- create trigger create or replace trigger EMP_INFO after insert or update or delete on scott. empdeclare cursor CUR_EMP is select deptno, COUNT (EMPNO) AS TOTAL_EMP, SUM (SAL) AS TOTAL_SAL FROM scott. emp group by deptno; begin delete DEPT_SAL; -- When triggered, the ing table information FOR V_EMP IN CUR_EMP LOOP -- values (v_emp.deptno | v_emp.total_emp | v_emp.total_sal) is deleted first ); -- insert into DEPT_SAL VALUES (V_EMP.DEPTNO, V_EMP.TOTAL_EMP, V_EMP.TOTAL_SAL); end loop; END; -- insert into emp (empno, deptno, sal) VALUES ('100', '10', 123); SELECT * FROM dept_sal; delete emp where empno = 10000; SELECT * FROM dept_sal;

The result is as follows:

4) create a trigger to record the deletion data of a table

-- Create table employee (id VARCHAR2 (4) not null, name VARCHAR2 (15) not null, age NUMBER (2) not null, sex char not null ); -- insert into employee VALUES ('e101 ', 'zhao', 23, 'M'); insert into employee VALUES ('e102', 'jian ', 21, 'F'); -- CREATE a record TABLE (including data records) create table old_employee as select * FROM employee; -- create trigger create or replace trigger TIG_OLD_EMP after delete on employee for each row -- Statement-Level TRIGGER, that is, each row triggers begin insert into OLD_EMPLOYEE VALUES (: OLD. ID,: OLD. NAME,: OLD. AGE,: OLD. SEX); --: old indicates the old value END;/-- perform the following test: DELETE employee; SELECT * FROM old_employee;

5) create a trigger and insert data using a view

-- Create table tab1 (tid NUMBER (4) primary key, tname VARCHAR2 (20), tage NUMBER (2); create table tab2 (tid NUMBER (4 ), ttel VARCHAR2 (15), tadr VARCHAR2 (30); -- INSERT data INTO tab1 VALUES (101, 'zhao', 22); insert into tab1 VALUES (102, 'yang', 20); insert into tab2 VALUES (101, '000000', 'anhuisuzhou '); insert into tab2 VALUES (13761512841, '20170', 'anhuisuzhou '); -- CREATE a VIEW to connect two tables: create or replace view tab_view as select tab1.tid, tname, ttel, tadr FROM tab1, tab2 WHERE tab1.tid = tab2.tid; -- create trigger create or replace trigger TAB_TRIGGER instead of insert on TAB_VIEWBEGIN insert into TAB1 (TID, TNAME) VALUES (: NEW. TID,: NEW. TNAME); insert into TAB2 (TTEL, TADR) VALUES (: NEW. TTEL,: NEW. TADR); END;/-- now you can use the view to INSERT data INTO tab_view VALUES (106, 'ljq', '123', 'beijing '); -- Query SELECT * FROM tab_view; SELECT * FROM tab1; SELECT * FROM tab2;

6) create a trigger to compare the updated salary in the emp table.

-- CREATE a TRIGGER set serveroutput on; create or replace trigger SAL_EMP before update on emp for each rowbegin if: OLD. SAL>: NEW. sal then DBMS_OUTPUT.PUT_LINE ('salary demo'); ELSIF: OLD. SAL <: NEW. sal then DBMS_OUTPUT.PUT_LINE ('salary add'); ELSE DBMS_OUTPUT.PUT_LINE ('salary has not changed '); end if; DBMS_OUTPUT.PUT_LINE ('salary Before update:' |: OLD. SAL); DBMS_OUTPUT.PUT_LINE ('salary after update: '|: NEW. SAL); END;/-- execute UPDATE to view the results. UPDATE emp SET sal = 3000 WHERE empno = '20170101 ';

The running result is as follows:

7) CREATE a trigger and store the CREATE and DROP operations in the log_info table.

-- Create table log_info (manager_user VARCHAR2 (15), manager_date VARCHAR2 (15), manager_type VARCHAR2 (15), obj_name VARCHAR2 (15), obj_type VARCHAR2 (15 )); -- CREATE a TRIGGER set serveroutput on; create or replace trigger TRIG_LOG_INFO after create or drop on schemabegin insert into LOG_INFO VALUES (USER, SYSDATE, SYS. DICTIONARY_OBJ_NAME, SYS. DICTIONARY_OBJ_OWNER, SYS. DICTIONARY_OBJ_TYPE); END;/-- Test statement: create table a (id NUMBER); create type aa as object (id NUMBER); drop table a; drop type aa; -- SELECT * FROM log_info; -- related data dictionary SELECT * FROM USER_TRIGGERS; -- You must Log On As a dba before using this data dictionary SELECT * FROM ALL_TRIGGERS; SELECT * FROM DBA_TRIGGERS; -- ENABLE and disable alter trigger trigger_name DISABLE; alter trigger trigger_name ENABLE;

I hope this article will help you with Oracle database programming.

Articles you may be interested in:

• Oracle automatic backup script
• Steps for Oracle automatic backup and automatic backup
• Two methods for creating Database Link in Oracle
• Oracle Variable Length array varray, nested table, set usage
• Oracle RETURNING clause usage
• How to create an index in an Oracle database
• Oracle Database foreign key operations
• Oracle exception examples