Which has the higher priority in your organization: Deploying a new database or securing the ones you already have?
Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Data Dictionary Objects
ALL_TRIGGERS
CDB_TRIGGER_ORDERING
TRIGGER$
ALL_TRIGGER_COLS
DBA_TRIGGERS
USER_TRIGGERS
ALL_TRIGGER_ORDERING
DBA_TRIGGER_COLS
USER_TRIGGER_COLS
CDB_TRIGGERS
DBA_TRIGGER_ORDERING
USER_TRIGGER_ORDERING
CDB_TRIGGER_COLS
System Privileges
ADMINISTER DATABASE TRIGGER
CREATE ANY TRIGGER
DROP ANY TRIGGER
ALTER ANY TRIGGER
CREATE TRIGGER
Trigger Firing Options
Before Constraints Are Applied
After Constraints Are Applied
BEFORE DELETE
AFTER DELETE
BEFORE INSERT
AFTER INSERT
BEFORE UPDATE
AFTER UPDATE
Maximum trigger size
32K - but you can call procedures and function in triggers to perform processing
Transaction Model
Oracle transactions are atomic. No commit or rollback are allowed in a trigger.
Create Statement Level Triggers (the default)
Statement Level Trigger With A Single Action
CREATE OR REPLACE [<EDITIONABLE | NONEDITIONABLE>]
TRIGGER <trigger_name>
[SHARING = <METADATA | NONE>]
[DEFAULT COLLATION <USING_NLS_COMP>]
<BEFORE | AFTER> <ACTION> [OR <ACTION> OR <ACTION>]
ON <table_name>
[FOLLOWS <schema.trigger_name>]
[<ENABLE | DISABLE>]
DECLARE
<variable definitions>
BEGIN
<trigger_code>
EXCEPTION
<exception clauses>
END <trigger_name>;
/
CREATE OR REPLACE TRIGGER statement_level
BEFORE UPDATE
ON orders
DECLARE
vMsg VARCHAR2(30) := 'Statement Level Trigger Fired';
BEGIN
dbms_output.put_line(vMsg);
END statement_level;
/
set serveroutput on
INSERT INTO orders (somecolumn) VALUES ('ABC');
UPDATE orders SET somecolumn = 'XYZ';
Best practice when creating new triggers in a production database is to create them in a disabled state.
By doing this there is no chance that an error in the trigger will cause DML on the table to fail.
An excellent tip delivered at UKOUG 2009 by Connor McDonald
CREATE OR REPLACE TRIGGER statement_level
BEFORE UPDATE
ON orders
DISABLE
DECLARE
vMsg VARCHAR2(30) := 'Statement Level Trigger Fired';
BEGIN
dbms_output.put_line(vMsg);
END statement_level;
/
SELECT trigger_name, status
FROM user_triggers;
ALTER TRIGGER statement_level ENABLE;
SELECT trigger_name, status
FROM user_triggers;
Statement Level Trigger With Multiple Actions
CREATE OR REPLACE TRIGGER statement_level
AFTER INSERT OR UPDATE OR DELETE
ON orders
DECLARE
vMsg VARCHAR2(30) := 'Statement Level Trigger Fired';
BEGIN
IF INSERTING THEN
dbms_output.put_line(vMsg || ' When Inserting');
ELSIF UPDATING('NUMBERCOL') THEN
dbms_output.put_line(vMsg || ' Updating Numbercol');
ELSIF UPDATING THEN
dbms_output.put_line(vMsg || ' When Updating');
ELSIF DELETING THEN
dbms_output.put_line(vMsg || ' When Deleting');
END IF;
END statement_level;
/
set serveroutput on
INSERT INTO orders (somecolumn) VALUES ('ABC');
UPDATE orders SET somecolumn = 'DEF' WHERE ROWNUM = 1;
UPDATE orders SET numbercolumn = 9 WHERE ROWNUM = 1;
DELETE FROM orders WHERE ROWNUM = 1;
Create Row Level Triggers
Note: AFTER row triggers create less UNDO than BEFORE row triggers so use AFTER when the distinction will not affect business rules
Row Level Trigger ... most common usage to provide a surrogate key from a sequence
CREATE OR REPLACE [<EDITIONABLE | NONEDITIONABLE>]
TRIGGER <trigger_name>
[FOLLOWS <schema.trigger_name>]
[<ENABLE | DISABLE>]
[WHEN (<condition>)]
<BEFORE | AFTER> <ACTION> OR <ACTION> OR <ACTION>
[OF <column_name_list>]
ON <table_name>
REFERENCING NEW AS <synonym> OLD AS <synonym> PARENT AS <synonym>
FOR EACH ROW
DECLARE
<variable definitions>
BEGIN
<trigger_code>
EXCEPTION
<exception clauses>
END <trigger_name>;
/
CREATE TABLE t (
rid NUMBER(5),
col VARCHAR2(3));
ALTER TABLE t
ADD CONSTRAINT pk_t
PRIMARY KEY (rid)
USING INDEX;
CREATE SEQUENCE seq_t;
CREATE OR REPLACE TRIGGER row_level
BEFORE INSERT
ON t
FOR EACH ROW
BEGIN
SELECT seq_t.NEXTVAL
INTO :NEW.rid
FROM dual;
dbms_output.put_line(:NEW.rid);
END row_level;
/
INSERT INTO t (col) VALUES ('A');
INSERT INTO t (col) VALUES ('B');
INSERT INTO t (col) VALUES ('C');
SELECT * FROM t;
Row Level Trigger With A Single Action
CREATE OR REPLACE TRIGGER row_level
BEFORE UPDATE
ON orders
FOR EACH ROW
DECLARE
vMsg VARCHAR2(30) := 'Row Level Trigger Fired';
BEGIN
dbms_output.put_line(vMsg);
END row_level;
/
set serveroutput on
INSERT INTO orders (somecolumn) VALUES ('ABC');
INSERT INTO orders (somecolumn) VALUES ('ABC');
INSERT INTO orders (somecolumn) VALUES ('ABC');
SELECT * FROM orders;
UPDATE orders SET somecolumn = 'XYZ';
Row Level Trigger With Multiple Actions
CREATE OR REPLACE TRIGGER row_level
AFTER INSERT OR UPDATE OR DELETE
ON orders
FOR EACH ROW
DECLARE
vMsg VARCHAR2(30) := 'Row Level Trigger Fired';
BEGIN
IF INSERTING THEN
dbms_output.put_line(vMsg || ' On Insert');
ELSIF UPDATING THEN
dbms_output.put_line(vMsg || ' On Update');
ELSIF DELETING THEN
dbms_output.put_line(vMsg || ' On Delete');
END IF;
END row_level;
/
set serveroutput on
INSERT INTO orders (somecolumn) VALUES ('ABC');
UPDATE orders
SET somecolumn = 'ZZT';
DELETE FROM orders WHERE rownum < 4;
Row Level Trigger With WHEN Clause
INSERT INTO orders (somecolumn) VALUES ('ABC');
UPDATE orders SET numbercol = 0, datecol = SYSDATE;
UPDATE orders SET numbercol = 1 WHERE somecolumn = 'ZZT' AND rownum = 1;
SELECT * FROM orders;
CREATE OR REPLACE TRIGGER when_clause
BEFORE UPDATE
ON orders
FOR EACH ROW
WHEN (NEW.numbercol <> OLD.numbercol AND NEW.numbercol < 5)
BEGIN
dbms_output.put_line('Fired with value: ' || :NEW.numbercol);
END when_clause;
/
set serveroutput on
SELECT * FROM orders;
UPDATE orders
SET numbercol = 8;
UPDATE orders
SET numbercol = 4;
Row Level Trigger With OF Clause
CREATE OR REPLACE TRIGGER of_clause
BEFORE UPDATE
OF numbercol
ON orders
FOR EACH ROW
DECLARE
vMsg VARCHAR2(40) := 'Update Will Change numbercol Column';
BEGIN
dbms_output.put_line(vMsg);
END of_clause;
/
set serveroutput on
UPDATE orders
SET numbercol = 8;
Row Level Trigger With REFERENCING Clause
CREATE TABLE person (
fname VARCHAR2(15),
lname VARCHAR2(15));
CREATE OR REPLACE TRIGGER referencing_clause
AFTER UPDATE
ON person
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
INSERT INTO audit_log
(o_fname, o_lname, n_fname, n_lname, chng_by, chng_when)
VALUES
(:OLD.fname, :OLD.lname, :NEW.fname, :NEW.lname, USER, SYSDATE);
END referencing_clause;
/
INSERT INTO person (fname, lname) VALUES ('Dan', 'Morgan');
SELECT * FROM person;
SELECT * FROM audit_log;
UPDATE person
SET lname = 'Dangerous';
SELECT * FROM person;
SELECT * FROM audit_log;
UPDATE person
SET fname = 'Mark', lname = 'Townsend';
Compound triggers allow for writing a single trigger incorporating STATEMENT and ROW LEVEL and BEFORE and AFTER
CREATE TRIGGER <trigger_name>
FOR <triggering_event> ON <table_name>
COMPOUND TRIGGER
BEFORE STATEMENT IS
BEGIN
...
END BEFORE STATEMENT;
BEFORE EACH ROW IS
BEGIN
...
END BEFORE EACH ROW;
AFTER STATEMENT IS
BEGIN
...
END AFTER STATEMENT;
AFTER EACH ROW IS
BEGIN
...
END AFTER EACH ROW;
END compound_trigger;
/
CREATE TABLE test AS
SELECT table_name, tablespace_name
FROM user_all_tables;
set serveroutput on
CREATE OR REPLACE TRIGGER compound_trig
FOR INSERT ON test
COMPOUND TRIGGER
-------------------------------
BEFORE STATEMENT IS
BEGIN
dbms_output.put_line('BEFORE STATEMENT LEVEL');
END BEFORE STATEMENT;
-------------------------------
BEFORE EACH ROW IS
BEGIN
dbms_output.put_line('BEFORE ROW LEVEL');
END BEFORE EACH ROW;
-------------------------------
AFTER STATEMENT IS
BEGIN
dbms_output.put_line('AFTER STATEMENT LEVEL');
END AFTER STATEMENT;
-------------------------------
AFTER EACH ROW IS
BEGIN
dbms_output.put_line('AFTER ROW LEVEL');
END AFTER EACH ROW;
END compound_trig;
/
SELECT trigger_name, trigger_type
FROM user_triggers;
INSERT INTO test
(table_name, tablespace_name)
VALUES
('MORGAN', 'UWDATA');
Altering Triggers (all types)
Disable A Single Trigger
ALTER TRIGGER <trigger_name> DISABLE;
CREATE TABLE t (
testcol VARCHAR2(20));
CREATE OR REPLACE TRIGGER bi_t
BEFORE INSERT
ON t
BEGIN
NULL;
END bi_t;
/
CREATE OR REPLACE TRIGGER bd_t
BEFORE DELETE
ON t
BEGIN
NULL;
END bd_t;
/
SELECT trigger_name, status
FROM user_triggers;
ALTER TRIGGER bi_t DISABLE;
SELECT trigger_name, status
FROM user_triggers;
ALTER TABLE t DISABLE ALL TRIGGERS;
SELECT trigger_name, status
FROM user_triggers;
ALTER TRIGGER bd_t ENABLE;
SELECT trigger_name, status
FROM user_triggers;
ALTER TABLE t ENABLE ALL TRIGGERS;
SELECT trigger_name, status
FROM user_triggers;
Disable All Triggers On A Table
ALTER TABLE <table_name> DISABLE ALL TRIGGERS;
See DISABLE Demo;
Enable A Single Trigger
ALTER TRIGGER <trigger_name> ENABLE;
See DISABLE Demo;
Enable All Triggers On A Table
ALTER TABLE <table_name> ENABLE ALL TRIGGERS;
See DISABLE Demo;
Rename Trigger
ALTER TRIGGER <trigger_name> RENAME TO <new_name>;
ALTER TRIGGER bi_t RENAME TO new_trigger_name;
SELECT trigger_name, status
FROM user_triggers;
Drop Trigger (all types)
Drop Trigger
DROP TRIGGER <trigger_name>;
DROP TRIGGER new_trigger_name;
Trigger With Autonomous Transaction
Trigger declared as an autonomous transaction
conn uwclass/uwclass@pdbdev
ALTER TABLE audit_log
ADD (commentcol VARCHAR2(50));
desc audit_log
CREATE OR REPLACE TRIGGER t_autonomous_tx
BEFORE INSERT
ON person
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO audit_log
(chng_when, commentcol)
VALUES
(SYSDATE, 'Reporting an error');
COMMIT;
END t_autonomous_tx;
/
INSERT INTO person (fname) VALUES ('abcdefghijklmnopqrst');
SELECT * FROM person;
SELECT chng_when, commentcol FROM audit_log;
Cascading Triggers
Cascading Trigger Demo
conn uwclass/uwclass@pdbdev
CREATE TABLE cascade (
testcol VARCHAR2(10));
CREATE OR REPLACE TRIGGER t_cascade
AFTER INSERT
ON cascade
BEGIN
INSERT INTO cascade
(testcol)
VALUES
('change');
END t_cascade;
/
The insert into t1 firest the trigger which attempts to count the number of records in t1 ... which is ambiguous
conn uwclass/uwclass@pdbdev
CREATE TABLE t1 (x int);
CREATE TABLE t2 (x int);
INSERT INTO t1 VALUES (1);
SELECT * FROM t1;
SELECT * FROM t2;
CREATE OR REPLACE TRIGGER t_trigger
AFTER INSERT
ON t1
FOR EACH ROW
DECLARE
i PLS_INTEGER;
BEGIN
SELECT COUNT(*)
INTO i
FROM t1;
INSERT INTO t2
VALUES
(i);
END;
/
INSERT INTO t1 VALUES (1);
SELECT COUNT(*) FROM t1;
SELECT COUNT(*) FROM t2;
Mutating Trigger Fix With Autonomous Transaction
Count on t1 is performed as though a different user logged on and asked the question of t1
But keep in mind, even though this workaround exists, it is more likely you are not approaching the problem from the correct perspective ... consider options to avoid this.
conn uwclass/uwclass@pdbdev
CREATE OR REPLACE TRIGGER t_trigger
AFTER INSERT ON t1 FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
i PLS_INTEGER;
BEGIN
SELECT COUNT(*)
INTO i
FROM t1;
CREATE OR REPLACE TRIGGER t_t2_audit
AFTER UPDATE
ON t2
REFERENCING NEW AS n OLD AS o
FOR EACH ROW
BEGIN
INSERT INTO changes
VALUES (:n.x, :o.x, user);
END t_t2_audit;
/
SELECT * FROM t2;
UPDATE t2
SET x = 1;
COMMIT;
SELECT * FROM changes;
Trigger Enforcing Integrity Constraint
Trigger To Disallow Entry Of Future Dates
conn uwclass/uwclass@pdbdev
CREATE OR REPLACE TRIGGER t_date
BEFORE INSERT
ON orders
FOR EACH ROW
DECLARE
bad_date EXCEPTION;
BEGIN
IF :new.datecol > SYSDATE THEN
RAISE_APPLICATION_ERROR(-20005,'Future Dates Not Allowed');
END IF;
END;
/
INSERT INTO orders VALUES ('ABC', 999, SYSDATE-1);
INSERT INTO orders VALUES ('ABC', 999, SYSDATE);
INSERT INTO orders VALUES ('ABC', 999, SYSDATE+1);
This demonstration should be run in both 11gR2 and prior versions. In prior versions the trigger is invalidated by adding or dropping the column "newcol"
conn uwclass/uwclass@pdbdev
CREATE TABLE t1 (
testcol NUMBER(3));
CREATE OR REPLACE TRIGGER bi_t
BEFORE INSERT
ON t1
FOR EACH ROW
BEGIN
:NEW.testcol := 1;
END;
/
SELECT trigger_name, status
FROM user_triggers;
ALTER TABLE t
ADD (newcol VARCHAR2(3));
SELECT trigger_name, status
FROM user_triggers;
ALTER TABLE t DROP COLUMN newcol;
SELECT trigger_name, status
FROM user_triggers;
Trigger Ensuring Data Entry During Business Hours
Trigger To Disallow Entry Of Future Dates
conn uwclass/uwclass@pdbdev
CREATE OR REPLACE PROCEDURE secure_dml(dateval IN DATE)IS
BEGIN
IF TO_CHAR (dateval, 'HH24:MI') NOT BETWEEN '08:00' AND '18:00'
OR TO_CHAR (dateval, 'DY') IN ('SAT', 'SUN') THEN
RAISE_APPLICATION_ERROR (-20205, 'Changes only allowed during office hours');
END IF;
END secure_dml;
/
CREATE OR REPLACE TRIGGER secure_data
BEFORE INSERT OR UPDATE OR DELETE
ON orders
FOR EACH ROW
BEGIN
secure_dml(:NEW.datecol);
END;
/
INSERT INTO orders VALUES ('ABC', 999, SYSDATE-1);
INSERT INTO orders VALUES ('ABC', 999, SYSDATE-4/24);
INSERT INTO orders VALUES ('ABC', 999, SYSDATE+1);
Native Dynamic SQL Demo
Native Dynamic SQL used to disable, and re-enable, all triggers in a schema
conn uwclass/uwclass@pdbdev
SELECT status, COUNT(*)
FROM user_triggers
GROUP BY status;
DECLARE
exstr VARCHAR2(100);
CURSOR dtt_cur IS
SELECT DISTINCT table_name
FROM user_triggers;
BEGIN
FOR dtt_rec IN dtt_cur LOOP
exstr := 'ALTER TABLE ' || dtt_rec.table_name || ' DISABLE ALL TRIGGERS';
execute immediate exstr;
END LOOP;
END;
/
SELECT status, COUNT(*)
FROM user_triggers
GROUP BY status;