Execution Error while using a trigger
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Execution Error while using a trigger

  1. #1
    Join Date
    Feb 2006
    Posts
    1

    Execution Error while using a trigger

    hi, I have created the procedure in scott schema and the trigger in tinku schema. the procedure is created with no errors but trigger is created with compilation errors.
    the procedure created in scott schema:

    CREATE OR REPLACE PROCEDURE populate_pub_dtls(pPublisherId Number)
    IS
    CURSOR book_id_cursor IS
    SELECT book_index
    FROM books
    WHERE publisher_id=pPublisherId;

    next_val NUMBER;
    BEGIN
    FOR i IN book_id_cursor LOOP
    SELECT updates_id_seq.NEXTVAL INTO next_val FROM dual;
    INSERT INTO scott.publisher_details
    VALUES (next_val,i.book_index,12);
    END LOOP;
    COMMIT;
    END;


    the Trigger created in Tinku schema is:

    CREATE OR REPLACE TRIGGER update_publisher_trgr
    AFTER INSERT OR UPDATE
    ON tinku.book_publishers
    FOR EACH ROW
    BEGIN
    IF INSERTING THEN
    INSERT INTO scott.publishers
    VALUES(:new.p_id,:new.name,:new.token_id,:new.is_reg);
    ELSIF UPDATING THEN
    UPDATE scott.publishers
    SET is_reg=:new.is_reg
    WHERE publisher_id=ld.p_id;
    IF(:new.is_reg=0) THEN
    scott.populate_pub_dtls ( ld.p_id );
    END IF;
    END IF;
    END update_publisher_trgr;

    but this trigger on execution is giving the following errors:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    3/8 PLS-00201: identifier 'PUBLISHERS' must be declared
    3/8 PL/SQL: SQL Statement ignored
    6/6 PLS-00201: identifier 'PUBLISHERS' must be declared
    6/6 PL/SQL: SQL Statement ignored
    10/28 PLS-00201: identifier 'SCOTT.POPULATE_PUB_DTLS '
    must be declared

    10/28 PL/SQL: Statement ignored


    but scott.publishers is existing.

    how can i get rid of this error.

    Please help me in this regard,

    Thanks in Advance;

    Trinath Somanchi,

  2. #2
    Join Date
    Jan 2006
    Posts
    3
    Check object privileges.

Posting Permissions

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


Click Here to Expand Forum to Full Width