An example of Oracle trigger usages detailed _oracle

The examples in this article describe the use of Oracle triggers. Share to everyone for your reference, specific as follows:

Introduction of Triggers

The definition of a trigger is that when a condition is set up, the statement defined within the trigger is automatically executed. Therefore, the trigger does not need to be called artificially, nor can it be invoked. The trigger condition is then set when you define it. It should be explained that triggers can be divided into statement-level triggers and row-level triggers. A detailed introduction can refer to the information on the web, simply speaking, statement-level triggers can be triggered before or after certain statements are executed. Row-level triggers are triggered once when the row data in the defined table is changed.

Specific examples:

1, the statement-level trigger defined in a table, when the table is deleted, the program automatically executes the procedure defined in the trigger. This is the operation of the delete table is the condition of the trigger execution.
2. A row-level trigger is defined in a table, and when one row of data in the table changes, such as deleting a row of records, the trigger is automatically executed.

Second, trigger syntax

Syntax for triggers:

create [or replace] Tigger trigger name triggering time trigger event on
table name
[for each row]
begin
 Pl/sql statement
end

which

Trigger name : The name of the trigger object. Since triggers are automatically executed by the database, the name is only a name and has no real purpose.
Trigger Time : Indicates when the trigger executes, and this value is preferable:
before: Indicates that the trigger executes before the database action;
after: Represents a trigger execution after a database action.
Trigger Event : Indicates which database actions trigger this trigger:
Insert: Database insert triggers this trigger;
Update: Database modification triggers this trigger;
Delete: Database deletion triggers this trigger.
table name : The table in which the database triggers are located.
for each row: executes once for every row of a table trigger. If this option is not available, only the entire table is executed once.

Triggers can implement the following functions:

function :

1, allow/limit the modification of the table
2, automatically generate derived columns, such as self-added fields
3. Enforce data consistency
4. Provide audit and log records
5, prevent invalid transaction processing
6. Enable Complex business logic

Example

1), the following triggers are triggered before the table tb_emp is updated to allow the table to be modified at weekends:

Create or replace trigger auth_secure before insert or update or DELETE on
tb_emp
begin
  IF (To_char , ' DY ') = ' Sunday ') THEN
    raise_application_error (-20600, ' cannot modify table tb_emp on weekends ');
  End IF;
End;
/

2), the use of triggers to achieve the number of self-increase

To create a test table:

CREATE TABLE Tab_user (
  ID number (one) primary key,
  username varchar (m),
  password varchar ()
);

Create a sequence:

Copy Code code as follows:

Create sequence My_seq increment by 1-start with 1 nomaxvalue nocycle cache 20;

To create a trigger:

CREATE OR REPLACE TRIGGER my_tgr
 before INSERT on Tab_user
 for each row--performs a DECLARE on every row of the table's triggers Next_
 ID number;
BEGIN
 SELECT my_seq. Nextval into next_id from DUAL;
 : new.id: = next_id;--:new represents the newly inserted record end
;

To insert data into 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 the user executes a DML statement on the test table, the relevant information is logged to the log table

--Create a test table created
table test (
  t_id number  (4),
  t_name VARCHAR2 (),
  t_age number (2),
  T_sex CHAR
);
--Creating a record test table create
table Test_log (
  l_user  VARCHAR2,
  l_type  VARCHAR2 (),
  l_date  VARCHAR2 ()
);

To create a trigger:

--Create Trigger
or REPLACE TRIGGER test_trigger after
 DELETE or INSERT or UPDATE on TEST
DECLARE
 v_type TES T_log. L_type%type;
BEGIN
 IF inserting THEN
  --insert trigger
  v_type: = ' INSERT ';
  Dbms_output. Put_Line (' The record has been successfully inserted and logged to the log ');
 elsif updating THEN
  --update trigger
  v_type: = ' UPDATE ';
  Dbms_output. Put_Line (' record has been successfully updated and recorded to log ');
 elsif deleting THEN
  --delete trigger
  v_type: = ' DELETE ';
  Dbms_output. Put_Line (' record has been successfully deleted and recorded to log ');
 End IF;
 INSERT into Test_log
 VALUES
  (user, V_type, to_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss '));--user represents the current user name
End;
/
--below we will execute the DML statement
INSERT into Test VALUES (' Zhao ', "M");
UPDATE Test SET t_age = WHERE t_id =;
DELETE Test WHERE t_id =;
--then view the effect
SELECT * from test;
SELECT * from Test_log;

The results of the operation are as follows:

3), creating a trigger that maps the total number of units and total wages in each department in the EMP table

