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.
>
> --
> John Gordon "... What with you being his parents and all,
> gordon_at_panix.com I think that you could be trusted not to shaft
> him." -- Robert Chang, rec.games.board

The variable thedate isn't DECLAREd.

SELECT * FROM USER_ERRORS WHERE NAME = '<NAME OF INVALID OBJECT>' should always work.

Hth

-- 
Sybrand Bakker
Senior Oracle DBA