29. Oracle Trigger

I. INTRODUCTION OF Triggers

The definition of a trigger is that when a condition is established, the statements defined in the trigger are automatically executed. Therefore, the trigger does not need to be called artificially, nor can it be called. Then, the trigger's trigger condition is actually set when you define it. It is necessary to explain that triggers can be divided into statement-level triggers and row-level triggers. The detailed introduction can refer to the information on the Internet, simply said that the statement-level triggers can be triggered before or after some statements are executed. A row-level trigger is triggered once when the row data in the triggered table is defined.
Specific examples:
1, a statement-level trigger defined in a table, when the table is deleted, the program automatically executes the action defined inside the trigger. This is the operation of the delete table is the trigger execution conditions.
2. A row-level trigger is defined in a table, and when a row of data in the table changes, such as deleting a row of records, the trigger is automatically executed.

Second, trigger syntax

The syntax of the trigger:
create [or replace] Tigger Trigger name trigger Time trigger Event
On table name
[For each row]
Begin
PL/SQL statements
End
which
Trigger Name: The name of the trigger object. Because the trigger is automatically executed by the database, the name is just a name and has no real purpose.
Trigger time: Indicates when the trigger executes, which is desirable:
Before: Indicates that the trigger executes before the database action;
After: Indicates that the trigger executes after the database action.
Trigger event: Indicates which database actions will 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 where the database trigger resides.
For each row: executes once on each row of the table trigger. If you do not have this option, only one time is executed for the entire table.

Triggers can achieve the following functions:

Function:
1. Allow/restrict the modification of the table
2. Automatically generate derived columns, such as self-increment fields
3. Enforce 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 updating the table tb_emp, in order to not allow the table to be modified on weekends:

On Tb_emp
Begin
   IF (To_char (sysdate, ' DY ') = ' Sunday ') then
       Raise_application_error (-20600, ' cannot modify table tb_emp on weekends ');
   END IF;
END;
/

2), use the trigger to achieve serial number self-increment
Create a test table:

CREATE TABLE Tab_user (
   ID number (one) primary key,
   Username varchar (50),
   Password varchar (50)
);

Create a sequence:

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--on each row of a table trigger
DECLARE
  NEXT_ID number;
BEGIN
  SELECT My_seq. Nextval to 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, logs related information to the log table

--Create a test table
CREATE TABLE Test (
   T_ID number   (4),
   T_name VARCHAR2 (20),
   T_age number  (2),
   T_sex  CHAR
);
--Create a record test table
CREATE TABLE Test_log (
   L_user   VARCHAR2 (15),
   L_type   VARCHAR2 (15),
   L_date   VARCHAR2 (30)
);

To create a trigger:

--Create a trigger
CREATE OR REPLACE TRIGGER Test_trigger
  After DELETE or INSERT or UPDATE on TEST
DECLARE
  V_type Test_log. L_type%type;
BEGIN
  IF INSERTING Then
    --insert Trigger
    V_type: = ' INSERT ';
    Dbms_output. Put_Line (' The record has been successfully inserted and has been recorded to the log ');
  Elsif UPDATING Then
    --update Trigger
    V_type: = ' UPDATE ';
    Dbms_output. Put_Line (' Records have been successfully updated and have been recorded to the log ');
  Elsif DELETING Then
    --delete Trigger
    V_type: = ' DELETE ';
    Dbms_output. Put_Line (' Records have 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 indicates the current user name
END;
/

--Let's execute the DML statements separately.
INSERT into Test VALUES (101, ' Zhao ', A, ' M ');
UPDATE Test SET t_age = WHERE t_id = 101;
DELETE Test WHERE t_id = 101;
--then look at the effect
SELECT * from Test;
SELECT * from Test_log;

The results of the operation are as follows:

3), create a trigger that maps the total number of each department in the EMP table and the total wage

--Create a mapping table



GROUP by Deptno;

     

