Oracle trigger 2-DML trigger

Oracle trigger 2-DML trigger DML trigger is the most common trigger type, developers use more, while other types of triggers are mainly used for database management or audit, while DBA uses more. 1. Introduction to DML triggers: BEFORE triggers are triggered before an operation occurs. For example, BEFORE insert is triggered before the insert operation. An AFTER trigger is triggered after an operation. For example, AFTER update is triggered before the insert operation. Statement-level triggers are triggered by the entire SQL statement. This SQL statement may operate on one or more data records in the database table. Row-level triggers are applicable to each row of records that operate 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. The pseudo record NEW is a data structure called NEW and looks very similar to the 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. The pseudo record OLD is a data structure called OLD, which looks very similar to the 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. The DML trigger in the WHEN clause uses this clause to determine whether the code of the trigger should be executed. We can use this clause to avoid unnecessary execution. The transaction DML trigger is involved in the transaction that triggers 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 dml trigger 1 CREATE [or replace] TRIGGER trigger_name -- specify a TRIGGER name, or replace optional 2 {BEFORE | AFTER} -- specify that the TRIGGER is 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 (...)] -- This optional when clause can avoid unnecessary execution of 6 [DECLARE...] 7 BEGIN 8 ...exe cutable statements... -- execution body 9 [EXCEPTION...] -- Optional Exception Handling Part 10 END [trigger_name]; Examples: -- an after statement level triggerCREATE 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 statements ('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 st Atement 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 a different value: 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;/WHEN clause Note:. the entire judgment logic expression should be included () B. do not add ":" c. WHEN using the WHEN clause, you can only use SQL built-in functions. 2.2. Use the 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 ;/