DML triggers:
- Simple
- Compound
- can fire at one, some, or all of the preceding timing points
INSTEAD OF- Created on a noneditioning view, or on a nested table column of a noneditioning view.
- Used to perform a DML action on the underlying tables of a view.
- The database fires the
INSTEAD OF trigger instead of running the triggering DML statement.
- Crossedition
- For use only in edition-based redefinition
- Triggering event:
DELETE, INSERT, and UPDATE.
- Simple DML trigger fires at exactly one of these timing points:
- Before the triggering statement runs (
BEFORE trigger).
- After the triggering statement runs (
AFTER trigger).
- Statement triggers are always executed, independently of whether the triggering event actually affected any rows in the underlying table.
- Statement-level triggers are also known as table-level triggers.
- You cannot use a WHEN clause in a statement-level trigger.
- You also cannot reference the new or old pseudo-records in a statement-level trigger. Doing so results in a compile-time error (ORA-04082: NEW or OLD references not allowed in table level triggers).
- Before each row that the triggering statement affects (
BEFORE each row trigger).
- After each row that the triggering statement affects (
AFTER each row trigger).
- If the DML statement does not affect any row the trigger is NOT executed.
DML Triggers and DCL (Commit, Rollback, Savepoint)
- You cannot use SQL Data Control Language (DCL) in them, unless you declare the trigger as autonomous.
- When triggers run inside the scope of a transaction, they disallow setting a SAVEPOINT or performing either a ROLLBACK or COMMIT statement.
- Also, No function or procedure called by a trigger can have a DCL statement in their execution path.
DML Trigger: Syntax
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER}
{INSERT | UPDATE | UPDATE OF column1 [, column2 [, column(n+1)]] | DELETE}
ON table_name
[FOR EACH ROW]
[WHEN (logical_expression)]
[DECLARE]
[PRAGMA AUTONOMOUS_TRANSACTION;]
declaration_statements;
BEGIN
execution_statements;
END [trigger_name];
/
| Trigger firing sequence for any DML (I/U/D) |
- Before DML FOR EACH STATEMENT.
- Before DML FOR EACH ROW.
- DML statement executed.
- After DML FOR EACH ROW.
- After DML FOR EACH STATEMENT.
|
triggers can you have for
INSERT on Employees?
- No restrictions. You can create many for a single event.
- However, the ORDER of firing is not guaranteed.
- Consolidate triggers, if sequence in important.
| Before UPDATE Statement-level Triggers |
- Useful to ensure integrity rules (i.e. new value should be within some range) or business requirements (i.e. ensure that user has required privileges for operation).
- Can be also used to check whether the triggering event should be allowed to complete successfully. If the statement is found to be not authorized, the trigger could, for example, log it on an audit table, send notification messages, and raise an exception so that the statement fails.
SQL> CREATE or REPLACE TRIGGER audit_emp_hours
BEFORE INSERT OR UPDATE OR DELETE
ON employees
BEGIN
IF TO_CHAR (SYSDATE, 'HH24:MI') NOT BETWEEN '08:00' AND '18:00'
OR TO_CHAR (SYSDATE, 'DY') IN ('SAT', 'SUN') THEN
RAISE_APPLICATION_ERROR (-20205,
'You may only make changes during normal office hours');
END IF;
END;
/
Trigger Created.
SQL>
SQL> update employees set salary=43000 where employee_id=210;
update employees set salary=43000 where employee_id=210
*
ERROR at line 1:
ORA-20205: You may only make changes during normal office hours
ORA-06512: at "HR.AUDIT_EMP_HOURS", line 4
ORA-04088: error during execution of trigger 'HR.AUDIT_EMP_HOURS'
| Using CONDITIONAL PREDICATES to audit actions/events: |
INSERINGUPDATINGDELETING
| { TRUE | FALSE } |
The trigger below writes into an
audit table (
AUDITBL) when any
DML statement is executed on the
EMPLOYEES table.
(a) Create the Audit table (
AUDITBL)
SQL> CREATE TABLE AudiTbl (
id_audit NUMBER NOT NULL ,
username VARCHAR2 (20) ,
action VARCHAR2 (400) ,
dt_action DATE ,
table_name VARCHAR2 (100) ,
old_value VARCHAR2 (400) ,
new_value VARCHAR2 (400)
);
Table created.
SQL>ALTER TABLE AudiTbl
ADD CONSTRAINT "AudiTblPK" PRIMARY KEY (id_audit);
Table altered.
SQL> CREATE SEQUENCE seq_audit START WITH 1;
Sequence created.
SQL> (b) Create Conditional
AFTER UPDATE/INSERT/DELETE Trigger on (
EMPLOYEES).
SQL> Set Serveroutput on;
SQL> CCREATE OR REPLACE TRIGGER audit_employees
AFTER
INSERT OR
UPDATE OF salary OR
UPDATE OF department_id OR
DELETE
ON employees
FOR EACH ROW
BEGIN
CASE
WHEN INSERTING THEN
INSERT into AudiTbl VALUES
(seq_audit.nextval, user, 'INSERT', sysdate, 'employees', null, null);
DBMS_OUTPUT.PUT_LINE('Inserting on employees');
WHEN UPDATING('salary') THEN
DBMS_OUTPUT.PUT_LINE('Updating salary');
INSERT into AudiTbl VALUES
(seq_audit.nextval, user, 'UPDATE salary', sysdate, 'employees',
:OLD.salary, :NEW.salary);
WHEN UPDATING('department_id') THEN
DBMS_OUTPUT.PUT_LINE('Updating department ID');
INSERT into AudiTbl VALUES
(seq_audit.nextval, user, 'UPDATE Dept_id', sysdate, 'employees',
:OLD.department_id, :NEW.department_id);
WHEN DELETING THEN
DBMS_OUTPUT.PUT_LINE('Deleting');
INSERT into AudiTbl VALUES
(seq_audit.nextval, user, 'DELETE', sysdate, 'employees',
:OLD.employee_id, null);
END CASE;
END;
/Notes:
- OLD, NEW and PARENT are correlation names. :OLD and :NEW qualifiers can only be used in row level triggers. These reference the old and new values of a column, respectively.
(c) Test the INSERT trigger on (
EMPLOYEES).
SQL> insert into employees (employee_id, first_name, last_name, email,
phone_number, hire_date, job_id, salary, commission_pct,
manager_id, department_id)
values (employees_seq.nextval, 'John', 'Fergunson', 'email@email.com',
null, sysdate, 'IT_PROG', 20000, null, null, null);
Inserting on employees
1 row created.
SQL>
SQL> Select * from auditbl;
| ID_AUDIT | USERNAME | ACTION | DT_ACTION | TABLE_NAME | OLD_VALUE | NEW_VALUE |
| 1 | JOHNF | INSERT | 18-AUG-10 | employees | | |
(d) Test the UPDATE (salary) trigger
SQL> update employees set salary=salary*2 where employee_id=210;
Updating salary
1 row updated.
SQL>
SQL> Select * from auditbl;
| ID_AUDIT | USERNAME | ACTION | DT_ACTION | TABLE_NAME | OLD_VALUE | NEW_VALUE |
| 1 | JOHNF | INSERT | 18-AUG-10 | employees | | |
| 2 | MARCJ | UPDATE | 11-Sep-10 | employees | 15000 | 30000 |
 | Row-Level Update Trigger |
