Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2013
    Posts
    2

    Unanswered: ORA-01843: not a valid month

    Hi, i'm currently receiving this error message when trying to delete data from a customers table. When the data is been deleted a trigger is been fired to insert the old values into a new previous customers table. The trigger code is here


    [CODE]create or replace TRIGGER CUSTOMER_AD
    BEFORE DELETE ON CUSTOMER
    REFERENCING OLD AS OLD
    FOR EACH ROW
    DECLARE
    pragma autonomous_transaction;
    nPlaced_order_count NUMBER;
    BEGIN
    SELECT COUNT(*)
    INTO nPlaced_order_count
    FROM PLACED_ORDER p
    WHERE p.FK1_CUSTOMER_ID = :OLD.CUSTOMER_ID;
    IF nPlaced_order_count > 0 THEN
    INSERT into previous_customer
    (customer_id,
    first_name,
    last_name,
    address)
    VALUES
    (ld.customer_id,
    ld.first_name,
    ld.last_name,
    ld.address);
    END IF;
    END CUSTOMER_AD;
    [CODE]
    And the previous employee table structure is like this

    CUSTOMER_ID VARCHAR2 10
    FIRST_NAME VARCHAR2 25
    LAST_NAME VARCHAR2 25
    ADDRESS VARCHAR2 50

    Anyone any idea why i'd be getting this error message?
    There is no trigger on the previous_customer table and no other triggers on the customer table
    Last edited by RichardClare; 05-13-13 at 12:23. Reason: added full trigger and explanation

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Please show us the complete trigger code.

    Did you check if there is an insert trigger on PREVIOUS_CUSTIOMER?
    Or maybe more than one delete trigger on the CUSTOMERS table?
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Mar 2013
    Posts
    2

    Updated

    Hi i've updated the question and added code still no idea why its saying this?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    given that the trigger contains no DATE, I suspect the problem occurs elsewhere
    Code:
    CREATE OR replace TRIGGER customer_ad 
      BEFORE DELETE ON customer 
      REFERENCING OLD AS OLD 
      FOR EACH ROW 
    DECLARE 
        PRAGMA autonomous_transaction; 
        nplaced_order_count NUMBER; 
    BEGIN 
        SELECT Count(*) 
        INTO   nplaced_order_count 
        FROM   placed_order p 
        WHERE  p.fk1_customer_id = :OLD.customer_id; 
    
        IF nplaced_order_count > 0 THEN 
          INSERT INTO previous_customer 
                      (customer_id, 
                       first_name, 
                       last_name, 
                       address) 
          VALUES      (ld.customer_id, 
                       ld.first_name, 
                       ld.last_name, 
                       ld.address); 
        END IF; 
    END customer_ad;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    also why are you using "pragma autonomous_transaction;", there is no need in your trigger for it.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •