The trouble with triggers

From: John Gordon <gordon_at_panix.com>
Date: Fri, 17 Nov 2006 17:40:59 +0000 (UTC)
Message-ID: <ejks7b$phh$1@reader2.panix.com>

(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

Received on Fri Nov 17 2006 - 11:40:59 CST