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@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 -- //www.freelists.org/webpage/oracle-l
