CODEWITHSUNDEEP
sqloracle-databasetriggersprocedure
user3352615
user3352615

Reputation: 119

Created triggers but not able to detect the update

Hi I am trying to create audit table which will insert the row when their is change in the trigger table.

TRIGGER:

create or replace TRIGGER ABC
AFTER
  UPDATE ON TABLE1
  FOR EACH ROW DECLARE 
  DB_USER VARCHAR2(100);
  OS_USER    VARCHAR2(100);
  IP_ADDRESS VARCHAR2(100);
  BEGIN
    SELECT USER INTO DB_USER FROM DUAL;
    SELECT SYS_CONTEXT('USERENV', 'OS_USER') INTO OS_USER FROM DUAL;
    SELECT SYS_CONTEXT('USERENV','IP_ADDRESS')INTO IP_ADDRESS FROM DUAL;
    IF UPDATING('NAME'|| 

    'NOTES' ) THEN

   ABC_PROC(:NEW.ID,:OLD.NAME,:NEW.NAME,:OLD.NOTES ,:NEW.NOTES 
 ,DB_USER, OS_USER,IP_ADDRESS);

   END IF;
  END;

PROCEDURE:

create or replace PROCEDURE ABC_PROC

  ( 
  ID IN NUMBER,
  OLD_NAME IN VARCHAR2,
  NEW_NAME IN VARCHAR2,
  OLD_NOTES IN VARCHAR2,
  NEW_NOTES IN VARCHAR2,
    DB_USER IN VARCHAR2,
    OS_USER IN VARCHAR2,
    IP_ADDRESS IN VARCHAR2
   ) AS
BEGIN

    IF ( OLD_NAME!= NEW_NAME )  or

        ( OLD_NOTES  != NEW_NOTES ) 
         THEN

  INSERT INTO "AUDIT_TABLE"(
    ID,
        OLD_NAME ,NEW_NAME ,

  OLD_NOTES ,NEW_NOTES ,
DBUSER,OSUSER,IP_ADDRESS)
VALUES
(
 ID,
        OLD_NAME ,NEW_NAME ,
  OLD_NOTES ,NEW_NOTES,
DB_USER, OS_USER,IP_ADDRESS
);
  END IF;

Exception
        when VALUE_ERROR then
            DBMS_OUTPUT.PUT_LINE('VALUE ERROR');
       WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('OTHERS SQLCODE:'||SQLCODE||', SQLERRM:'||SQLERRM);

END ABC_PROC;

AUDIT_TABLE: 

  CREATE TABLE "XCHANGE"."AUDIT_TABLE" 
   (    "ID" NUMBER(19,0) NOT NULL ENABLE, 
    "OLD_NAME" VARCHAR2(100 BYTE), 
    "NEW_NAME" VARCHAR2(100 BYTE), 
    "OLD_NOTES" VARCHAR2(100 BYTE), 
    "NEW_NOTES" VARCHAR2(100 BYTE), 
    "DBUSER" VARCHAR2(100 BYTE), 
    "OSUSER" VARCHAR2(100 BYTE), 
    "IP_ADDRESS" VARCHAR2(100 BYTE)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;

Audit table , Trigger and Procedure all are created successfully but when i am updating the table it is not able to insert the change.

Upvotes: 0

Views: 77

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

Your trigger includes this:

IF UPDATING('NAME'|| 'NOTES') THEN

In Oracle (and most SQL) the || is the concatenation operator, not the logical OR. So your insert would only happen if you were updating a column called NAMENOTES, rather than if you were updating a column called NAME or a column called NOTES, as you presumably intended.

Instead do:

IF UPDATING('NAME') OR UPDATING('NOTES') THEN

Incidentally, there doesn't seem much point having the local variables, you can just pass USER, SYS_CONTEXT('USERENV', 'OS_USER') etc. directly into the procedure call. No need to have the selects from dual.

If you want your audit record to only show the values that actually changed, you need some logic in the values clause, which you can do with a case expression. I'd recommend you change your procedure parameter names so they don't match the column names to avoid confusion, e.g. with a P_ prefix (though some people prefer to just explicitly use the table/procedure name to identify where each is coming from instead):

CREATE OR REPLACE PROCEDURE ABC_PROC ( 
  P_ID IN AUDIT_TABLE.ID%TYPE,
  P_OLD_NAME IN AUDIT_TABLE.OLD_NAME%TYPE,
  P_NEW_NAME IN AUDIT_TABLE.NEW_NAME%TYPE,
  P_OLD_NOTES IN AUDIT_TABLE.OLD_NOTES%TYPE,
  P_NEW_NOTES IN AUDIT_TABLE.NEW_NOTES%TYPE,
  P_DB_USER IN AUDIT_TABLE.DB_USER%TYPE,
  P_OS_USER IN AUDIT_TABLE.OS_USER%TYPE,
  P_IP_ADDRESS IN AUDIT_TABLE.IP_ADDRESS%TYPE
) AS
BEGIN
  IF (P_OLD_NAME != p_NEW_NAME) or (P_OLD_NOTES != P_NEW_NOTES) THEN
    INSERT INTO AUDIT_TABLE (ID, OLD_NAME, NEW_NAME, OLD_NOTES, NEW_NOTES,
      DBUSER, OSUSER, IP_ADDRESS)
    VALUES (P_ID,
      CASE WHEN P_OLD_NAME != P_NEW_NAME THEN P_OLD_NAME END,
      CASE WHEN P_OLD_NAME != P_NEW_NAME THEN P_NEW_NAME END,
      CASE WHEN P_OLD_NOTES != P_NEW_NOTES THEN P_OLD_NOTES END,
      CASE WHEN P_OLD_NOTES != P_NEW_NOTES THEN P_NEW_NOTES END,
      P_DB_USER, P_OS_USER, P_IP_ADDRESS);
  END IF;
END ABC_PROC;

Your exception handlers will only show anything if the client that does the update happens to have output enabled, which you can't rely on; and squashing/hiding any errors, especially when others, really isn't a good idea. You'll realise one day you don't have audit records but have no idea why.

I'm not sure why you have a procedure here at all though, it would be simpler to just do the insert directly from the trigger. If you plan to call the procedure form somewhere else too - can't imagine why - you could get the user and context values within the procedure rather than passing them around. The comparison logic you have also won't catch a value changing from null to non-null, or vice versa, but they may be not-null columns in the main table anyway.

Upvotes: 2

Related Questions