--Create a mapping table create
table Dept_sal
as SELECT deptno, COUNT (empno) total_emp, SUM (SAL) total_sal from
scott.emp< C4/>group by Deptno;
--Create Trigger
or REPLACE TRIGGER emp_info after
 INSERT or UPDATE or DELETE on Scott. EMP
DECLARE
 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--first delete the mapping information for v_emp in
 Cur_emp loop--dbms_output when triggered
  . Put_Line (V_emp.deptno | | v_emp.total_emp | | v_emp.total_sal);
  --Inserts data into
  dept_sal
  VALUES
   (v_emp. DEPTNO, V_emp. Total_emp, V_emp. Total_sal);
 End LOOP;
End;
--DML operation of the EMP table
INSERT into EMP (empno,deptno,sal) VALUES (' 123 ', ', ', 10000);
SELECT * from Dept_sal;
DELETE EMP WHERE empno=123;
SELECT * from Dept_sal;

The results appear as follows:

4), create triggers, to record the deletion data of the table

--Create TABLE Creation table
employee (
  ID  VARCHAR2 (4) Not NULL,
  name VARCHAR2 isn't null, age number
  (2) Not  null,
  sex CHAR not null
);
--insert data into
employee VALUES (' e101 ', ' Zhao ', N, ' M ');
INSERT into employee VALUES (' e102 ', ' Jian ', ' F ');
CREATE TABLE Old_employee as SELECT * from employee by creating a record table (containing data records)
;
--Create Trigger
or REPLACE TRIGGER tig_old_emp after
 DELETE on EMPLOYEE
 for each row--statement-level triggering, that is, each row fires once
BEGIN
 INSERT into Old_employee VALUES (: old.id,: old.name): Old. Age,: Old. SEX); --:old represents the old value end
;
/
--The following test
DELETE employee;
SELECT * from Old_employee;

5), creating triggers, using the view to insert data

-Create TABLE
Tab1 (tid number (4) PRIMARY key,tname VARCHAR2, Tage number (2));
CREATE TABLE tab2 (tid number (4), Ttel VARCHAR2 (), Tadr VARCHAR2 ());
Insert data into
tab1 VALUES (' Zhao ',);
INSERT into Tab1 VALUES (102, ' Yang ',);
INSERT into TaB2 VALUES (' 13761512841 ', ' Anhuisuzhou ');
INSERT into TaB2 VALUES (102, ' 13563258514 ', ' Anhuisuzhou ');
--Create VIEW connection two table create
OR REPLACE view Tab_view as SELECT Tab1.tid,tname,ttel,tadr from tab1,tab2 WHERE Tab1.tid = Tab2.ti D;
--Create Trigger
OR REPLACE TRIGGER tab_trigger
 INSTEAD of Insert on Tab_view
BEGIN
 inserts into TAB1 (T ID, Tname) VALUES (: NEW. TID: NEW. Tname);
 INSERT into TAB2 (Ttel, Tadr) VALUES (: NEW. Ttel: NEW. TADR);
End;
/
--You can now insert data into
tab_view VALUES ("Ljq", ' 13886681288 ', ' Beijing ') with the view.
--Query
SELECT * from Tab_view;
SELECT * from TAB1;
SELECT * from TAB2;

6), create triggers, compare the updated wages in the EMP table

--Create trigger
set serveroutput on;
CREATE OR REPLACE TRIGGER sal_emp
 before UPDATE on EMP for each
 ROW
BEGIN
 if:old. SAL >: NEW. SAL THEN
  dbms_output. Put_Line (' salary reduction ');
 Elsif:old. SAL <: NEW. SAL THEN
  dbms_output. Put_Line (' wage increase ');
 ELSE
  Dbms_output. Put_Line (' No change in wages ');
 End IF;
 Dbms_output. Put_Line (' Pre-update salary: ' | |: Old. SAL);
 Dbms_output. Put_Line (' Updated Salary: ' | |: NEW. SAL);
End;
/
--Perform update viewing effect
update emp SET sal = 3000 WHERE empno = ' 7788 ';

The results of the operation are as follows:

7, create the trigger, will be the operation create, drop stored in the Log_info table

--Create TABLE
log_info (
  manager_user VARCHAR2,
  manager_date VARCHAR2,
  manager_type VARCHAR2 (
  obj_name VARCHAR2),
  obj_type   VARCHAR2
);
--Create trigger
set serveroutput on;
Create or REPLACE TRIGGER trig_log_info after a
 create or DROP on
SCHEMA BEGIN
 inserts into Log_info
 VAL UES
  (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;
--View Effects
SELECT * from Log_info;
--related data dictionary-----------------------------------------------------
SELECT * from User_triggers;
-Must be logged in as a DBA to use 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 your Oracle database program design.