Hi tom,
1) My problem is ,i have 500 tables in a schema,i need to
capture old and new value for all this table(when ever any field value for any table is getting changed),and i don't
want to write a trigger for all this 500 tables seprately.Is there any way for this other then trigger ??
How about performance in this case ??

2)Is there any way with which I can capture all the update
statement for all the session.

Thanks,
Regards
Ak

1. How about SQL to gen. SQL? Something like
SELECT
'CREATE TRIGGER ON ' || t.Table_Name
...
FROM
All_Tab_Cols t
??? I know that the real query would be much better ...

Tom,

How can this be used to capture deletes on a certain condition. for eg: old.c1 like abc% then insert record to history table on delete from main table ?

Good day Tom,

Today, I faced a problem, which I haven't seen or read anything about it before! Here is the issue:

I have the following table:
D820> desc s1_vehicle_processing
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
VEHICLE_DISPOSITION_NBR NOT NULL NUMBER
CONTRACT_NBR NOT NULL NUMBER
RELEASE_NBR NOT NULL NUMBER
QUANTITY_ENTRY_NBR NOT NULL NUMBER
SCALE_QUANTITY NUMBER
REPORT_QUANTITY NUMBER
OFFICIAL_QUANTITY NUMBER
VENDOR_ID NOT NULL VARCHAR2(10)
APPLIED_SCALE_QUANTITY NUMBER
APPLIED_REPORT_QUANTITY NUMBER
SCALE_HOLD_QUANTITY NUMBER
REPORT_HOLD_QUANTITY NUMBER
APPLIED_TYPE NOT NULL CHAR(1)
QUANTITY_ADDED_TO_CONTRACT NUMBER
GRADE_GOVERN_STATUS NOT NULL NUMBER(5)
SETTLEMENT_STATUS NOT NULL CHAR(1)
SCALE_TICKET_INBOUND_OUTBOUND NOT NULL CHAR(1)
INVOICED_STORAGE_FLAG NOT NULL CHAR(1)
DRYING_FLAG NOT NULL CHAR(1)
DRYING_AMOUNT NOT NULL NUMBER(20,5)
ADVANCE_STATUS NOT NULL CHAR(1)
FREIGHT_FLAG NOT NULL CHAR(1)
FREIGHT_AMOUNT NOT NULL NUMBER(20,5)
ADD_DATE NOT NULL DATE
CHANGE_DATE NOT NULL DATE
TERM_CODE VARCHAR2(10)
DUE_DATE DATE
ADVANCE_COUNTER NOT NULL NUMBER
MARKET_ZONE VARCHAR2(20)
MAX_RECEIPT_NBR NOT NULL NUMBER(5)
INVENTORY_DOLLAR_VALUE NOT NULL NUMBER(18,6)
INVENTORY_CONTRACT_EXCH_RATE NOT NULL NUMBER(20,10)

On this table I have a BEFORE UPDATE trigger. When I issue the following command the first one works and the second one failed. Oracle Error #ora-04020

ALTER TABLE s1_vehicle_processing
ADD inventory_dollar_value number(18,6) DEFAULT 0 not null
/

ALTER TABLE s1_vehicle_processing
ADD inventory_contract_exch_rate number(20,10) DEFAULT 1 not null
/

I noticed Update trigger became Invalid. and after I recompiled the trigger, the second statement will work.

I know that because of structure changes, the trigger became invalid, but I suppose that Oracle recompile it automatically.

Could please tell me what's going on?

Thanks for your time,

no versions, no nothing.  could be a "product issue" in some older release.  I could not reproduce on 9iR2 in any case:

ops$tkyte@ORA920> create table t ( x int );

Table created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> create trigger t_trigger before update on t
  2  begin
  3          if ( 1=0 )
  4          then
  5             raise_application_error( -20000, 'x' );
  6          end if;
  7  end;
  8  /

Trigger created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> alter table t add y number(18,6) default 0 not null
  2  /

Table altered.

ops$tkyte@ORA920>
ops$tkyte@ORA920> @invalid
ops$tkyte@ORA920> break on object_type skip 1
ops$tkyte@ORA920> column status format a10
ops$tkyte@ORA920> select object_type, object_name, status
  2  from user_objects
  3  where status = 'INVALID'
  4  order by object_type, object_name
  5  /

