ORA-04091 - trogger problems

[benjamenus]

FORCING DEFAULT VALUES WHEN THE APPLICATION INSERTS NULL...

I have an application which uses an Oracle DB. I do not have access to the source code for this application.

It is possible for a user to add a record leaving a field blank. When the application inserts this record it specifies NULL as the value for blank fields.

I wish to force a default value where users leave blank fields but cannot do this by amending the table as specifying NULL overrides this.

I've tried writing an after-insert trigger that checks the record and updates it if there are null fields, however this produces error 4091 as I'm trying to update the table I'm reading from.

Any ideas how to force the default values without having access to the application source code will be greatly appreciated.

I've tried reconfiguring the users but they keep crashing!

 

[carp]

This worked for me in Oracle 8i; I think it will work in 7.*:

CREATE OR REPLACE TRIGGER my_trigger BEFORE INSERT ON my_table
FOR EACH ROW
BEGIN
IF new.x IS NULL) THEN
:new.x := sysdate;
END IF;
END;

 

[jimbopalmer]

BTW even if you have 'no source' check the System table All_source, you may have more source than you know. The one thing you can't give for your heart's desire is your heart. - Lois McMaster Bujold

 

[benjamenus]

Cheers Carp

Unfortunately this does not work. To test it I tried saying

if (new.somecol is null or new.somecol is not null) then
:new.somecol := 'new value';
end if;

whatever the value of somecol, I should have ended up with 'new value', but alas not. I'm a bit stuck now...

 

[MikeLacey]

ben - is it that the column is not really null?

try testing for '' Mike
michael.j.lacey@ntlworld.com
Email welcome if you're in a hurry or something -- but post in tek-tips as well please, and I will post my reply here as well.