Oracle Trigger Usage

Original Link: https://www.jb51.net/article/80804.htm

1. Introduction to Triggers

The definition of a trigger means that when a condition is established, the statement defined in the trigger is automatically executed.

Therefore, triggers do not need to be called artificially, nor can they be called.

Then the trigger's trigger condition is actually set when you define it.

It is important to note that triggers can be divided into statement-level triggers and row-level triggers.

Detailed descriptions can refer to the information on the Web. Simply put, statement-level triggers can be triggered before or after certain statements are executed.Row-level triggers are triggered once when row data changes in the table defined to trigger.

Examples:

1. A statement-level trigger defined in a table. When the table is deleted, the program automatically executes the action defined in the trigger.This is the deletion of the table that is the condition for the trigger to execute.
2. A row-level trigger is defined in a table. When a row of data in this table changes, such as deleting a row of records, the trigger will also be automatically executed.

2. Trigger Syntax

Syntax for triggers:

create [or replace] tigger Trigger name Trigger time Trigger event
on Table Name
[for each row]
begin
 pl/sql Sentence
end

Where:

Trigger name: The name of the trigger object.Since triggers are automatically executed by the database, the name is just a name and has no real purpose.
Trigger time: Indicates when the trigger will execute, and this value is preferable:
Before: indicates that the trigger executes before the database action;
After: Indicates that a trigger executes after a database action.
Trigger Event: Indicates which database actions trigger this trigger:
Insert: A 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 trigger is located.
For each row: executes once for each row trigger of the table.If this option is not available, the entire table is executed only once.

Triggers perform the following functions:

Functions:

1. Allow/Restrict modifications to tables
2. Automatically generate derived columns, such as self-added fields
3. Enforcing data consistency
4. Provide audit and log records
5. Prevent invalid transaction processing
6. Enable complex business logic

Give an example

1) The following triggers are triggered before the table tb_emp is updated to disallow table modifications on weekends:

create or replace trigger auth_secure before insert or update or DELETE
on tb_emp
begin
  IF(to_char(sysdate,'DY')='Sunday') THEN
    RAISE_APPLICATION_ERROR(-20600,'Table cannot be modified on weekends tb_emp');
  END IF;
END;
/

2) Use triggers to increase sequence number

Create a test table:

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

Create a sequence:

Copy the code 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 once for each row trigger of the table
DECLARE
 NEXT_ID NUMBER;
BEGIN
 SELECT MY_SEQ.NEXTVAL INTO NEXT_ID FROM DUAL;
 :NEW.ID := NEXT_ID; --:NEW Represents the newly inserted record
END;

Insert data into the 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, record the relevant information to the log table

--Create Test Table
CREATE 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 triggers:

--Create 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('Record has been inserted successfully and logged');
 ELSIF UPDATING THEN
  --UPDATE trigger
  V_TYPE := 'UPDATE';
  DBMS_OUTPUT.PUT_LINE('Record has been successfully updated and logged');
 ELSIF DELETING THEN
  --DELETE trigger
  V_TYPE := 'DELETE';
  DBMS_OUTPUT.PUT_LINE('Record deleted successfully and logged');
 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;
/
--Let's do it separately DML Sentence
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 see the effect
SELECT * FROM test;
SELECT * FROM test_log;

The results are as follows:

3) Create a trigger that maps the total number and total salary for each department in the emp table

--Create Mapping Table
CREATE TABLE dept_sal AS
SELECT deptno, COUNT(empno) total_emp, SUM(sal) total_sal
FROM scott.emp
GROUP BY deptno;
--Create 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
 DELETE DEPT_SAL; --Delete map information first when triggered
 FOR V_EMP IN CUR_EMP LOOP
  --DBMS_OUTPUT.PUT_LINE(v_emp.deptno || v_emp.total_emp || v_emp.total_sal);
  --insert data
  INSERT INTO DEPT_SAL
  VALUES
   (V_EMP.DEPTNO, V_EMP.TOTAL_EMP, V_EMP.TOTAL_SAL);
 END LOOP;
END;
--Yes emp Table Processing DML operation
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 are as follows:

4) Create triggers to record deleted data from tables

--Create Table
CREATE TABLE employee(
  id  VARCHAR2(4) NOT NULL,
  name VARCHAR2(15) NOT NULL,
  age NUMBER(2)  NOT NULL,
  sex CHAR NOT NULL
);
--insert data
INSERT INTO employee VALUES('e101','zhao',23,'M');
INSERT INTO employee VALUES('e102','jian',21,'F');
--Create Record Table(Include 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 --Sentence level trigger, which is triggered once per line
BEGIN
 INSERT INTO OLD_EMPLOYEE VALUES (:OLD.ID, :OLD.NAME, :OLD.AGE, :OLD.SEX); --:old Represents old values
END;
/
--Test below
DELETE employee;
SELECT * FROM old_employee;

5) Create triggers to insert data using views

--Create Table
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
INSERT INTO tab1 VALUES(101,'zhao',22);
INSERT INTO tab1 VALUES(102,'yang',20);
INSERT INTO tab2 VALUES(101,'13761512841','AnHuiSuZhou');
INSERT INTO tab2 VALUES(102,'13563258514','AnHuiSuZhou');
--Create a view to join 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 views
INSERT INTO tab_view VALUES(106,'ljq','13886681288','beijing');
--query
SELECT * FROM tab_view;
SELECT * FROM tab1;
SELECT * FROM tab2;

6) Create triggers to 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('Wage 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('Wages before update:' || :OLD.SAL);
 DBMS_OUTPUT.PUT_LINE('Updated wages:' || :NEW.SAL);
END;
/
--implement UPDATE View effects
UPDATE emp SET sal = 3000 WHERE empno = '7788';

The results are as follows:

7) Create triggers to store operations CREATE, DROP in log_info table

--Create 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 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 DBA Identity logon 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;

 

Tags: Database SQL

Posted on Sat, 14 Sep 2019 20:29:39 -0700 by Jammerious