Re: The trouble with triggers

John Gordon wrote:
> (Apologies to David Gerrold.)
>
> I'm having trouble getting a trigger to work, and I could use some help.
>
> Whenever a new record is inserted into MASTERTABLE, the trigger is supposed
> to insert a new record in HISTORYTABLE, consisting of the same fields from
> MASTERTABLE plus the date when the transaction occurred.
>
> To keep things simple, I'm using two bare-bones tables:
>
> MASTERTABLE
> -----------
> ID Integer (primary key)
> Name VARCHAR
>
>
> HISTORYTABLE
> ------------
> ID Integer (primary key)
> Name VARCHAR
> MyDate DATE
>
>
> Here is the trigger I'm trying to use:
>
> CREATE OR REPLACE TRIGGER TestTrigger
> AFTER INSERT ON MASTERTABLE
> FOR EACH ROW
> BEGIN
> thedate := SYSDATE;
> INSERT INTO HISTORYTABLE VALUES(:new.ID, :new.Name, thedate);
> END;
>
> But when I add a row to MASTERTABLE, I get an ORA-04098 error "The trigger
> is invalid and failed re-validation." Very helpful message, there.
>
> I've tried compiling the trigger and then showing the errors, but I get
> an ORA-00900 Invalid SQL Statement error when I try to show the errors
> with this command: SHOW ERRORS TRIGGER TestTrigger;
>
> The database is Oracle 9.2.0.6 (on a remote server), and locally I'm
> using Oracle's free SQL Developer package to issue SQL commands.

Why are you using a trigger? An single insert into multiple tables can be done in Oracle.

For example try this:

CREATE TABLE ap_cust (
customer_id VARCHAR2(4),
program_id VARCHAR2(3),
del_date DATE);

CREATE TABLE ap_orders (
order_date DATE,
program_id VARCHAR2(3));

INSERT ALL
INTO ap_cust VALUES (customer_id, program_id, delivered_date) INTO ap_orders VALUES (order_date, program_id) SELECT program_id, delivered_date, customer_id, order_date FROM airplanes;

You can get the DDL for the airplanes table and its data at: http://www.psoug.org/files/airplanes.sql

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org