- The trigger below is fired when an employee is assigned to the department 10.
- It fires AFTER an UPDATE on emploees, but ONLY WHEN the update was on the department_id COLUMN AND the new value for that column is set to 10.
- Note that in the WHEN clause you use NEW. In the trigger bodyyou use :NEW.
- Inside a trigger body, you preface the pseudo-records with a colon (:). The colon let you reference the externally scoped pseudo-records in the trigger body.
SQL> CREATE OR REPLACE TRIGGER audit_dept10
AFTER UPDATE OF department_id
ON employees
FOR EACH ROW
WHEN (new.department_id = 10)
BEGIN
DBMS_OUTPUT.PUT_LINE('Update on employees');
INSERT into AudiTbl VALUES
(seq_audit.nextval, user, 'UPDATE', sysdate, 'employees',
:old.department_id, :new.department_id);
END;
/
Trigger created.
SQL> set serveroutput on
SQL> update employees
set department_id = 10
where employee_id in (201, 202, 203, 210);
4 rows updated.
Update on employees
Update on employees
Update on employees
Update on employees
SQL> select * from auditbl;
| ID_AUDIT | USERNAME | ACTION | DT_ACTION | TABLE_NAME | OLD_VALUE | NEW_VALUE |
| 18 | JAFFN | UPDATE | 26-AUG-11 | employees | 70 | 10 |
| 18 | JAFFN | UPDATE | 26-AUG-11 | employees | 20 | 10 |
| 18 | JAFFN | UPDATE | 26-AUG-11 | employees | 20 | 10 |
| 18 | JAFFN | UPDATE | 26-AUG-11 | employees | 40 | 10 |
