I am enrolled at the Art Institute of Pittsburgh - Online working towards a Bachelor of Science in photography.
My other blog [blogspot.com]
We want to be able to perform some inserts to other tables and apply some grants if the criteria in an update trigger are met. Once met, the criteria will never change. The grants and inserts may change over time and other files may be added. I find myself in a position where the most logical (to me anyway) solution seems to be one in which I can dynamically run an SQL file.
Here's my pseudocode of how I envision it should work.
CREATE OR REPLACE TRIGGER au_some_tbl
AFTER UPDATE ON some_tbl
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
v_file VARCHAR2(1024); -- excessively large, I know
IF (:NEW.enabled = 1 AND
:OLD.enabled = 0) THEN
:= '/path/to/sql/files/' || :NEW.name || '.sql';
@@v_file; -- somehow execute the sql file
WHEN OTHERS THEN
So let's say I have a "function" named say_foo in some_tbl and enabled is set to 1 for that row. The trigger should execute the SQL contained in
The problem is that there doesn't appear to be a way to do this. It looks like I have to add a case or a series of if statements to get it work; it also means that the trigger will have to be edited any time we need a new "function" added. I hope that either I am wrong and it can be done or that there is a cleaner solution. Does any one have any ideas?