Examples of Oracle Trigger Usage

Brief introduction of flip-flop

The definition of a trigger means that when a condition holds, the statement defined in the trigger will be automatically executed.
Therefore, triggers do not need to be called artificially, nor can they be called.
Then, the trigger condition of the trigger is actually set when you define it.
It needs to be explained that triggers can be divided into statement-level triggers and row-level triggers.
Simply put, statement-level triggers can be triggered before or after execution of certain statements. The row-level trigger is triggered once when the row data in the table defined to trigger changes.
Specific examples:
1. A statement-level trigger defined in a table. When the table is deleted, the program automatically executes the operation defined in the trigger. This is the operation of deleting the table, which is the condition for the trigger to execute.

2. A row-level trigger is defined in a table. When the row data in this table changes, such as deleting a row record, the trigger will be automatically executed.

Types of flip-flops

According to the different statements created by triggers and the objects affected, triggers are divided into the following three categories(
1) DML flip-flop
Triggers triggered by DML statement operations (such as insert, update, delete) on data tables can be divided into: statement-level triggers or row-level triggers: row-level triggers trigger the trigger code once for each affected row in the database table, and statement-level triggers trigger only once, which is affected by statements. Line-independent before trigger or after trigger: before trigger executes trigger code before trigger event occurs, after trigger executes after trigger event occurs

2) Alternative flip-flop (instead of flip-flop)
The grammar of triggers:
Flip-flops defined when operating on views, instead of flip-flops, can only be defined on views

Syntax: create [or replace] trigger trigger_name -- trigger name
 instead of trigger_event -- trigger event
 on view_name -- view name f
 or each row -- Substitute triggers must be specified as row-level triggers
 [when trigger_condition] -- trigger condition
 trigger_body -- trigger body, PL/SQL block

3) System Event Trigger
The triggers defined when operating on a database instance or a user mode can be divided into database system triggers and user triggers.

Trigger Grammar

Among them:
Trigger name: The name of the trigger object. Because the trigger is automatically executed by the database, the name is only a name and has no real purpose.
Trigger time: Indicates when the trigger is executed. This value is desirable:
Before: indicates that the trigger executes before the database action;
After: Represents the trigger execution after the 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 where the database trigger is located.
For each row: Execute once for each row trigger of the table. If this option is not available, the entire table is executed only once.
The flip-flop can achieve the following functions:
Functions:
1. Allow/Restrict Modifications to Tables
2. Automatically generating derived columns, such as self-increasing fields
3. Force data consistency
4. Providing audit and log records
5. Preventing Invalid Transaction Processing
6. Enabling complex business logic
1) The following triggers are triggered before updating the table tb_emp, with the aim of not allowing the table to be modified over the weekend:

create or replace trigger t_secure 
before insert or update or delete
on emp
begin
  IF(to_char(sysdate,'DAY')='Sunday') THEN
    RAISE_APPLICATION_ERROR(-20600,'You can't modify the table on weekends. emp');
  END IF;
END;

2) Use trigger to realize serial number self-increasing

--Create Sequences
create sequence sq_deptno
start with 1--Beginning value
increment by 1
maxvalue 2000
nocycle --Whether or not to cycle--Partial circulation
cache 10--Pre-caching 10
--Create test tables
create table mylog(
    uid number(4),
    creatdate date,
    action varchar2(10) 
)
--Create triggers
create or replace trigger t_pk
before insert on mylog
for each row
  begin
   select sq_deptno.nextval into :new.deptno from dual;
  end; 

3) When the user executes DML statement on emp table, the relevant information is recorded in log table

create or replace trigger t_log
after
 insert or update or delete 
 on emp
 begin
   if inserting then 
     insert into mylog values(sysdate,'add');
   elsif updating then
     insert into mylog values(sysdate,'modify');
   else
    insert into mylog values(sysdate,'delete');
    end if;
 end;

5) Create triggers to insert data using views

--Create tables
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 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 triggers
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;