I have a table of different app settings, I want to trigger a directory object update when the path variable changes in the database. (Using Oracle 10G)
settings table: (setting varchar2, value varchar2)
create or replace trigger SETTINGS_UPDATE
AFTER INSERT OR UPDATE ON SETTINGS
FOR EACH ROW WHEN (new.setting = 'DATA_PATH')
execute immediate 'CREATE OR REPLACE DIRECTORY DATA_PATH AS '||:new.value;
now on an inser statement oracle complains about literal strings:
ERROR at line 1:
ORA-01780: string literal required
ORA-06512: at "SETTINGS_UPDATE", line 2
ORA-04088: error during execution of trigger 'SETTINGS_UPDATE'
I have also tried without the 'execute immediate' but then the error tells me that I can't start with 'CREATE...'
Any help welcome.