dcsimg
Results 1 to 3 of 3

Thread: Oracle Trigger help - Very basic

  1. #1
    Join Date
    Apr 2011
    Posts
    2

    Oracle Trigger help - Very basic

    Hello, this is what am trying to achieve

    Capture OSUser name who is making changes to table data (from an application tied to LDAP) and move the whole record (when INSERT UPDATE DELETE) to an audit table

    Parent table
    CREATE TABLE MASTER_TAB
    (
    COL1 VARCHAR2(3 BYTE)
    );

    History table
    CREATE TABLE MASTER_TAB_HST
    (
    COL1 VARCHAR2(3 BYTE),
    OSUSER VARCHAR2(5 BYTE)
    )

    Trigger on parent table
    CREATE OR REPLACE TRIGGER INVITRO.TRG_master_tab
    BEFORE INSERT OR UPDATE
    ON master_tab
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    DECLARE
    BEGIN
    IF INSERTING THEN
    INSERT INTO master_tab_hst VALUES (:NEW.col1,
    SELECT SYS_CONTEXT('USERENV', 'OS_USER')
    INTO :NEW.OSUSER
    FROM dual;
    ELSIF UPDATING THEN
    INSERT INTO master_tab_hst VALUES (:NEW.col1,
    SELECT SYS_CONTEXT('USERENV', 'OS_USER')
    INTO :NEW.OSUSER
    FROM dual;
    END IF;
    EXCEPTION
    WHEN OTHERS THEN
    -- Consider the error and then re-raise
    RAISE;
    END ;

    Getting error when creating the trigger, I know am doing something wrong here.

    Help much appreciated.

    -Srini

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,912

  3. #3
    Join Date
    Apr 2011
    Posts
    2
    Err...missed the error. Below is the error I get in toad

    [Error] Syntax check (10: 6): Found: 'SELECT' Invalid identifier: SELECT
    [Error] Syntax check (10: 13): Found: 'SYS_CONTEXT' Expecting: ) , -or- + - || -or- * / MOD REMAINDER -or- ** -or- (+) AT DAY MULTISET YEAR -or- % . [ -or- @ -or- ( string

Posting Permissions

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