• Oracle Triggers Overview
• Dml triggers
  
  • Pseudo recordings
  • Instead of dml triggers
  • Instead of triggers on Nested Table Columns of Views.
  • Compound DML triggers (compound DML triggers)
    
    • Composite trigger structure
  • Basic rules for determining DML triggers
  • Limitations of DML Triggers
  • Error mutation table ORA-04091
• System triggers (System triggers)
  
  • Schema level triggers
  • Database Triggers (database triggers)
  • Instead of create triggers
  • Attributes of system triggers
  • System Trigger Events
• Compiling triggers
• Trigger Exceptions
• The order of execution of triggers
• Enable / Disable Triggers
• Rights for operations with triggers
• Trigger Data Dictionaries

Trigger Overview

• You cannot trigger a trigger yourself, it always works only on a certain event automatically (if it is enabled)
• Do not create a recursive trigger. Ie, for example, the after update trigger, in which the update of the same table is executed. In this case, the trigger will fire recursively until the memory runs out.

• DML trigger (per table or view)
• System trigger (per schema or database)
• Conditional trigger (those that have a when clause)
• Instead of trigger (dml view trigger or system trigger create command)

• For automatic generation of virtual field values
• For logging
• To collect statistics
• To change the data in the tables, if the representation is involved in the dml operation
• To prevent dml operations at certain times
• To implement complex data integrity constraints that cannot be implemented through descriptive constraints set when creating tables
• For the organization of various types of audit
• For notifying other modules about what to do if the information in the database changes
• To implement business logic
• For organizing cascade effects on database tables
• To respond to system events in the database or schema

Dml triggers

• DML triggers are created for tables or views, triggered when inserting, updating, or deleting records.
• A trigger can be created in a different scheme than the one where the tables are defined. In this case, the current scheme when the trigger is executed is the scheme of the trigger itself.
• During the MERGE operation, triggers to modify, insert or delete records are triggered, depending on the operation with the string.
• A trigger is part of a transaction, an error in a trigger rolls back an operation, changes to tables in a trigger become part of a transaction.
• If a transaction is rolled back, the trigger changes are also rolled back.
• Triggers do not allow DDL and transaction management statements (exceptions are autonomous transactions).

• On the table
• On the submission (instead of trigger)

• Insert records (insert)
• Updating records (update)
• Deleting entries (delete)

• Statement level triggers
• Record Level (row level triggers)
• Compound triggers

• Before performing the operation (before)
• After the operation (after)

CREATE OR REPLACE TRIGGER t BEFORE INSERT OR UPDATE OF salary, department_id OR DELETE ON employees BEGIN CASE WHEN INSERTING THEN DBMS_OUTPUT.PUT_LINE('Inserting'); WHEN UPDATING('salary') THEN DBMS_OUTPUT.PUT_LINE('Updating salary'); WHEN UPDATING('department_id') THEN DBMS_OUTPUT.PUT_LINE('Updating department ID'); WHEN DELETING THEN DBMS_OUTPUT.PUT_LINE('Deleting'); END CASE; END; 

Pseudo recordings

• With pseudo-records, operations of the entire record level are prohibited (: new = null;)
• You cannot change the values ​​of the old record fields
• If the trigger fires on delete, you cannot change the field values ​​of new
• In the after trigger, you cannot change the field values ​​of new

Instead of dml triggers

• They are created for views (view) and serve to replace DML operations with their functionality.
• Enables insert / update or delete operations for non-updated views.

• This is always the trigger level record (row level)
• It has access to old and new pseudo-records, but cannot change them.
• Replaces dml operation with view

 CREATE OR REPLACE VIEW order_info AS SELECT c.customer_id, c.cust_last_name, c.cust_first_name, o.order_id, o.order_date, o.order_status FROM customers c, orders o WHERE c.customer_id = o.customer_id; CREATE OR REPLACE TRIGGER order_info_insert INSTEAD OF INSERT ON order_info DECLARE duplicate_info EXCEPTION; PRAGMA EXCEPTION_INIT (duplicate_info, -00001); BEGIN INSERT INTO customers (customer_id, cust_last_name, cust_first_name) VALUES ( :new.customer_id, :new.cust_last_name, :new.cust_first_name); INSERT INTO orders (order_id, order_date, customer_id) VALUES ( :new.order_id, :new.order_date, :new.customer_id); EXCEPTION WHEN duplicate_info THEN RAISE_APPLICATION_ERROR ( num=> -20107, msg=> 'Duplicate customer or order ID'); END order_info_insert; 

