Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Help! Calling a PL/SQL procedure with a trigger ... just isn't working!

Help! Calling a PL/SQL procedure with a trigger ... just isn't working!

From: jeff <nospam_at_nettwerk.com>
Date: Mon, 04 Jan 1999 05:25:14 -0800
Message-ID: <3690C13A.65F9C173@nettwerk.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US