Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2008
    Posts
    5

    Unanswered: Trigger is not working properly

    I have two table:
    NodeTable
    MetaNOdeTable

    NodeTable
    ----------------------------
    NodeId :1
    NodeType:Tools (This NodeId & NodeType are column)

    MetaNodeTable
    -------------------------
    NodeId:1
    SubType: Process (This NodeId & SubType are column)


    Here NodeId ofboth the table are the primary key of that table.There is no FOREIGN Key between this two table.
    But there is a validation rules between this two table...i.e. the SubType of MetaNodeTable depends on the NodeType of NodeTable
    Example: If the NodeType of NodeTable is "Tools" the the SubType of MetaNodeTable will be "Process".


    CREATE OR REPLACE TRIGGER HPGMNGDEMO.NODE_TYPE_FORM
    BEFORE INSERT OR UPDATE
    ON HPGMNGDEMO.MWB_META_NODE_TBL
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    DECLARE
    RT MWB_NODE_TBL.NODE_TYPE%TYPE;RID MWB_NODE_TBL.NODE_ID%TYPE;
    TT EXCEPTION;
    BEGIN
    SELECT NODE_TYPE INTO RT,NODE_ID INTO RID FROM MWB_NODE_TBL WHERE NODE_ID:=:NEW.NODE_ID;
    IF ( RT:='Tools' AND :NEW.SUB_TYPE NOT IN('Process'))
    THEN RAISE TT
    END IF
    END;
    It's throwing exception at the time of compilation showing:
    PL/SQL: ORA-00923: FROM keyword not found where expected

  2. #2
    Join Date
    Jan 2004
    Posts
    370
    Check the syntax of the select ... into statement


    .

  3. #3
    Join Date
    Mar 2007
    Posts
    623
    Yes, your SELECT INTO statement has incorrect syntax.
    Instead of correcting it, throw it away and reference the desired columns of the inserted/updated row directly by :NEW.<column_name>.
    You can find more info in http://download.oracle.com/docs/cd/B...4.htm#i2235611; search for "new".

    Just for completeness, maybe using check constraint would be sufficient for this task.
    Link in documentation: http://download.oracle.com/docs/cd/B...2.htm#g1053592, search for "check".

  4. #4
    Join Date
    Mar 2008
    Posts
    4
    Hi Arpita,

    It is only select statement problem.

    Regards,
    Hemant

  5. #5
    Join Date
    Mar 2008
    Posts
    2
    Hi,
    Arpita
    you have problem in select into stmt
    write like this
    select node_id, node_type into rt, rid from MWB_NODE_TBL WHERE NODE_ID:=:NEW.NODE_ID;

    now see result
    By
    mahesh rai

Posting Permissions

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