When writing a row-level trigger in Oracle, I know that you can use the OLD
and NEW
pseudo-records to reference the old and new state of the row that fired the trigger.
I know that in an INSERT
trigger OLD
doesn't contain any data, but I'm not sure how this affects the evaluation of a WHEN
clause for that trigger. For example, if I have the following trigger:
CREATE OR REPLACE TRIGGER mung_row
BEFORE INSERT OR UPDATE ON some_table
FOR EACH ROW
BEGIN
:NEW.foo = 'some val';
END;
and I want to modify this trigger to only run on an update when foo was previously null, but always run on an insert, I could satisfy the update part of the change by adding a WHERE
clause:
CREATE OR REPLACE TRIGGER mung_row
BEFORE INSERT OR UPDATE ON some_table
FOR EACH ROW
WHEN (OLD.foo IS NULL)
BEGIN
:NEW.foo = 'some val';
END;
Will this cause problems in the insert case? What will OLD.foo evaluate to in the INSERT
?
I'm aware that I could split the INSERT
and UPDATE
triggers or use INSERTING
/UPDATING
/DELETING
in the trigger body, but I'd rather not in the case that inspired this question.
When a record is being inserted, every field of OLD
will be NULL
, including the fields marked as NOT NULL
in the table's definition.
For example, suppose your table has a non-nullable column named id
:
CREATE TABLE some_table (
id NUMBER NOT NULL,
foo VARCHAR2(100)
)
When a record is inserted into this table, OLD.id
will be NULL
. However, when a record is updated in this table, OLD.id
will not be NULL
. Because you only want to change :NEW.foo
if a record is being updated, you just have to check to see if OLD.id
has a non-null value.
CREATE OR REPLACE TRIGGER mung_row
BEFORE INSERT OR UPDATE ON some_table
FOR EACH ROW
WHEN (OLD.id IS NOT NULL AND OLD.foo IS NULL)
BEGIN
:NEW.foo = 'some val';
END;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With