Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Help! Calling a PL/SQL procedure with a trigger ... just isn't working!
Hello-
I am trying to call a stored procedure using a trigger, and it doesn't seem to want to work for some reason.
Current system: OS = Windows NT 4 (sp3), 8.0.4 production Eventually: OS = solaris 2.6, 8.0.5 production
I have written a simple stats program that uses 6 different tables, and all are partitioned using the date column (which is actually a number in the form 'YYYYMMDD'). In order to help with the administration of these tables, I have written some dynamic sql that will create (by adding or splitting) or drop partitions for all of the tables involved. This procedure works just fine when called by sqllpus or from another PL/SQL procedure, and is called DO_PARTITIONS.
DO_PARTITIONS has 2 parameters, the date (which is actually a number in the form 'YYYYMMDD') and the action (varchar2 of 'create' or 'drop').
I then created this trigger to hopefully automate the job even further, so that when a new date is added to the DATE table, the partitions would be created automatically. Similarly, when a date is deleted, the appropriate table partitions would be dropped.
Here is the trigger:
create or replace trigger dates_trig before insert or delete on dates for each row begin if INSERTING then do_partitions(:new.date_id,'create'); dbms_output.put_line('INSERTING'); else do_partitions(:old.date_id,'drop'); dbms_output.put_line('DELETING'); end if; end; /
When I insert into the table, the appropriate "INSERTING" output is generated, and "DELETING" appears when a date is being deleted, but the partitions are not being added or dropped.
I'm at a loss, and any help would be GREATLY appreciated.
....jeff
jeff grant, it manager, nettwerk productions http://www.nettwerk.com http://www.nettweb.com mailto:jeff_at_nettwerk.com Received on Mon Jan 04 1999 - 07:25:14 CST