OBJECT_TYPE        OBJECT_NAME                    STATUS
------------------ ------------------------------ ----------
TRIGGER            T_TRIGGER                      INVALID


ops$tkyte@ORA920>
ops$tkyte@ORA920> alter table t add z number(20,10) default 1 not null
  2  /

Table altered.


<b>I can say, it would be more efficient, specially with the defaults, to do that in a single statement:</b>

ops$tkyte@ORA920> alter table t add ( y number(18,6) default 0 not null, z number(20,10) default 1 not null )
  2  /

Table altered.


why make two passes after all... that'll update both columns in a single update instead of two full scans of the table...

 

hi tom,

do you think of fine grained auditing when you say that one can get the triggering statement in 9i?

Hi Tom,

Thanks millions of tons. That's right. It's a version problem. Sorry I forgot to mention that I am using 8.0.5.

Best Regards,
Arash

Tom,

In your example you used AFTER UPDATE OR DELETE, could I use BEFORE UPDATE OR DELETE?

create or replace trigger audit_that_table
after update or delete on that_table
for each row
begin
insert into history_table ( c1, c2, .., cn )
values
( :old.c1, :old.c2, ..., :old.cn );
end;
/

Tom,

Database version - 9.2.0.6

I need to capture the old values from two base tables cust and trans_tab

