Re: using default column values with an instead of trigger

I've noticed this behavior elsewhere as well. Basically, since you are defining :NEW.a in your trigger, it's treated as NULL (which you demonstrated). You would definitely have to add a check in IF :NEW.a IS NULL THEN and don't use it in your INSERT statement. Or optionally, define the default in your trigger.

If it changes there, you do a code change. If it changes in your table definition, you'll need to perform DDL.

(Not sure if I only repeated what you said...it's late).

chet

On Tue, Nov 17, 2009 at 10:25 PM, Mark Weaver <mark-clist_at_npsl.co.uk> wrote:

> I'm having a bit of trouble with an INSTEAD OF INSERT trigger on a view --
> it seems that any default column values aren't supplied to the trigger.
> It's easiest to explain with an example.
>
> First without a trigger:
>
> create table x (a int default 5, b int);
> create view y as select * from x;
>
> insert into y (b) values (6);
>
> 1 row created.
>
> select * from y;
>
> A B
> ---------- ----------
> 5 6
>
> So here "A" gets the default value of 5 which is what I expect.
>
> However, when I add an INSTEAD OF INSERT trigger:
>
> rollback;
>
> create or replace trigger z
> instead of insert on y
> for each row
> begin
> dbms_output.put_line('a is '||:NEW.A);
> insert into x (a, b) values (:NEW.A, :NEW.B);
> end;
> /
>
> insert into y (b) values (6);
> a is
>
> 1 row created.
>
> axels_at_mail> select * from y;
>
> A B
> ---------- ----------
> 6
>
> 1 row created.
>
> Here I've lost the default value -- it's not supplied to the trigger as
> :NEW.A as I would hope.
>
> Of course, if I leave it out of the trigger, as:
>
> begin
> insert into (b) values (:NEW.B);
> end;
>
> then the insert picks up the default value. I could add a conditional on
> :NEW.A is null to take care of that, however that then leaves:
>
> insert into y (a, b) values (null, 6);
>
> indistinguishable from:
>
> insert into y (b) values (6);
>
> Is there any way around this behaviour?
>
>
> Thanks,
>
> Mark
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l