--Create a trigger
CREATE 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
  
  For v_emp in Cur_emp LOOP
    --dbms_output. Put_Line (V_emp.deptno | | v_emp.total_emp | | v_emp.total_sal);  
    --Inserting data  
    INSERT into Dept_sal
    VALUES
      (V_emp. DEPTNO, V_emp. Total_emp, V_emp. Total_sal);
  END LOOP;
END;

      

--DML operations on the EMP table
INSERT into EMP (empno,deptno,sal) VALUES (' 123 ', ' 10 ', 10000);
SELECT * from Dept_sal;
DELETE EMP WHERE empno=123;
SELECT * from Dept_sal;

The results appear as follows:

4), create trigger, used to record the table delete data

--Create a table
CREATE TABLE Employee (
   ID   VARCHAR2 (4)  
   
   Age number  (2)    
   Sex  CHAR not NULL
);

--Inserting data
INSERT into employee VALUES (' e101 ', ' Zhao ', +, ' M ');
INSERT into employee VALUES (' e102 ', ' Jian ', +, ' F ');

--Create a record table (containing data records)
CREATE TABLE Old_employee as SELECT * from employee;

--Create a trigger
CREATE OR REPLACE TRIGGER tig_old_emp
  After DELETE on EMPLOYEE
  For each row-a statement-level trigger that fires once per row
BEGIN
  INSERT into Old_employee VALUES (: old.id,: Old.name,: Old. Age,: Old. SEX); --:old represents the old value
END;
/

--Test below
DELETE employee;
SELECT * from Old_employee;

5), create triggers, insert data using views

--Create TABLE 
 CREATE table Tab1 (TID number (4) PRIMARY key,tname VARCHAR2), Tage number (2)); 
 CREATE TABLE tab2 (tid number (4), Ttel VARCHAR2 (), Tadr VARCHAR2 (30)); 
 
-Insert data 
 INSERT INTO TAB1 VALUES (101, ' Zhao ', +); 
 INSERT into Tab1 VALUES (102, ' Yang ', 20); 
 INSERT into TaB2 VALUES (101, ' 13761512841 ', ' Anhuisuzhou '); 
 INSERT into TaB2 VALUES (102, ' 13563258514 ', ' Anhuisuzhou ');  
 
--Create a view connection 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_view 
 BEGIN 
 INSERT into TAB1 (TID, Tname) VALUES (: NEW. TID,: NEW. Tname); 
 INSERT into TAB2 (Ttel, Tadr) VALUES (: NEW. Ttel,: NEW. TADR); 
 END; 
/
 
--Now you can insert data using a view 
 INSERT into Tab_view VALUES (106, ' ljq ', ' 13886681288 ', ' Beijing '); 
 
--Query 
 SELECT * from Tab_view; 
 SELECT * from TAB1; 
 SELECT * from TAB2; 

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

--Create a 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 (' wage reduction ');
  Elsif:old. SAL <: NEW. SAL Then
    Dbms_output. Put_Line (' Increase in wages ');
  ELSE
    Dbms_output. Put_Line (' No change in wages ');
  END IF;
  Dbms_output. Put_Line (' Pre-update salary: ' | |: Old. SAL);
  Dbms_output. Put_Line (' Post-update salary: ' | |: NEW. SAL);
END;
/

--Perform the update view effect
UPDATE emp SET sal = empno = ' 7788 ';

The results of the operation are as follows:

7), create trigger, store operation Create, drop in Log_info table

--Create a table

   
   Manager_date VARCHAR2 (15),
   
   Obj_name     
   Obj_type     VARCHAR2 (15)
);

--Create a trigger
Set serveroutput on;
CREATE OR REPLACE TRIGGER trig_log_info
  After CREATE OR DROP on SCHEMA
BEGIN
  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;

--View effects
SELECT * from Log_info;

--Related data dictionary-----------------------------------------------------
SELECT * from User_triggers;
--Must be logged in as 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;

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

29. Oracle Trigger