Oracle PL SQL/Trigger before insert or delete or update on StmTbl

Question

How can i avoid null value of :old or new? Im trying to insert to one table 3 step before update,delete,insert with having more columns

Answer 1

How can i avoid null value of :old or new?

Both old and new pseudorecords reflect values in the table (old ones are "previous", new ones are ... well, "new", which will replace old ones).

Suppose there's a job column in the table and its value is NULL. If you run

update emp set job = 'CLERK' where empno = 7369;

then :old.job = NULL, :new.job = 'CLERK'.

Or, the opposite: suppose there was 'ANALYST' in that column and you run

update emp set job = null where empno = 7369;

so :old.job = 'ANALYST', :new.job = NULL.

---

You asked how to avoid null; why would you want to "avoid" them? They have values they should have. There's nothing wrong in NULL as it means that there's an absence of a value.

In the 1st example above (where :old.job = null), what would you want to use instead of it? There was no value there ...

If you must (although, I don't know why), you could use NVL or COALESCE functions or CASE expression; for example:

insert into log_table (job) values (nvl(:old.job, 'no previous job'));

insert into log_table (job) values (coalesce(:old.job, 'no previous job'));

insert into log_table (job), values (case when :old.job is null then 'no previous job'
                                          else :old.job
                                     end);