create or replace trigger audit_cust_table
after update or delete on cust
for each row
begin
insert into cust_history ( cust_name, cust_address,trans_id )
values
( :old.cust_name, :old.cust_address, (select max(trans_id) from trans_tab );
end;
/


can I do something like that ?.

Thanks for your help

Here is a code that will create trigger for all tables that have a table of the same name with an "%_a" table of the same structure. "%_a" is an audit table. (_audit post_fix was to long.)Give it a try.

How to:
GRANT CREATE TRIGGER TO $USER;

create table test(
col1 number,
col2 number);

create table test_a(
col1 number,
col2 number.
test_audit_msg varchar2(4000));
--test_audit_msg is optional, this tell in english what happened for the last record.
create table clob_ddl ( id number primary key, data_value clob );
exec p_trigger_generator.main;
select * from triggers; U will see a trigger for all tables with a audit table. These triggers start with trg_%


----------------------

CREATE OR REPLACE PACKAGE p_trigger_generator AS
PROCEDURE main;

PROCEDURE make_trigger(
table_name VARCHAR2);
END;
/

CREATE OR REPLACE PACKAGE BODY p_trigger_generator AS
--create table clob_ddl ( id number primary key, data_value clob )
l_cnt NUMBER DEFAULT 1;
l_clob CLOB;
l_dummy DUAL.dummy%TYPE;
l_temp LONG;
--REMEMBER, ~ on both sides of the table
delete_table_allowed VARCHAR2(4000) := '~TABLE1~TABLE2~';
--REMEMBER, ~ on both sides of the table
delete_table_handled VARCHAR2(4000) := '~~';

---------------------------------------------------------------
PROCEDURE data_append(
p_string IN VARCHAR2) AS
BEGIN
DBMS_LOB.writeappend(l_clob, LENGTH(p_string), p_string);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('data_append :' || SQLERRM);
DBMS_OUTPUT.PUT_LINE('data_append : length of p_string is : ' || TO_CHAR(LENGTH(p_string)));
-- DBMS_OUTPUT.put_line (SUBSTR (p_string, 1, 254));
-- DBMS_OUTPUT.put_line (SUBSTR (p_string, 255, 254));
-- DBMS_OUTPUT.put_line (SUBSTR (p_string, 510, 254));
RAISE;
END;

---------------------------------------------------------------
FUNCTION trigger_maker_check_nn(
ptable_name VARCHAR2)
RETURN VARCHAR2 IS
CURSOR cur_col(
ptable_name VARCHAR2) IS
SELECT *
FROM (SELECT ROWNUM row_num,
t.*,
TRUNC(ROWNUM / 5) md
FROM (SELECT LOWER(column_name) column_name,
nullable
FROM USER_TAB_COLUMNS
WHERE table_name = ptable_name
AND nullable = 'N'
AND column_name NOT LIKE '%ENTRY_IDV_CODE'
AND column_name NOT LIKE '%ENTRY_TS'
ORDER BY nullable) t);

col_list VARCHAR2(4096);
BEGIN
FOR cur IN cur_col(ptable_name) LOOP
col_list := '';
col_list :=
col_list || 'IF :old.' || cur.column_name || '<> :new.' || cur.column_name || ' THEN' || CHR(10);
col_list := col_list || 'audit_msg:=audit_msg||''' || cur.column_name || ','';' || CHR(10);
col_list := col_list || 'END IF;' || CHR(10);
data_append(NVL(col_list, ' '));
END LOOP;

-- DBMS_OUTPUT.put_line ('LENGTH (TRIM (col_list))= ' || LENGTH (TRIM (col_list)));
IF col_list IS NULL THEN
RETURN ' ';
ELSE
RETURN col_list;
-- RETURN 'IF ' || col_list || ' THEN' || CHR (10) || ' GOTO INSERT_LABEL;' || CHR (10) || 'END IF;' || CHR (10);
END IF;
EXCEPTION
WHEN OTHERS THEN
-- RETURN ptable_name || ' columns to long';
RAISE;
END;

---------------------------------------------------------------
FUNCTION trigger_maker_check_null(
ptable_name VARCHAR2)
RETURN VARCHAR2 IS
CURSOR cur_col(
ptable_name VARCHAR2) IS
SELECT *
FROM (SELECT ROWNUM row_num,
t.*,
TRUNC(ROWNUM / 5) md
FROM (SELECT LOWER(column_name) column_name,
nullable,
data_type
FROM USER_TAB_COLUMNS
WHERE table_name = ptable_name
AND nullable = 'Y'
AND column_name NOT LIKE '%ENTRY_IDV_CODE'
AND column_name NOT LIKE '%ENTRY_TS'
ORDER BY nullable) t);

col_list VARCHAR2(4096);
BEGIN
FOR cur IN cur_col(ptable_name) LOOP
col_list := '';
col_list :=
col_list
|| 'IF NOT p_util.is_same(:old.'
|| cur.column_name
|| ',:new.'
|| cur.column_name
|| ') THEN'
|| CHR(10);
col_list := col_list || 'audit_msg:=audit_msg||''' || cur.column_name || ','';' || CHR(10);
col_list := col_list || 'END IF;' || CHR(10);
data_append(NVL(col_list, ' '));
END LOOP;

-- IF col_list IS NULL THEN
-- RETURN ' ';
-- ELSE
col_list :=
CHR(10)
|| 'IF audit_msg IS NOT NULL THEN '
|| CHR(10)
|| ' GOTO INSERT_LABEL;'
|| CHR(10)
|| 'END IF;'
|| CHR(10);
data_append(NVL(col_list, ' '));
RETURN ' ';
-- END IF;
EXCEPTION
WHEN OTHERS THEN
-- RETURN ptable_name || ' columns to long';
RAISE;
END;

---------------------------------------------------------------
FUNCTION trigger_maker_insert(
ptable_name VARCHAR2,
new_old VARCHAR2)
RETURN VARCHAR2 IS
audit_msg VARCHAR2(100);

CURSOR cur_col(
ptable_name VARCHAR2) IS
SELECT LOWER(column_name) column_name,
ROWNUM row_num
FROM USER_TAB_COLUMNS
WHERE table_name = ptable_name || '_A';

ret VARCHAR2(4000);
col_list VARCHAR2(4000);
val_list VARCHAR2(4000);
BEGIN
ret := ret || 'INSERT INTO ' || ptable_name || '_a (' || CHR(10) || ' ';

FOR cur IN cur_col(ptable_name) LOOP
IF cur.row_num > 1 THEN
col_list := col_list || ',' || CHR(10) || ' ';
val_list := val_list || ',' || CHR(10) || ' ';
END IF;

col_list := col_list || cur.column_name;

IF cur.column_name LIKE '%audit_msg' THEN
val_list := val_list || 'audit_msg';
ELSE
val_list := val_list || ':' || new_old || '.' || cur.column_name;
END IF;
END LOOP;

RETURN ret || col_list || ')' || CHR(10) || ' values (' || val_list || ')';
EXCEPTION
WHEN OTHERS THEN
-- RETURN ptable_name || ' columns to long';
RAISE;
END;

---------------------------------------------------------------
FUNCTION trigger_maker_data(
ptable_name VARCHAR2)
RETURN VARCHAR2 IS
CURSOR cur_col(
ptable_name VARCHAR2) IS
SELECT LOWER(column_name) column_name,
ROWNUM row_num
FROM USER_TAB_COLUMNS
WHERE table_name = ptable_name;

ret VARCHAR2(4000);
col_list VARCHAR2(4000);
val_list VARCHAR2(4000);
BEGIN
ret := CHR(10) || ' ' || CHR(39);

FOR cur IN cur_col(ptable_name) LOOP
IF cur.row_num > 1 THEN
col_list := col_list || '||' || CHR(39) || ', ' || CHR(39) || '||' || CHR(10) || ' ';
END IF;

col_list := col_list || CHR(39) || cur.column_name || ': ' || CHR(39) || '||:new.' || cur.column_name;
END LOOP;

RETURN CHR(10)
|| ' '
|| col_list
|| CHR(10)
|| '|| '
|| CHR(39)
|| 'rowid: '
|| CHR(39)
|| '||:NEW.rowid';
EXCEPTION
WHEN OTHERS THEN
-- RETURN ptable_name || ' columns to long';
RAISE;
END;

-------------------------------------------------
PROCEDURE main AS
CURSOR cur_tab IS
SELECT *
FROM USER_TABLES
WHERE table_name NOT LIKE '%_A'
AND table_name IN(SELECT SUBSTR(table_name, 1, LENGTH(table_name) - 2)
FROM USER_TABLES
WHERE table_name LIKE '%_A')
ORDER BY 1;
BEGIN
-- p_trigger_generator.gt_clob_table_create;
DELETE FROM clob_ddl;

FOR x IN (SELECT *
FROM USER_TRIGGERS
WHERE trigger_name LIKE 'TR_%' AND trigger_name LIKE '%_A') LOOP
EXECUTE IMMEDIATE 'drop trigger ' || x.trigger_name;
END LOOP;

FOR x IN cur_tab LOOP
make_trigger(x.table_name);
END LOOP;
-- gt_clob_table_drop;
EXCEPTION
WHEN OTHERS THEN
-- RETURN ptable_name || ' columns to long';
DBMS_OUTPUT.PUT_LINE('**length of lob is : ' || TO_CHAR(DBMS_LOB.getlength(l_clob)));
RAISE;
END;

-- ---------------------------------------------------------------
-- PROCEDURE gt_clob_table_create IS
-- BEGIN
-- -- EXECUTE IMMEDIATE 'create table clob_ddl ( id number primary key, data_value clob )';
-- NULL;
-- EXCEPTION
-- WHEN OTHERS THEN
-- DBMS_OUTPUT.put_line ('sqlerrm= ' || SQLERRM);
-- -- EXECUTE IMMEDIATE 'truncate table clob_ddl';
-- END gt_clob_table_create;

---------------------------------------------------------------
-- PROCEDURE gt_clob_table_drop IS
-- BEGIN
-- -- EXECUTE IMMEDIATE 'drop table clob_ddl';
-- NULL;
-- END gt_clob_table_drop;

------------------------------------------------------------------
PROCEDURE make_trigger(
table_name VARCHAR2) AS
s1 VARCHAR2(4000);
s2 VARCHAR2(4000);
s2a VARCHAR2(4000);
s2b VARCHAR2(4000);
s3 VARCHAR2(4000);
s4 VARCHAR2(4000);
s5 VARCHAR2(4000);
s6 VARCHAR2(4000);
s7 VARCHAR2(4000);
s7a VARCHAR2(4000);
s7b VARCHAR2(4000);
delete_label BOOLEAN;
BEGIN
delete_label := FALSE;
-- p_trigger_generator.gt_clob_table_create;
DBMS_OUTPUT.PUT_LINE('Audit Trigger Installing..: ' || table_name);
s1 :=
'CREATE OR REPLACE TRIGGER TR_'
|| SUBSTR(table_name, 1, 25)
|| '_A '
|| CHR(10)
|| 'AFTER INSERT OR UPDATE OR DELETE ON '
|| table_name
|| CHR(10)
|| ' FOR EACH ROW '
|| CHR(10);
s2 := 'DECLARE ' || CHR(10) || 'audit_msg VARCHAR2 (4000); ' || CHR(10) || 'BEGIN '
-- || CHR (10);
-- || ' p_log.process('
-- || CHR (39)
-- || 'TR_'
-- || table_name
-- || '_A '
-- || CHR (39)
-- || '); '
|| CHR(10) || CHR(10);

IF INSTR(delete_table_allowed, '~' || table_name || '~') > 0 THEN
s2a :=
CHR(10)
|| CHR(10)
|| '--deletes allowed, audits handled in service...'
|| CHR(10)
-- || 'IF DELETING THEN '
-- || CHR (10)
-- || ' GOTO INSERT_LABEL;'
-- || CHR (10)
-- || 'END IF;'
|| CHR(10)
|| CHR(10);
ELSIF INSTR(delete_table_handled, '~' || table_name || '~') > 0 THEN
delete_label := TRUE;
s2a :=
'IF DELETING THEN '
|| CHR(10)
|| ' audit_msg:=''DELETE'';'
|| CHR(10)
|| ' GOTO DELETE_LABEL;'
|| CHR(10)
|| 'END IF;';
ELSE
s2a :=
'IF DELETING THEN '
|| CHR(10)
|| ' raise_application_error (-20005, '
|| CHR(39)
|| table_name
|| ' DELETE AUDIT TRIGGER : DELETE IS NOT ALLOWED ON '
|| table_name
|| ' TABLE '
|| CHR(39)
|| '); '
|| CHR(10)
|| 'END IF;';
END IF;

s2b := CHR(10) || CHR(10) || 'IF UPDATING THEN ' || CHR(10) || ' audit_msg:=''UPDATE: '';' || CHR(10);
-- s3 := TRIM (trigger_maker_check_nn (table_name));
-- s4 := TRIM (trigger_maker_check_null (table_name));
s5 :=
'end if;'
|| CHR(10)
|| CHR(10)
|| 'IF INSERTING THEN '
|| CHR(10)
|| ' audit_msg:=''INSERT'';'
|| CHR(10)
|| CHR(10)
|| ' GOTO INSERT_LABEL;'
|| CHR(10)
|| 'END IF;'
|| CHR(10)
|| CHR(10)
-- || 'p_log.process_return; '
-- || CHR (10)
|| 'return;'
|| CHR(10)
|| CHR(10)
|| '<<INSERT_LABEL>>'
|| CHR(10);
s6 := trigger_maker_insert(table_name, 'NEW') || ';' || CHR(10) || CHR(10) || 'RETURN;';

IF delete_label THEN
s7 := CHR(10) || CHR(10) || '<<DELETE_LABEL>>' || CHR(10);
s7a := trigger_maker_insert(table_name, 'OLD') || ';' || CHR(10) || CHR(10) || 'RETURN;' || CHR(10);
ELSE
s7 := CHR(10);
s7a := '';
END IF;

s7b :=
CHR(10)
|| 'EXCEPTION'
|| CHR(10)
|| CHR(10)
|| ' WHEN OTHERS THEN'
|| ' raise; '
|| CHR(10)
|| CHR(10)
|| 'END; '
|| CHR(10);

-- DBMS_OUTPUT.put_line (s1);
-- DBMS_OUTPUT.put_line (s2);
FOR y IN (SELECT s1,
s2,
s2a,
s2b,
s3,
s4,
s5,
s6,
s7,
s7a,
s7b
FROM DUAL) LOOP
EXECUTE IMMEDIATE ' begin insert into clob_ddl values ( :sqx, empty_clob() ) returning data_value into :b1; end; '
USING l_cnt, OUT l_clob;

data_append(NVL(y.s1, ' '));
data_append(NVL(y.s2, ' '));
data_append(NVL(y.s2a, ' '));
data_append(NVL(y.s2b, ' '));
s3 := TRIM(trigger_maker_check_nn(table_name));
s4 := TRIM(trigger_maker_check_null(table_name));
data_append(NVL(y.s4, ' '));
data_append(NVL(y.s5, ' '));
data_append(NVL(y.s6, ' '));
data_append(NVL(y.s7, ' '));
data_append(NVL(y.s7a, ' '));
data_append(NVL(y.s7b, ' '));

EXECUTE IMMEDIATE DBMS_LOB.SUBSTR(l_clob, 32765, 1);

l_cnt := l_cnt + 1;
END LOOP;
-- gt_clob_table_drop;
EXCEPTION
WHEN OTHERS THEN
-- RETURN ptable_name || ' columns to long';
DBMS_OUTPUT.PUT_LINE('***length of lob is : ' || TO_CHAR(DBMS_LOB.getlength(l_clob)));
RAISE;
END;
END p_trigger_generator;
/