Instead of triggers on Nested Table Columns of Views

 -- Create type of nested table element: CREATE OR REPLACE TYPE nte AUTHID DEFINER IS OBJECT ( emp_id NUMBER(6), lastname VARCHAR2(25), job VARCHAR2(10), sal NUMBER(8,2) ); / -- Created type of nested table: CREATE OR REPLACE TYPE emp_list_ IS TABLE OF nte; / -- Create view: CREATE OR REPLACE VIEW dept_view AS SELECT d.department_id, d.department_name, CAST (MULTISET (SELECT e.employee_id, e.last_name, e.job_id, e.salary FROM employees e WHERE e.department_id = d.department_id ) AS emp_list_ ) emplist FROM departments d; -- Create trigger: CREATE OR REPLACE TRIGGER dept_emplist_tr INSTEAD OF INSERT ON NESTED TABLE emplist OF dept_view REFERENCING NEW AS Employee PARENT AS Department FOR EACH ROW BEGIN -- Insert on nested table translates to insert on base table: INSERT INTO employees ( employee_id, last_name, email, hire_date, job_id, salary, department_id ) VALUES ( :Employee.emp_id, -- employee_id :Employee.lastname, -- last_name :Employee.lastname || '@company.com', -- email SYSDATE, -- hire_date :Employee.job, -- job_id :Employee.sal, -- salary :Department.department_id -- department_id ); END; 

 INSERT INTO TABLE ( SELECT d.emplist FROM dept_view d WHERE department_id = 10 ) VALUES (1001, 'Glenn', 'AC_MGR', 10000); 

Compound DML triggers (compound DML triggers)

• Triggers such triggers at different events and at different points in time (at the level of the operator or line, when inserting / updating / deleting, before or after the event).
• Cannot be offline transactions.

• Collect rows for insertion into another table in order to intermittently insert them in batches.
• Avoid mutating-table error

Composite trigger structure

• Before statement
• After statement
• Before each row
• After each row

• Cannot access old, new or parent pseudo-records in expression level sections (before statement and after statement)
• You can only change the values ​​of the pseudo-record fields in the before each row section.
• Exceptions generated in one section cannot be processed in another section.
• If a goto statement is used, it must point to a code in the same section.

 create or replace trigger tr_table_test_compound for update or delete or insert on table_test compound trigger v_count pls_integer := 0; before statement is begin dbms_output.put_line ( 'before statement' ); end before statement; before each row is begin dbms_output.put_line ( 'before insert' ); end before each row; after each row is begin dbms_output.put_line ( 'after insert' ); v_count := v_count + 1; end after each row; after statement is begin dbms_output.put_line ( 'after statement' ); end after statement; end tr_table_test_compound; 

Basic rules for determining DML triggers

• Update of - allows you to specify a list of editable fields for triggering
• All conditions in the header and When ... are checked without trigger triggering at the stage of SQL execution.
• You can use only built-in functions in a When statement.
• You can make several triggers of the same type, the execution order is not defined by default, but it can be specified using the FOLLOWS TRIGGER_FIRST construct
• Uniqueness constraints are checked when the record is changed, that is, after the execution of the triggers before
• The variable declaration section is defined by the word DECLARE
• The main trigger block follows the same rules as regular PL / SQL blocks.

Limitations of DML Triggers

• DDL statements cannot be executed (only in an autonomous transaction)
• cannot run subroutines with transaction control statements
• does not have access to SERIALLY_REUSABLE packages
• size can not exceed 32K
• variables of type LONG and LONG RAW cannot be declared

Error mutation table ORA-04091

• use operation level triggers
• autonomous transaction in trigger
• use third-party structures (package level collections)
• use COMPOUND TRIGGER
• change of the algorithm itself with the removal of the functional from the trigger

System triggers (System triggers)

• Before the operation is performed (which triggers the trigger)
• After the operation is performed (which triggers the trigger)
• Instead of the Create statement

Schema level triggers

• It is always triggered when the user who owns the schema triggers an event (performs an operation) on which the trigger should fire.
• In case any other user starts a procedure / function that is called with the creator's rights, and in this procedure / function, the operation for which the system trigger is created is performed - this trigger will trigger.

 CREATE OR REPLACE TRIGGER drop_trigger BEFORE DROP ON hr.SCHEMA BEGIN RAISE_APPLICATION_ERROR ( num => -20000, msg => 'Cannot drop object'); END; 

Database Triggers (database triggers)

• Such a trigger is triggered when any database user executes the command on which the trigger is created.

 CREATE OR REPLACE TRIGGER check_user AFTER LOGON ON DATABASE BEGIN check_user; EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR (-20000, 'Unexpected error: '|| DBMS_Utility.Format_Error_Stack); END; 

Instead of create triggers

• This is a schema level trigger that responds to the create command and replaces this command with itself (that is, instead of executing the create command, the body of the trigger is executed).

 CREATE OR REPLACE TRIGGER t INSTEAD OF CREATE ON SCHEMA BEGIN EXECUTE IMMEDIATE 'CREATE TABLE T (n NUMBER, m NUMBER)'; END; 

