1, Trigger definition

Oracle trigger is a segment of PL/SQL program code that is triggered when the user makes a specific operation on the object of Oracle database. Triggered events include DML operations on tables, DDL operations of users, database events, etc.

2, Trigger classification

Triggers are divided into statement level triggers and row level triggers.

• Statement level trigger: triggered before or after the execution of some statements.
• Row level trigger: it will be triggered once when the row data in the table with trigger defined changes.

According to the user's specific operation event type, it can be divided into five types of triggers.

• Data operation (DML) trigger: this trigger is defined on the Oracle table. It can be triggered when performing insert, update and delete operations on the table. If the row level data in the table is triggered or triggered at the statement level, it can be divided into row level triggers. Statement level triggers can be divided into after triggers and before triggers according to the trigger before and after data modification.

• Data definition operation (DDL) trigger: when creating, alter and drop operations are performed on database objects, trigger triggers to save operation records or limit operations.

• User and system event trigger: this type of trigger is used on Oracle database system. When database events are carried out, trigger is triggered. It is generally used to record login related information.

• INSTEAD OF trigger: this type of trigger acts on the view. When the user operates on the view, it triggers the trigger to convert the relevant operation into operation on the table.

• Composite trigger: refers to the composite of multiple types of data operation (DML) triggers, such as; A trigger contains after (or before) row level trigger and after (or before) statement level trigger to complete some more complex operations.

3, Trigger function

Oracle trigger can call specific trigger program blocks according to different database events. Therefore, it can help developers solve some problems that PL/SQL stored procedures cannot complete, such as:

• Allow or restrict modifications to tables
  Automatically generate derived columns, such as self incrementing fields (sequences)
• Enforce data consistency
• Provide audit and logging
• Prevent invalid transactions
• Enable complex business logic

However, it is not recommended to write business logic programs in triggers, because this will greatly increase the cost of later data maintenance.

4, Trigger syntax

create [or replace] trigger Trigger name trigger time trigger event
on Table name
[for each row]
begin
  PL/SQL Statement block
end;

Syntax explanation:
Trigger name: the name of the trigger object, which has no practical use.
Trigger time: specifies when the trigger is executed. There are two values: before and after.
Before: Execute Trigger before database action
After: Execute Trigger after database action
Trigger event: indicates which database actions will trigger the trigger
This trigger is triggered when the insert database is inserted
update this trigger is triggered when the database is updated
This trigger is triggered when the delete database is deleted
Table name: the table where the database trigger is located
For each row: limit the execution range of the trigger. The trigger is executed once for each row of the table. If this option is not available, it is executed once for the whole table

5, Trigger use cases

Case 1: insert a piece of data into the job1 table and output the welcome statement

--Preparation: copy jobs Table as job11 Table, case 1 and case 2 are in job1 Execute in table.
CREATE TABLE JOB1 AS SELECT * FROM HR.JOBS;
--Create trigger
create or replace trigger trigger1 after insert
on job1
begin
  dbms_output.put_line('I wish you an early salary increase!');
end;

After the trigger is created, a row of data can be inserted into the job1 table to verify the effect.

--Insert data to trigger the execution of the trigger
insert into job1 values('ruirui','Prisident',15000,30000);

The effect is as follows:

Case 2: data verification. It is not allowed to insert / update data into emp1 table on Tuesday and Thursday.

create or replace trigger trigger2 before insert or update 
on job1
declare
  v_day varchar2(20);
begin
  ---Judge whether today is Tuesday or Thursday
  select to_char(sysdate,'day') into v_day from dual;
  ---judge
  if v_day = 'Tuesday' then
    dbms_output.put_line('Today is Tuesday. You can't insert it/Update data!');
    raise_application_error(-20001,'Today is Tuesday. You can't insert it/Update data!');
  if v_day = 'Thursday' then
    dbms_output.put_line('Today is Thursday. You can't insert it/Update data!');
    raise_application_error(-20001,'Today is Thursday. You can't insert it/Update data!');
  end if;
  end if;
end;

After the trigger is created, a row of data can be inserted into the job1 table to verify the effect.

--Insert data to trigger the execution of the trigger
insert into job1 values('rui1','Prisident',15000,30000);

The effect is as follows:


Case 3: create a trigger to record the deletion data of the table

--establish job1_log Table for recording job1 Delete record of table
create table job1_log  as select * from job1 where  1<>1;

--Create trigger
create or replace trigger trigger3 after delete 
on job1
for each row
begin
  insert into job1_log values(:old.job_id,:old.job_title,:old.min_salary,:old.max_salary);
  dbms_output.put_line('The record has been successfully deleted and recorded in the log');
end;

After the trigger is created, a row of data in the job1 table can be deleted to verify the effect.

--test
delete from job1 where job_id='ruirui';

The effects are as follows:

Case 4: create a trigger to record the updated data of the table

--Create logging table
create table test_log(
  l_user varchar2(15),
  l_type varchar2(15),
  l_date varchar2(20)
);

--Create trigger
create or replace trigger trigger4 after delete or insert or update
on job1
declare
  v_type test_log.l_type%type;
begin
  if deleting then
    v_type := 'delete';
    dbms_output.put_line('The record has been successfully deleted and recorded in the log');
  elsif inserting then
    v_type := 'insert';
    dbms_output.put_line('The record has been successfully inserted and recorded in the log');
  elsif updating then
    v_type := 'update';
    dbms_output.put_line('The record has been successfully updated and logged');
  end if;
  insert into test_log values(user,v_type,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'));
end;

After the trigger is created, the data validation effect in the job1 table can be updated.

--test
insert into job1 values('rui1','Prisident',15000,30000);

update job1 set min_salary=20000 where job_id='rui1';

delete from job1 where job_id='rui1';

Query test_log table is recorded as follows:

select * from test_log;

Case 5: create a trigger and insert a piece of data in the record table before deleting it

--establish job1_log Table for recording job1 Delete record of table
create table test1_log  as select * from job1 where  1<>1;

--Create trigger
create or replace trigger trigger5 before delete on job1
for each row --row-level trigger 
begin
  insert into test1_log values(:old.job_id,:old.job_title,:old.min_salary,:old.max_salary);
end;

After the trigger is created, the data validation effect in the job1 table can be updated.

--test
delete from job1 where job_id='rui2';
commit;

Query test1_log table is recorded as follows:

select * from test01_log;

reference resources: Oracle database trigger