| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Help! Calling a PL/SQL procedure with a trigger ... just isn't working!
A copy of this was sent to jeff <nospam_at_nettwerk.com>
(if that email address didn't require changing)
On Mon, 04 Jan 1999 05:25:14 -0800, you wrote:
>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.
>
making some assumptions here:
If partitions has an exception handler that just catches and ignores errors, get rid of it.
If partitions is doing DDL, you cannot call it from a trigger. DDL commits automagically and since you are midway through an insert (insert isn't over yet) you cannot commit.
If you really really need to do DDL in a trigger you can use dbms_job today to schedule the ddl to occur shortly after the transaction commits. In Oracle8i, you'll be able to use what is known as an autonomous transaction to do the same.
>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
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Mon Jan 04 1999 - 08:41:34 CST
 |
 |