Question on Trigger Code
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Question on Trigger Code

  1. #1
    Join Date
    Dec 2005
    Posts
    39

    Question on Trigger Code

    Hello. I'm trying to put a trigger onto table1 that will eventually cause data to be inserted into table2. My immediate problem is getting the new data to be inserted into variables. Here's and example of what I have:

    CREATE OR REPLACE TRIGGER tr_table1_insert
    AFTER INSERT ON table1
    FOR EACH ROW
    DECLARE
    v_emp1 NUMBER(3);

    BEGIN
    SELECT :new.emp1 INTO v_emp1;

    END;

    This gives me the following error:
    PLS-00103: Encountered the symbol ";" when expecting one of the following: . ( , % from

    I can't see what's wrong with the syntax. Can someone help me? Thanks.

  2. #2
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    First, in Oracle SELECT needs a FROM clause. The system table DUAL is provided for this, although in principle it's no different from any other table.

    However, PL/SQL has an assignment operator ":=", so you could code for example,

    DECLARE
    v_emp1 emp.emp1%TYPE := :new.emp1;
    BEGIN
    ...code using v_emp1...
    END;

    But why go to the trouble of duplicating the variable at all? I would just use :new.emp1 directly in the code.

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