| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: tigger to store info on change
In article <e6bb5a8d.0209190302.1dedf749_at_posting.google.com>, jriker1_at_yahoo.com
says...
>
>I am working on some audit trail stuff, and was requested to do the
>following:
>
>1. When a new entry is placed in any table, log that it's an insert,
>the column names in the table that the insert is on, and the values
>being put in. Log this to a table say called AUDIT.
>
>2. When an update is done, log that it's an update, the column names
>in the table that is having the data updated, and the ORIGINAL values
>before the update to the AUDIT table.
>
>3. Same with deleting.
>
>Now I think in step two it should log the new value since the original
>one is already in the audit log, but it is felt by others that they
>can look at the actual table the data is stored in and retrieve the
>current value, so the original one is more relevant. With that said,
>does anyone have a sample of a trigger I can put on all my tables to
>accomplish the above?
>
>Figured I would have the AUDIT table with a auto-increment primary
>key, a column that says if it was an insert/update/delete, one to
>store the column names and data(or maybe two seperate for this part).
>Any help would be appreciated in accomplishing this. Thanks.
>
>JR
another approach would be to let the DB do the work for you. In 9i we added "workspace mgmt" which has as a side effect -- the ability to do this. Consider (if I recall, you could download this from otn for 817 as well):
ops$tkyte_at_ORA920.LOCALHOST> CREATE USER wsmgmt IDENTIFIED BY wsmgmt; User created.
ops$tkyte_at_ORA920.LOCALHOST>
ops$tkyte_at_ORA920.LOCALHOST> GRANT connect, resource, create table to wsmgmt;
Grant succeeded.
ops$tkyte_at_ORA920.LOCALHOST>
ops$tkyte_at_ORA920.LOCALHOST> begin
2 DBMS_WM.GrantSystemPriv
3 ('ACCESS_ANY_WORKSPACE, MERGE_ANY_WORKSPACE, ' ||
4 'CREATE_ANY_WORKSPACE, REMOVE_ANY_WORKSPACE, ' ||
5 'ROLLBACK_ANY_WORKSPACE', 'WSMGMT', 'YES');
6 end;
PL/SQL procedure successfully completed.
ops$tkyte_at_ORA920.LOCALHOST> ops$tkyte_at_ORA920.LOCALHOST> @connect wsmgmt/wsmgmt ops$tkyte_at_ORA920.LOCALHOST> set termout off wsmgmt_at_ORA920.LOCALHOST> wsmgmt_at_ORA920.LOCALHOST> set termout on
Table created.
wsmgmt_at_ORA920.LOCALHOST> alter table emp add constraint emp_pk primary key(empno);
Table altered.
wsmgmt_at_ORA920.LOCALHOST>
wsmgmt_at_ORA920.LOCALHOST> begin
2 DBMS_WM.EnableVersioning ('emp', 'VIEW_WO_OVERWRITE');
3 end;
4 /
PL/SQL procedure successfully completed.
wsmgmt_at_ORA920.LOCALHOST>
wsmgmt_at_ORA920.LOCALHOST> update emp set sal = sal * 1.10 where ename = 'KING';
1 row updated.
wsmgmt_at_ORA920.LOCALHOST> commit;
Commit complete.
wsmgmt_at_ORA920.LOCALHOST>
wsmgmt_at_ORA920.LOCALHOST> insert into emp
(empno,ename,job,mgr,hiredate,sal,comm,deptno)
2 values (1234,'NEW','BOSS',null,sysdate,1000,100,10);
1 row created.
wsmgmt_at_ORA920.LOCALHOST> commit;
Commit complete.
wsmgmt_at_ORA920.LOCALHOST> delete from emp where ename = 'BLAKE';
1 row deleted.
wsmgmt_at_ORA920.LOCALHOST> commit;
Commit complete.
wsmgmt_at_ORA920.LOCALHOST>
wsmgmt_at_ORA920.LOCALHOST> select ename, sal, type_of_change,
2 to_char(createtime,'dd-mon hh24:mi:ss') created, 3 to_char(retiretime,'dd-mon hh24:mi:ss') retired4 from emp_hist
ENAME SAL T CREATED RETIRED
---------- ---------- - --------------- --------------- NEW 1000 I 18-sep 07:36:46 SMITH 800 I 18-sep 07:36:43 ALLEN 1600 I 18-sep 07:36:43 WARD 1250 I 18-sep 07:36:43 JONES 2975 I 18-sep 07:36:43 MARTIN 1250 I 18-sep 07:36:43 BLAKE 2850 D 18-sep 07:36:46 BLAKE 2850 I 18-sep 07:36:43 18-sep 07:36:46 CLARK 2450 I 18-sep 07:36:43 SCOTT 3000 I 18-sep 07:36:43 KING 5000 I 18-sep 07:36:43 18-sep 07:36:46 KING 5500 U 18-sep 07:36:46 TURNER 1500 I 18-sep 07:36:43 ADAMS 1100 I 18-sep 07:36:43 JAMES 950 I 18-sep 07:36:43 FORD 3000 I 18-sep 07:36:43 MILLER 1300 I 18-sep 07:36:43
17 rows selected.
wsmgmt_at_ORA920.LOCALHOST> select ename, sal, comm
2 from emp
3 where ename in( 'KING', 'NEW' )
4 /
ENAME SAL COMM
---------- ---------- ----------
KING 5500 NEW 1000 100
it can do that sort of "auditing" for you. Just food for thought.
-- Thomas Kyte (tkyte@oracle.com) http://asktom.oracle.com/ Expert one on one Oracle, programming techniques and solutions for Oracle. http://www.amazon.com/exec/obidos/ASIN/1861004826/ Opinions are mine and do not necessarily reflect those of Oracle CorpReceived on Thu Sep 19 2002 - 07:47:15 CDT
 |
 |