Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2015
    Posts
    1

    Unanswered: Compilation Errors for Trigger

    Hi Team,
    I am facing issues while compiling below trigger.

    Please help me out

    CREATE OR REPLACE TRIGGER barc_channel_test_trigger
    AFTER
    INSERT OR
    UPDATE OR
    DELETE
    ON mst_channel_test
    FOR EACH ROW
    DECLARE
    key audit_mst_channel_test.CHANNEL_KEY%TYPE;
    name audit_mst_channel_test.CHANNEL_NAME%TYPE;
    type audit_mst_channel_test.CHANNEL_TYPE%TYPE;
    desc1 audit_mst_channel_test.CHANNEL_DESC%TYPE;
    displayname audit_mst_channel_test.CHANNEL_DISPLAY_NAME%TYPE;
    source1 audit_mst_channel_test.SOURCE%TYPE;
    isactive audit_mst_channel_test.IS_ACTIVE%TYPE;
    createdt audit_mst_channel_test.CREATE_DT%TYPE;
    lastupddt audit_mst_channel_test.LAST_UPD_DT%TYPE;
    --modifiedby audit_mst_channel_test.MODIFIED_BY%TYPE;
    --modifieddt audit_mst_channel_test.MODIFIED_DT%TYPE;
    dmltyp audit_mst_channel_test.DML_TYPE%TYPE;
    BEGIN
    key := :NEW.CHANNEL_KEY;
    name := :NEW.CHANNEL_NAME;
    type := :NEW.CHANNEL_TYPE;
    desc1 := :NEW.CHANNEL_DESC;
    displayname := :NEW.CHANNEL_DISPLAY_NAME;
    source1 := :NEW.SOURCE;
    isactive := :NEW.IS_ACTIVE;
    createdt := :NEW.CREATE_DT;
    lastupddt := :NEW.LAST_UPD_DT;
    --modifiedby := :NEW.MODIFIED_BY;
    --modiieddt := :NEW.MODIFIED_DT;
    --dmltyp := :NEW.DML_TYPE;

    IF INSERTING THEN
    dmltyp := 'Insert';
    INSERT INTO AUDIT_MST_CHANNEL_test(CHANNEL_KEY , CHANNEL_NAME , CHANNEL_TYPE , CHANNEL_DESC , CHANNEL_DISPLAY_NAME , SOURCE , IS_ACTIVE ,CREATE_DT , LAST_UPD_DT , DML_TYPE)
    VALUES (key, name, type, desc1, displayname, source1, isactive, to_char(createdt, 'DD-MON-YY'), to_char(lastupddt , 'DD-MON-YY'), dmltyp);

    ELSIF UPDATING THEN
    dmltyp := 'Update';
    update audit_mst_channel_test set CHANNEL_TYPE=type,CHANNEL_DESC=desc1,CHANNEL_DISPL AY_NAME=displayname,SOURCE=source1 , IS_ACTIVE=isactive,CREATE_DT=to_char(createdt, 'DD-MON-YY'),LAST_UPD_DT=TO_CHAR(lastupddt, 'DD-MON-YY'),
    MODIFIED_DT=TO_CHAR(sysdate, 'DD-MON-YY'),DML_TYPE='UPDATE' WHERE CHANNEL_KEY=key and CHANNEL_NAME=name;

    ELSIF DELETING THEN
    key := :OLD.CHANNEL_KEY;
    dmltyp := 'Delete';
    update audit_mst_channel_test set DML_TYPE='DELETE' WHERE CHANNEL_KEY=key and CHANNEL_NAME=name;
    ELSE
    DBMS_OUTPUT.PUT_LINE('This code is not reachable.');
    END IF;
    END barc_channel_test_trigger;
    /

    COMMIT;

    Please see the table structures as below

    Main Table:"MST_CHANNEL"

    CREATE TABLE "MST_CHANNEL"
    ( "CHANNEL_KEY" NUMBER,
    "CHANNEL_ID" VARCHAR2(20 BYTE),
    "CHANNEL_NAME" VARCHAR2(100 BYTE),
    "CHANNEL_TYPE" VARCHAR2(20 BYTE),
    "CHANNEL_DESC" VARCHAR2(200 BYTE),
    "CHANNEL_DISPLAY_NAME" VARCHAR2(100 BYTE),
    "SOURCE" VARCHAR2(20 BYTE),
    "IS_ACTIVE" CHAR(1 BYTE),
    "CREATE_DT" DATE,
    "LAST_UPD_DT" DATE
    ) ;

    Audit Table:"AUDIT_MST_CHANNEL"

    CREATE TABLE "AUDIT_MST_CHANNEL"
    ( "CHANNEL_KEY" NUMBER,
    "CHANNEL_NAME" VARCHAR2(100 BYTE),
    "CHANNEL_TYPE" VARCHAR2(20 BYTE),
    "CHANNEL_DESC" VARCHAR2(200 BYTE),
    "CHANNEL_DISPLAY_NAME" VARCHAR2(100 BYTE),
    "SOURCE" VARCHAR2(20 BYTE),
    "IS_ACTIVE" CHAR(1 BYTE),
    "CREATE_DT" DATE,
    "LAST_UPD_DT" DATE,
    "MODIFIED_BY" VARCHAR2(20 BYTE),
    "MODIFIED_DT" DATE,
    "DML_TYPE" VARCHAR2(10 BYTE)
    );

    I want to capture the Insert/update/delete operation on main table into audit table.

    Thanks,
    Vara Prasad

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >ON mst_channel_test

    you did not provide DDL for table MST_CHANNEL_TEST

    it is poor programming to use keywords/reserved words (key, name, view) as variable names.
    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.

Posting Permissions

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