Mark, 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@xxxxxxxxxx> 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@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 > > >