Attributes of system triggers

 IF (ora_sysevent = 'LOGON') THEN v_addr := ora_client_ip_address; END IF; 

 v_db_name := ora_database_name; 

 IF (ora_dict_obj_type = 'USER') THEN INSERT INTO event_table VALUES (ora_des_encrypted_password); END IF; 

 INSERT INTO event_table VALUES ('Changed object is ' || ora_dict_obj_name); 

 IF (ora_sysevent='ASSOCIATE STATISTICS') THEN number_modified := ora_dict_obj_name_list(name_list); END IF; 

 INSERT INTO event_table VALUES ('object owner is' || ora_dict_obj_owner); 

 IF (ora_sysevent='ASSOCIATE STATISTICS') THEN number_modified := ora_dict_obj_name_list(owner_list); END IF; 

 INSERT INTO event_table VALUES ('This object is a ' || ora_dict_obj_type); 

 IF (ora_sysevent = 'GRANT') THEN number_of_grantees := ora_grantee(user_list); END IF; 

 IF (ora_instance_num = 1) THEN INSERT INTO event_table VALUES ('1'); END IF; 

 IF (ora_sysevent = 'ALTER' AND ora_dict_obj_type = 'TABLE') THEN alter_column := ora_is_alter_column('C'); END IF; 

 IF (ora_sysevent = 'CREATE' AND ora_dict_obj_type = 'TABLE' AND ora_is_creating_nested_table) THEN INSERT INTO event_table VALUES ('A nested table is created'); END IF; 

 IF (ora_sysevent = 'ALTER' AND ora_dict_obj_type = 'TABLE') THEN drop_column := ora_is_drop_column('C'); END IF; 

 IF ora_is_servererror(error_number) THEN INSERT INTO event_table VALUES ('Server error!!'); END IF; 

 SELECT ora_login_user FROM DUAL; 

 -- Retrieve ora_sql_txt into sql_text variable v_n := ora_partition_pos; v_new_stmt := SUBSTR(sql_text,1,v_n - 1) || ' ' || my_partition_clause || ' ' || SUBSTR(sql_text, v_n)); 

 IF (ora_sysevent = 'GRANT' OR ora_sysevent = 'REVOKE') THEN number_of_privileges := ora_privilege_list(privilege_list); END IF; 

 IF (ora_sysevent = 'REVOKE') THEN number_of_users := ora_revokee(user_list); END IF; 

 INSERT INTO event_table VALUES ('top stack error ' || ora_server_error(1)); 

 n := ora_server_error_depth; -- Use n with functions such as ora_server_error 

 INSERT INTO event_table VALUES ('top stack error message' || ora_server_error_msg(1)); 

 n := ora_server_error_num_params(1); 

 -- Second %s in "Expected %s, found %s": param := ora_server_error_param(1,2); 

 CREATE TABLE event_table (col VARCHAR2(2030)); DECLARE sql_text ora_name_list_t; n PLS_INTEGER; v_stmt VARCHAR2(2000); BEGIN n := ora_sql_txt(sql_text); FOR i IN 1..n LOOP v_stmt := v_stmt || sql_text(i); END LOOP; INSERT INTO event_table VALUES ('text of triggering statement: ' || v_stmt); END; 

 INSERT INTO event_table VALUES (ora_sysevent); 

 IF (ora_sysevent = 'GRANT' AND ora_with_grant_option = TRUE) THEN INSERT INTO event_table VALUES ('with grant option'); END IF; 

 IF (ora_space_error_info ( eno,typ,owner,ts,obj,subobj) = TRUE) THEN DBMS_OUTPUT.PUT_LINE('The object '|| obj || ' owned by ' || owner || ' has run out of space.'); END IF; 

System Trigger Events

Compiling triggers

• The trigger was created in the disabled state (or transferred to this state)
• Trigger event after startup on database
• The trigger event after logon on database or after logon on schema and an attempt is made to login under the user System

Trigger Exceptions

• If the trigger event is after startup on database or before shutdown on database
• If the event is a trigger after logon on database and the user has the privilege of administer database trigger
• If the trigger event is after logon on schema and the user either owns the schema or has the privilege of alter any trigger

The order of execution of triggers

1. First, all the statement before the trigger is executed.
2. Then all before each row of the trigger
3. After all after each row of the trigger
4. And at the end all after statement trigger

Enable / Disable Triggers

 ALTER TRIGGER [schema.]trigger_name { ENABLE | DISABLE }; 

 ALTER TABLE table_name { ENABLE | DISABLE } ALL TRIGGERS; 

 alter trigger TRIGGER_NAME compile; 

Rights for operations with triggers

 grant create trigger to USER; 

 grant create any trigger to USER; grant alter any trigger to USER; grant drop any trigger to USER; 

 grant ADMINISTER DATABASE TRIGGER to USER; 

Triggers data dictionaries:

• dba_triggers - information about triggers
• dba_source - body code of the trigger
• dba_objects - trigger validity

Other Oracle Articles