Re: The trouble with triggers

From: What's in a namespace <xml_at_ns.com>
Date: Mon, 20 Nov 2006 15:10:55 +0100
Message-ID: <4561b76b$0$335$e4fe514c@news.xs4all.nl>

"DA Morgan" <damorgan_at_psoug.org> bubbled in news:1163960393.455305_at_bubbleator.drizzle.com...
> 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

But I don't think you want to re-program this for every insert on this table in an application. Using triggers for history tables looks very normal to me. I'm glad you're not a car mechanic.
"My Ford does not run! Ford? Why a Ford? You can buy a Chevy! Or a bike! Or go by train.... Or walk! Or stay at home..."

And then of course HPUXRAC and Mladen and friends come in and a discussion will start about train tables, bike tyres, home building etc etc etc. (Just kidding!)
And all because of one simple programming error..... (your example has one too by the way, so finally you didn't even help the OP by replacing one error with another). I think Sybrands answer was sufficient!

Shakespeare
(ns.oracle.com is still down) Received on Mon Nov 20 2006 - 08:10:55 CST