ORACLE trigger 2-dml trigger

DML triggers are the most common types of triggers, which are used by developers. Other types of triggers are mainly used for database management or audit, while DBA is usually used.

1. Introduction to DML triggers:

Before trigger

This trigger is triggered before an operation occurs. For example, before insert is triggered before the insert operation.

After trigger

This trigger is triggered after an operation. For example, after update is triggered before the insert operation.

Statement-level triggers

This trigger is triggered by the entire SQL statement. This SQL statement may operate on one or more data records in the database table.

Row-level triggers

This trigger is intended for each row of records operated during SQL statement execution. Assume that the books table contains 1000 rows of records. The following update statement modifies the 1000 rows of records:

Update books set Title = upper (title );

If I define a row-level update trigger on the Books table, this trigger will be triggered 1000 times.

Pseudo record new

This is a data structure called New and looks very similar to records in PL/SQL. This pseudo record can be used only in DML triggers for update and insert operations. This record contains the modified values of the operated rows.

False record old

This is a data structure called old, which looks very similar to records in PL/SQL. This pseudo record can be used only in DML triggers for update and insert operations. This record contains the values before the modified rows of the operation.

When clause

DML triggers use this clause to determine whether the trigger should be executedCodeWe can use it to avoid unnecessary execution.

Related Transactions

DML triggers are involved in the transactions that trigger them.

If the trigger throws an exception, this part of the transaction will be rolled back ).

If the trigger itself runs a DML Statement (such as inserting a row of data into a log table), this DML will also become part of the main transaction.

You cannot execute commit or rollback statements in DML triggers.

2. Create a DML trigger

1 Create [or replace] trigger trigger_name -- specify a trigger name, or replace is optional.
2 {before | after} -- specify that the trigger should be before or after the statement is executed.
3 {insert | Delete | update of column_list} on table_name -- specifies the DML type combination of the trigger application: insert, update, or delete operations.
4 [for each row] -- if for each row is specified, the trigger is activated for each row of records processed by the statement.
5 [When (...)] -- use this optional when clause to avoid unnecessary execution
6 [declare...]
7 begin
8 ...exe cutable statements... -- execution body
9 [exception...] -- Optional Exception Handling Section
10 end [trigger_name];

Examples:

-- An after statement Level Trigger

Create or replace trigger statement_trigger
After insert on to_table
Begin
Dbms_output.put_line ('after insert statement level ');
End;
/
/* -- An after row level trigger */
Create or replace trigger row_trigger
After insert on to_table
For each row
Begin
Dbms_output.put_line ('after insert row level ');
End;
/

-- A before statement Level Trigger
Create or replace trigger before_statement_trigger
Before insert on to_table
Begin
Dbms_output.put_line ('before insert statement level ');
End;
/

-- A before row Level Trigger
Create or replace trigger before_row_trigger
Before insert on to_table
For each row
Begin
Dbms_output.put_line ('before insert row level ');
End;
/

-- After insert statement
Create or replace trigger after_insert_statement
After insert on to_table
Begin
Dbms_output.put_line ('after insert statement ');
End;
/

-- After update statement
Create or replace trigger after_update_statement
After update on to_table
Begin
Dbms_output.put_line ('after update statement ');
End;
/

-- After Delete statement
Create or replace trigger after_delete_statement
After delete on to_table
Begin
Dbms_output.put_line ('after Delete statement ');
End;
/

2.1 use the when clause

For example, use the when clause to ensure that the trigger code is executed only when the salary is changed to different values:

Create or replace trigger check_raise
After update of salary
On employees
For each row
When (old. salary! = New. Salary)
Or (old. Salary is null and new. salary is not null)
Or (old. salary is not null and new. Salary is null ))
Begin
NULL;
End;
/

Note when using the when clause:

A. You need to include the entire judgment logical expression ()

B. Do not add ":" Before old and new ":"

C. Only SQL built-in functions can be used when the when clause is used;

2.2 use new and old pseudo records

Create or replace trigger bef_ins_ceo_comp
before insert
On ceo_compensation
for each row
declare
Pragma autonomous_transaction;
begin
insert into ceo_comp_history
values (
: New. name
,: Old. compensation
,: New. compensation
, 'after insert'
, sysdate
);

commit;
end;
/