Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2013
    Posts
    46

    Unanswered: Unable to compile trigger

    Hi, I have the following trigger which is not compiling. Below trigger I have shown error code. Please suggest what i am doing wrong?

    CREATE OR REPLACE TRIGGER XYZ_AIDR
    AFTER INSERT OR DELETE ON XYZ
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    BEGIN
    IF inserting THEN
    MERGE INTO ABC AS T
    USING (
    VALUES (:NEW.TAR_TABLE)
    )
    ON (:NEW.TAR_TABLE=T.TBL)
    -- if value is same, this needs to be ignored
    -- if value is different, it has to be a new insert
    WHEN NOT MATCHED THEN
    INSERT (T.TBL)
    VALUES(:NEW.TAR_TABLE);
    --DELETE IF ALL POSSIBLE OCCURENCES DELETED from XYZ
    ELSIF deleting THEN

    IF NOT EXISTS (SELECT X.TAR_TABLE FROM XYZ X
    WHERE X.TAR_TABLE=:OLD.TAR_TABLE)

    THEN DELETE FROM ABC C
    WHERE C.TBL=:OLD.TAR_TABLE;

    ELSE
    END IF;

    END IF;
    END;

    Error(4,4): PL/SQL: SQL Statement ignored
    Error(4,37): PL/SQL: ORA-02012: missing USING keyword
    Error(27,3): PLS-00103: Encountered the symbol "END" when expecting one of the following: ( begin case declare exit for goto if loop mod null pragma raise return select update while with <an identifier> <a double-quoted delimited-identifier> <a bind variable> << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge

    i have used USING keyword, so don't know why it is saying it is missing from merge statement. well this is aggrevating.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You need to learn how to produce & debug your own code.
    First produce valid static MERGE command with hard coded values.
    Next include MERGE into valid PL/SQL procedure
    Lastly replace hard coded values with variables; one at at time.

    First make it work; then make it fancy.
    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.

  3. #3
    Join Date
    Dec 2007
    Posts
    253
    Quote Originally Posted by anacedent View Post
    First make it work; then make it fancy.
    +1

    I'm going to commandeer that phrase

Posting Permissions

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