What do references to OLD evaluate to in the WHEN cause of an Oracle insert trigger?

Question

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…

Accepted Answer

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;
```

What do references to OLD evaluate to in the WHEN cause of an Oracle insert trigger?

Solution

Problem