Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2011
    Posts
    8

    Angry Unanswered: Trigger fun... Well, not really.

    Hey guys, first time poster on this site!

    OK, I'm currently studying databases as one of my modules at university, so I'm not a total expert with Oracle and SQL, but I know enough

    Here's my issue: I'm trying to create an audit table using the following trigger:

    Create table audit_cases
    (Audit_ID NUMBER(22) not null,
    Audit_Analyst_ID VARCHAR2(100),
    Audit_CASE_DETAILS VARCHAR2(500),
    Audit_DATE_SUBMITTED Date,
    Audit_CASE_STATUS VARCHAR2(500),
    Audit_DATE_DUE Date,
    Audit_Case_Status
    );


    CREATE OR REPLACE TRIGGER trg_audit_case
    -- starts on every update, insert or delete command
    AFTER INSERT OR DELETE OR UPDATE ON case
    FOR EACH ROW
    DECLARE
    -- variable which declares if update, delete or insert process
    v_trg_action VARCHAR2(6);
    BEGIN
    IF updating
    THEN
    -- when update
    v_trg_action := 'UPDATE';
    ELSIF deleting
    THEN
    -- when delete
    v_trg_action := 'DELETE';
    ELSIF inserting
    THEN
    -- when insert
    v_trg_aktion := 'INSERT';
    ELSE
    -- if something else
    v_trg_action := NULL;
    END IF;

    IF v_trg_action IN ('DELETE','UPDATE') THEN
    -- if v_trg_action is DELETE or UPDATE then insert old table values
    INSERT INTO audit_cases
    ( Audit_ID,Audit_Analyst_ID, Audit_CASE_DETAILS, Audit_DATE_SUBMITTED, Audit_CASE_STATUS,Audit_DATE_DUE, AUDIT_LAST_USER, DATED_AUDITED, ACTION)

    VALUES
    (:OLD.Audit_ID, :OLD.Audit_Analyst_ID, :OLD.Audit_CASE_DETAILS, :OLD.Audit_DATE_SUBMITTED, :OLD.Audit_CASE_STATUS, OLD.Audit_DATE_DUE, UPPER(v('APP_USER')), SYSDATE, v_trg_action);
    ELSE

    -- if v_trg_action is INSERT then insert new table values
    INSERT INTO audit_cases
    ( Audit_ID,Audit_Analyst_ID, Audit_CASE_DETAILS, Audit_DATE_SUBMITTED, Audit_CASE_STATUS,Audit_DATE_DUE, AUDIT_LAST_USER, DATED_AUDITED, ACTION)

    VALUES
    (:NEW.Audit_ID, :NEW.Audit_Analyst_ID, :NEW.Audit_CASE_DETAILS, :NEW.Audit_DATE_SUBMITTED, :NEW.Audit_CASE_STATUS, :NEW.Audit_DATE_DUE,
    UPPER(v('APP_USER')), SYSDATE, v_trg_action);
    END IF;

    END trg_audit_case;


    When I try to run this, I keep getting that damnable bad bind error! Can one of you clever people help me out with this?

    I'm using Oracle Apex =]

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >AFTER INSERT OR DELETE OR UPDATE ON case
    HUH? typo?
    It is bad idea to use KEYWORD (case) as table name.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Feb 2011
    Posts
    8
    Quote Originally Posted by anacedent View Post
    >AFTER INSERT OR DELETE OR UPDATE ON case
    HUH? typo?
    It is bad idea to use KEYWORD (case) as table name.
    'Case' was the name of my table. I could try and rename it if that's causing the issue; can't help to try

  4. #4
    Join Date
    Feb 2011
    Posts
    8
    Quote Originally Posted by Forensic_Mike View Post
    'Case' was the name of my table. I could try and rename it if that's causing the issue; can't help to try
    Ok renamed it, and still the same error. The message I'm getting is:


    Error at line 26: PLS-00049: bad bind variable 'OLD.AUDIT_ID' 0.13 seconds

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    post DDL for underlying table upon which the trigger is based
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Feb 2011
    Posts
    8
    Quote Originally Posted by anacedent View Post
    post DDL for underlying table upon which the trigger is based
    CREATE TABLE "CASE_DETAILS"
    ( "CASE_ID" NUMBER(2,0) NOT NULL ENABLE,
    "ANALYST_ID" NUMBER(2,0) NOT NULL ENABLE,
    "CASE_DETAILS" VARCHAR2(4000) NOT NULL ENABLE,
    "DATE_SUBMITTED" TIMESTAMP (6) NOT NULL ENABLE,
    "CASE_STATUS" VARCHAR2(10),
    "DATE_DUE" DATE,
    CONSTRAINT "CASE_PK" PRIMARY KEY ("CASE_ID") ENABLE
    )
    /

    As requested

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >:OLD.Audit_ID
    what does this mean when AUDIT_ID does NOT exist in table CASE_DETAILS?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Feb 2011
    Posts
    8
    Quote Originally Posted by anacedent View Post
    >:OLD.Audit_ID
    what does this mean when AUDIT_ID does NOT exist in table CASE_DETAILS?
    Sorry, that was my mistake. That DDL I gave you was from the original table which currently holds values based on a police case.

    I have created an Audit_table:

    Create table audit_cases
    (Audit_ID NUMBER(22) not null,
    Audit_Analyst_ID VARCHAR2(100),
    Audit_CASE_DETAILS VARCHAR2(500),
    Audit_DATE_SUBMITTED Date,
    Audit_CASE_STATUS VARCHAR2(500),
    Audit_DATE_DUE Date,
    Audit_Case_Status
    );

    And when a change is made to CASE_DETAILS, this should be recorded in AUDIT_CASES

    My bad

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    :OLD & :NEW apply to base table columns ONLY

    when INSERT INTO AUDIT_CASE, it make NO sense to refer to :OLD.AUDIT_ID for new record!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    Feb 2011
    Posts
    8
    That was my mistake, and since then I've figured it out! After you mentioned that, I was pointing the :NEW/:OLDs to the wrong tables and attributes :P

    Needless to say, I won't make that mistake again. This newbie certainly learned something

    Thankyou

Tags for this Thread

Posting Permissions

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