Default Value not firing when using a View/Trigger

[RichardF]

Hi,

I got a bit of a problem with this one. Below is a small sample of the replicated problem (because the real tables have many more columns).

CREATE TABLE abc
(
	IDENT VARCHAR2(100) NOT NULL PRIMARY KEY, 
	CREATION DATE DEFAULT SYSDATE NOT NULL
);
/

CREATE OR REPLACE VIEW abc_v (IDENT, CREATION) AS 
SELECT SUBSTR(IDENT, 1, 3), CREATION 
FROM abc;
/

CREATE OR REPLACE TRIGGER abc_v_b4 
INSTEAD OF INSERT ON abc_v 
FOR EACH ROW
DECLARE
BEGIN
	INSERT INTO abc (IDENT, CREATION) VALUES ( :new.IDENT, :new.CREATION);
END;
/

-- succeeds
INSERT INTO abc (IDENT) VALUES ( 'ABC' );

-- fails
INSERT INTO abc_v (IDENT) VALUES ( 'ABC' );

Do you guys know a way round this ? ?

Thanks,
Rich.



Programmers are tools for converting caffeine into code

 

[Dagon]

The problem is that you're specifying a value for the CREATION in the INSERT statement. In this case, the value would be NULL because that's what :new.CREATION is presently set to. To get it to use the default value, you would have to miss out CREATION altogher and just have:

INSERT INTO abc (IDENT) VALUES ( :new.IDENT);

If you wanted to allow the users to pass in a value of CREATION, then you could just test for it being not null using something like:

CREATE OR REPLACE TRIGGER abc_v_b4 
INSTEAD OF INSERT ON abc_v 
FOR EACH ROW
DECLARE
BEGIN
    IF :new.creation is not null then
      INSERT INTO abc (IDENT, CREATION) VALUES ( :new.IDENT,:new.CREATION);
    ELSE
      INSERT into abc (ident) values (:new.IDENT);
    end if;
END;

Alternatively, just put in the default using the trigger.