#1
  1. No Profile Picture
    Contributing User
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2003
    Posts
    3,601
    Rep Power
    595

    Noob Inserting table data into a variable


    I am trying to insert a column into a variable from a trigger.
    Here is the code that i have:
    Code:
    CREATE OR REPLACE TRIGGER BUYER_after_update AFTER UPDATE ON buyer
    FOR EACH ROW
    DECLARE
        v_key varchar2(10);
    BEGIN
        select ID into v_key from buyer;
        insert into message_log_table (table_name, message_comments)
    values
        ('Buyer', 'Buyer '||v_key||' has been updated');
    end;
    /
    When I run the above I get the following compiler error:
    [Error] ORA-00904 (6: 12): PL/SQL: ORA-00904: "ID": invalid identifier

    Since ID is defined in my BUYER table I do not understand what the error means. Can someone please explain this error message to me.

    Here is my create table statement:
    Code:
    CREATE TABLE BUYER 
    (
        ID    VARCHAR(50)    NOT NULL PRIMARY KEY,
        FNAME VARCHAR(50) NOT NULL,
        LNAME VARCHAR(50)  NOT NULL,
        ADDRESS     VARCHAR(50) NOT NULL,
        CITY        VARCHAR(50) NOT NULL,
        STATE       VARCHAR(2)  NOT NULL,
        ZIP_CODE    NUMBER(5)   NOT NULL
    );
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2003
    Posts
    3,601
    Rep Power
    595
    I was able to resolve my own error with the following:

    Code:
    CREATE OR REPLACE TRIGGER GETKEY_after_update AFTER UPDATE ON buyer
    FOR EACH ROW
    
    BEGIN
        insert into message_log_table (table_name, message_comments)
    values
        ('Buyer', 'Buyer '||:old.ID||' has been updated');
    end;
    /
    There are 10 kinds of people in the world. Those that understand binary and those that don't.

IMN logo majestic logo threadwatch logo seochat tools logo