It is inconsistent behavior in Oracle 11g that even if triggering condition not satisfied for update trigger, still adding column with default value will trigger the trigger. It can be replicated in any 11 DB or if DB has compatibility level set to 11G.
There is some other post also that reports similar issue.
I did some tests with
different-2 scenarios in different versions and there seems some
inconsistencies .
Oracle 12CR2:
CREATE TABLE Sampletab (col1
INTEGER PRIMARY KEY, modify_date TIMESTAMP);
CREATE OR REPLACE TRIGGER
trg_sampletab
BEFORE UPDATE
ON Sampletab
FOR EACH ROW
BEGIN
:new.modify_date := SYSTIMESTAMP;
END;
/
INSERT INTO Sampletab
VALUES
(1, SYSTIMESTAMP);
INSERT INTO Sampletab
VALUES
(2, SYSTIMESTAMP);
SELECT * FROM Sampletab;
Note down modify_date
ALTER TABLE Sampletab ADD
(newcol1 INTEGER DEFAULT '0');
SELECT * FROM Sampletab;
Note down modify_date again. There
is no difference. So it means trigger not fired
Oracle 11G2O3:
Scenario#1:Row update trigger
CREATE TABLE Sampletab (col1
INTEGER PRIMARY KEY, modify_date TIMESTAMP);
CREATE OR REPLACE TRIGGER
trg_sampletab
BEFORE UPDATE
ON Sampletab
FOR EACH ROW
BEGIN
:new.modify_date := SYSTIMESTAMP;
END;
/
INSERT INTO Sampletab
VALUES
(1, SYSTIMESTAMP);
INSERT INTO Sampletab
VALUES
(2, SYSTIMESTAMP);
SELECT * FROM Sampletab;
Note down modify_date
ALTER TABLE Sampletab ADD
(newcol1 INTEGER DEFAULT '0');
SELECT * FROM Sampletab;
Note down modify_date again. There
is difference. It means trigger got fired
Scenario#1:Column update trigger
CREATE TABLE Sampletab (col1
INTEGER PRIMARY KEY, modify_date TIMESTAMP);
CREATE OR REPLACE TRIGGER
trg_sampletab
BEFORE UPDATE
of col1
ON Sampletab
FOR EACH ROW
BEGIN
:new.modify_date := SYSTIMESTAMP;
END;
/
INSERT INTO Sampletab
VALUES
(1, SYSTIMESTAMP);
INSERT INTO Sampletab
VALUES
(2, SYSTIMESTAMP);
SELECT * FROM Sampletab;
Note down modify_date
ALTER TABLE Sampletab ADD
(newcol1 INTEGER DEFAULT '0');
SELECT * FROM Sampletab;
Note down modify_date again. There
is no difference. It means trigger not fired
Scenario#1:Column update
invalid trigger
CREATE TABLE Sampletab (col1
INTEGER PRIMARY KEY, modify_date TIMESTAMP);
CREATE OR REPLACE TRIGGER
trg_sampletab
BEFORE UPDATE
of col1
ON Sampletab
FOR EACH ROW
BEGIN
:new.modify_date := SYSTIMESTAMP1;
END;
/
INSERT INTO Sampletab
VALUES
(1, SYSTIMESTAMP);
INSERT INTO Sampletab
VALUES
(2, SYSTIMESTAMP);
SELECT * FROM Sampletab;
Note down modify_date
ALTER TABLE Sampletab ADD
(newcol1 INTEGER DEFAULT '0');
Result: ORA-00604: error occurred
at recursive SQL level 1
ORA-04098: trigger
'SCPOMGR.TRG_MUC_MOD_DT' is invalid and failed re-validation
So it seems oracle is compiling
trigger in 11G even if it won’t get invoked.
