Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

need help ! custom auditing procedure

drovnikdrovnik Member Posts: 31
I have a generic procedure that accept several parameters in order to keep track of changes on a database.
The code below refuses to update the table audit_records.

create or replace procedure AUDIT_RECORDS

( table_name IN AUDIT_EIS_RECORDS.TABLE_NAME%TYPE,
primary_key IN AUDIT_EIS_RECORDS.PRIMARY_KEY%TYPE,
primary_key_value IN AUDIT_EIS_RECORDS.PRIMARY_KEY_VALUE%TYPE,
changes IN AUDIT_EIS_RECORDS.CHANGES%TYPE,
cuser IN AUDIT_EIS_RECORDS.CUSER%TYPE,
date_in IN AUDIT_EIS_RECORDS.CDATE_TIME%TYPE ) IS

BEGIN
INSERT INTO AUDIT_EIS_RECORDS (TABLE_NAME,
PRIMARY_KEY,
PRIMARY_KEY_VALUE,
CHANGES,
CUSER,
CDATE_TIME)
VALUES (table_name,
primary_key,
primary_key_value,
changes,
cuser,
date_in);

END AUDIT_RECORDS;


sample code for the trigger taht calls the procedure

create or replace trigger TRG_AUDIT_ALLOCATIONS
before update on allocations
for each row
declare
-- local variables here

t_table AUDIT_EIS_RECORDS.TABLE_NAME%TYPE;

begin

SELECT table_name INTO t_table
FROM user_tables
WHERE table_name LIKE 'allocations';

AUDIT_RECORDS(TABLE_NAME => t_table
,PRIMARY_KEY => 'exre_pk'
,PRIMARY_KEY_VALUE => 'key'
,CHANGES => 'newTest_value'
,CUSER => USER
,DATE_IN => SYSDATE );


end TRG_AUDIT_ALLOCATIONS;

Comments

  • infidelinfidel Member Posts: 2,900
    : I have a generic procedure that accept several parameters in order to keep track of changes on a database.
    : The code below refuses to update the table audit_records.
    : [code]
    : create or replace procedure AUDIT_RECORDS
    :
    : ( table_name IN AUDIT_EIS_RECORDS.TABLE_NAME%TYPE,
    : primary_key IN AUDIT_EIS_RECORDS.PRIMARY_KEY%TYPE,
    : primary_key_value IN AUDIT_EIS_RECORDS.PRIMARY_KEY_VALUE%TYPE,
    : changes IN AUDIT_EIS_RECORDS.CHANGES%TYPE,
    : cuser IN AUDIT_EIS_RECORDS.CUSER%TYPE,
    : date_in IN AUDIT_EIS_RECORDS.CDATE_TIME%TYPE ) IS
    :
    : BEGIN
    : INSERT INTO AUDIT_EIS_RECORDS (TABLE_NAME,
    : PRIMARY_KEY,
    : PRIMARY_KEY_VALUE,
    : CHANGES,
    : CUSER,
    : CDATE_TIME)
    : VALUES (table_name,
    : primary_key,
    : primary_key_value,
    : changes,
    : cuser,
    : date_in);
    :
    : END AUDIT_RECORDS;
    : [/code]

    Just look at the names of your arguments. They're the same as the table's columns! When you say "table_name", how is the database supposed to know whether you mean the column or the argument? The convention I use is to prefix arguments with their direction. IN, OUT, and IN/OUT arguments are prefixed with i_, o_, or io_ respectively. Saying "insert into audit_eis_records (table_name) values (i_table_name)" is unambiguous.

    :
    : sample code for the trigger taht calls the procedure
    : [code]
    : create or replace trigger TRG_AUDIT_ALLOCATIONS
    : before update on allocations
    : for each row
    : declare
    : -- local variables here
    :
    : t_table AUDIT_EIS_RECORDS.TABLE_NAME%TYPE;
    :
    : begin
    :
    : SELECT table_name INTO t_table
    : FROM user_tables
    : WHERE table_name LIKE 'allocations';
    :
    : AUDIT_RECORDS(TABLE_NAME => t_table
    : ,PRIMARY_KEY => 'exre_pk'
    : ,PRIMARY_KEY_VALUE => 'key'
    : ,CHANGES => 'newTest_value'
    : ,CUSER => USER
    : ,DATE_IN => SYSDATE );
    :
    :
    : end TRG_AUDIT_ALLOCATIONS;

    [/code]

    The only thing I see wrong with this is the waste of processing time doing a select to get the table name when you use the table name as the criteria of the query. Just do this:

    [code]
    create or replace trigger TRG_AUDIT_ALLOCATIONS
    before update on allocations
    for each row
    declare
    -- local variables here
    begin

    AUDIT_RECORDS(
    TABLE_NAME => 'allocations',
    PRIMARY_KEY => 'exre_pk',
    PRIMARY_KEY_VALUE => 'key',
    CHANGES => 'newTest_value',
    CUSER => USER,
    DATE_IN => SYSDATE
    );
    [/code]
    end TRG_AUDIT_ALLOCATIONS;



    [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]

    [code]
    $ select * from users where clue > 0
    no rows returned
    [/code]

  • drovnikdrovnik Member Posts: 31
    SELECT allo.junior_centre_no, allo.selection_year, allo.candidate_id INTO t_primary_key_value
    FROM allocations allo
    WHERE ( :OLD.ALLOCATED_SCHOOL != :NEW.ALLOCATED_SCHOOL OR
    NVL :OLD.ALLOCATION_TYPE, 'Z') != :NEW.ALLOCATION_TYPE OR
    NVL( :OLD.PLACE_TAKENUP_YN,'Z') != :NEW.PLACE_TAKENUP_YN OR
    NVL( :OLD.TRANSFERED_YN,'Z') != :NEW.TRANSFERED_YN ) ;


    /*
    The erro points to above piece of code
    tried different multiple ways to correct it;
    please help
    */
  • infidelinfidel Member Posts: 2,900
    : SELECT allo.junior_centre_no, allo.selection_year, allo.candidate_id INTO t_primary_key_value
    : FROM allocations allo
    : WHERE ( :OLD.ALLOCATED_SCHOOL != :NEW.ALLOCATED_SCHOOL OR
    : [red]NVL[/red] :OLD.ALLOCATION_TYPE, 'Z') != :NEW.ALLOCATION_TYPE OR
    : NVL( :OLD.PLACE_TAKENUP_YN,'Z') != :NEW.PLACE_TAKENUP_YN OR
    : NVL( :OLD.TRANSFERED_YN,'Z') != :NEW.TRANSFERED_YN ) ;
    :
    :
    : /*
    : The erro points to above piece of code
    : tried different multiple ways to correct it;
    : please help
    : */

    The NVL function in red is missing a left-parenthesis.


    [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]

    [code]
    $ select * from users where clue > 0
    no rows returned
    [/code]

Sign In or Register to comment.