Here is a joke:
Q: When is a table not a table?
A: When you’ve enabled 21CFR11 functionality in E1…
How funny was that? So, this is the case. When you enable auditing, the table becomes a view. The view is based over the tableName_ADT and audit information is written to AtableNameNoF
Here the the SQL that is run, from this you can tell exactly what is going on. This simple example activates auditing on the F43008 – a common requirement.
grant create procedure to dd910;
grant create trigger to dd910;
grant select on sys.v_$session to dd910;
grant create procedure to sy910;
grant create trigger to sy910;
grant select on sys.v_$session to sy910;
grant create procedure to testdta;
grant create trigger to testdta;
grant select on sys.v_$session to testdta;
Create System – 910NJ (this is Non Julian in the advanced options, also take off “OCM data source” in advanced options)
OCM Map F9500003, F9500004 and F9500006 to System – 910NJ
Generate above tables to System – 910NJ
Dbtemplates.exe from a fat client (this installs trigger templates into the database)
This inserts the templates into INSERT INTO DVFINDTA.F986112 NOTE – business data. You are not going to be able to activate in another environment!
P9500001 to add pathcode entries, do from fat client.
Add DV910 – enter the SY910 owner password
Then add a table setup. Choose the columns next next
It’ll create the A version of the table
Keyed columns will be audited by default, add any additional columns you want in your audit trail table.
Okay, now the interesting part – enabling auditing on a table
Choose a small table first, you’ll see the SQL it runs below – and you’ll realise why.
drop table DVFINDTA.F43008_ADT;
CREATE TABLE DVFINDTA.F43008_ADT (APDCTO NCHAR(2), APARTG NCHAR(12), APDL01 NCHAR(30), APALIM NUMBER, APRPER NUMBER, APATY NCHAR(1)) tablespace dvfindtat ; storage ( pctincrease 0 )
GRANT ALL ON DVFINDTA.F43008_ADT TO PUBLIC ;
CREATE UNIQUE INDEX DVFINDTA.F43008_ADT_0 ON DVFINDTA.F43008_ADT (APDCTO ASC, APARTG ASC, APALIM ASC, APRPER ASC, APATY ASC) tablespace dvfindtai storage ( pctincrease 0 );
ALTER TABLE DVFINDTA.F43008_ADT ADD CONSTRAINT F43008_ADT_PK PRIMARY KEY (APDCTO, APARTG, APALIM, APRPER, APATY) ;
CREATE INDEX DVFINDTA.F43008_ADT_3 ON DVFINDTA.F43008_ADT (APRPER ASC, APDCTO ASC, APARTG ASC, APATY ASC) tablespace dvfindtai storage ( pctincrease 0 );
CREATE INDEX DVFINDTA.F43008_ADT_4 ON DVFINDTA.F43008_ADT (APARTG ASC) tablespace dvfindtai storage ( pctincrease 0 );
INSERT INTO DVFINDTA.F43008_ADT (APDCTO, APARTG, APDL01, APALIM, APRPER, APATY) SELECT APDCTO, APARTG, APDL01, APALIM, APRPER, APATY FROM DVFINDTA.F43008 ;
commit;
DROP TABLE DVFINDTA.F43008;
ALTER TABLE DVFINDTA.F43008_ADT ADD (APCFRGUID NCHAR(36) DEFAULT ' ' ) ;
ALTER TABLE DVFINDTA.F43008_ADT ADD (APCFRMKEY NCHAR(15) DEFAULT ' ' ) ;
ALTER TABLE DVFINDTA.F43008_ADT ADD (APCFRPID NCHAR(10) DEFAULT ' ' ) ;
ALTER TABLE DVFINDTA.F43008_ADT ADD (APCFRUSER NCHAR(10) DEFAULT ' ' ) ;
ALTER TABLE DVFINDTA.F43008_ADT ADD (APISDELETE NCHAR(1) DEFAULT ' ' ) ;
ALTER TABLE DVFINDTA.F43008_ADT ADD (APCFRSEQN NUMBER DEFAULT 0 ) ;
--Note all of the above could take ages, if you make them “NOT NULL”, they should run a lot quicker!
CREATE VIEW DVFINDTA.F43008 AS SELECT APDCTO , APARTG , APDL01 , APALIM , APRPER , APATY FROM DVFINDTA.F43008_ADT;
GRANT ALL ON DVFINDTA.F43008 TO PUBLIC;
DROP PACKAGE DVFINDTA.F43008_CFRA_PKG;
create or replace package DVFINDTA.F43008_ADT_CFRA_PKG
as
type ridArray is table of rowid index by binary_integer;
ids ridArray;
empty ridArray;
end;
--Now we get into the trigger body
--remember grants to ROLES do not apply in trigger BODY – so you must run the specific grants to sys.v$session to the data owner. Don’t
--rely on something nice and fancy that your DBA did, it must be specific to that object.
5 triggers and a package are created
create or replace trigger DVFINDTA.F43008_CFRA_RDA
after delete on DVFINDTA.F43008_ADT
for each row
declare
guid char(36);
user char(10);
workstation char(15);
programid varchar2(48);
audittype char(1) := '1';
begin
if (:old.APISDELETE = 'Y') then
-- JDB delete
guid := :old.APCFRGUID;
if (guid = 'OW Without GUID') then
audittype := '2';
user := substr(ora_login_user, 1, 10);
workstation := substr(sys_context('userenv', 'host'), 1, 15);
select program into programid from v$session where audsid = userenv('sessionid') and user in (select user from dual);
else
audittype := '1';
user := :old.APCFRUSER;
workstation := :old.APCFRMKEY;
programid := :old.APCFRPID;
end if;
else
-- 3rd party delete
audittype := '3';
guid := 'Third Party';
user := substr(ora_login_user, 1, 10);
workstation := substr(sys_context('userenv', 'host'), 1, 15);
select program into programid from v$session where audsid = userenv('sessionid') and user in (select user from dual);
end if;
-- write audit record for delete
insert into DVFINDTA.A43008 (APCFRGUID, APDCTO ,APARTG ,APALIM ,APRPER ,APATY , APB4ORAFTR, APCFRACTION,
APDL01,
APAUDITTYPE, APCFRUSER, APCFRMKEY, APDATETIME, APCFRPID) values
(guid, :old.APDCTO ,:old.APARTG ,:old.APALIM ,:old.APRPER ,:old.APATY , 'B', '3',
:old.APDL01,
audittype, user, workstation,
to_char(sysdate,'YYYY-MM-DD HH24:MI:SS')||'.000000', substr(programid, 1, 10));
end;
create or replace trigger DVFINDTA.F43008_CFRA_RIA
before insert on DVFINDTA.F43008_ADT
for each row
declare
guid char(36);
user char(10);
workstation char(15);
programid varchar2(48);
audittype char(1) := '1';
begin
if (:new.APCFRSEQN = 1) then
-- JDB insert
guid := :new.APCFRGUID;
if (guid = 'OW Without GUID') then
audittype := '2';
user := substr(ora_login_user, 1, 10);
workstation := substr(sys_context('userenv', 'host'), 1, 15);
select program into programid from v$session where audsid = userenv('sessionid') and user in (select user from dual);
:new.APCFRUSER := user;
:new.APCFRMKEY := workstation;
:new.APCFRPID := substr(programid, 1, 10);
else
audittype := '1';
user := :new.APCFRUSER;
workstation := :new.APCFRMKEY;
programid := :new.APCFRPID;
end if;
else
-- 3rd party insert
audittype := '3';
guid := 'Third Party';
user := substr(ora_login_user, 1, 10);
workstation := substr(sys_context('userenv', 'host'), 1, 15);
select program into programid from v$session where audsid = userenv('sessionid') and user in (select user from dual);
:new.APCFRGUID := guid;
:new.APCFRUSER := user;
:new.APCFRMKEY := workstation;
:new.APCFRPID := substr(programid, 1, 10);
end if;
-- write audit record for insert
insert into DVFINDTA.A43008 (APCFRGUID, APDCTO ,APARTG ,APALIM ,APRPER ,APATY , APB4ORAFTR, APCFRACTION,
APDL01,
APAUDITTYPE, APCFRUSER, APCFRMKEY, APDATETIME, APCFRPID) values
(guid, :new.APDCTO ,:new.APARTG ,:new.APALIM ,:new.APRPER ,:new.APATY , 'A', '1',
:new.APDL01,
audittype, user, workstation,
to_char(sysdate,'YYYY-MM-DD HH24:MI:SS')||'.000000', substr(programid, 1, 10));
end;
create or replace trigger DVFINDTA.F43008_CFRA_RUA
before update of APDCTO ,APARTG ,APALIM ,APRPER ,APATY , APDL01, APISDELETE on DVFINDTA.F43008_ADT
for each row
declare
guid char(36);
user char(10);
workstation char(15);
programid varchar2(48);
audittype char(1) := '1';
begin
if (:new.APISDELETE = 'Y') then
-- JDB delete
-- add this rowid to the list
DVFINDTA.F43008_ADT_CFRA_PKG.ids(DVFINDTA.F43008_ADT_CFRA_PKG.ids.count+1) := :new.rowid;
else
-- Don't log any thing if the audited columns did not change.
-- As per the requirements of the FDA CFR project.
if (:old.APDCTO <> :new.APDCTO or :old.APARTG <> :new.APARTG or :old.APALIM <> :new.APALIM or :old.APRPER <> :new.APRPER or :old.APATY <> :new.APATY )
or (:old.APDL01 <> :new.APDL01)
or (:old.APDL01 is null and :new.APDL01 is not null)
or (:old.APDL01 is not null and :new.APDL01 is null)
then
-- update, not delete
if (:new.APCFRSEQN = :old.APCFRSEQN) then
-- 3rd party update
audittype := '3';
guid := 'Third Party';
user := substr(ora_login_user, 1, 10);
workstation := substr(sys_context('userenv', 'host'), 1, 15);
select program into programid from v$session where audsid = userenv('sessionid') and user in (select user from dual);
:new.APCFRGUID := guid;
:new.APCFRUSER := user;
:new.APCFRMKEY := workstation;
:new.APCFRPID := substr(programid, 1, 10);
else
-- JDB update
guid := :new.APCFRGUID;
if (guid = 'OW Without GUID') then
audittype := '2';
user := substr(ora_login_user, 1, 10);
workstation := substr(sys_context('userenv', 'host'), 1, 15);
select program into programid from v$session where audsid = userenv('sessionid') and user in (select user from dual);
:new.APCFRUSER := user;
:new.APCFRMKEY := workstation;
:new.APCFRPID := substr(programid, 1, 10);
else
audittype := '1';
user := :new.APCFRUSER;
workstation := :new.APCFRMKEY;
programid := :new.APCFRPID;
end if;
end if;
-- write audit record for update - "before" data
insert into DVFINDTA.A43008 (APCFRGUID, APDCTO ,APARTG ,APALIM ,APRPER ,APATY ,APB4ORAFTR, APCFRACTION,
APDL01,
APAUDITTYPE, APCFRUSER,APCFRMKEY, APDATETIME, APCFRPID) values
(guid, :old.APDCTO ,:old.APARTG ,:old.APALIM ,:old.APRPER ,:old.APATY , 'B', '2',
:old.APDL01,
audittype,:old.APCFRUSER, :old.APCFRMKEY,
to_char(sysdate,'YYYY-MM-DD HH24:MI:SS')||'.000000', :old.APCFRPID);
-- write audit record for update - "after" data
insert into DVFINDTA.A43008 (APCFRGUID, APDCTO ,APARTG ,APALIM ,APRPER ,APATY ,APB4ORAFTR, APCFRACTION,
APDL01,
APAUDITTYPE, APCFRUSER,APCFRMKEY, APDATETIME, APCFRPID) values
(guid, :new.APDCTO ,:new.APARTG ,:new.APALIM ,:new.APRPER ,:new.APATY , 'A', '2',
:new.APDL01,
audittype, user, workstation,
to_char(sysdate,'YYYY-MM-DD HH24:MI:SS')||'.000000', substr(programid, 1, 10));
end if;
end if;
end;
create or replace trigger DVFINDTA.F43008_CFRA_SUA
after update on DVFINDTA.F43008_ADT
begin
-- JDB delete rows in list
for num in 1 .. DVFINDTA.F43008_ADT_CFRA_PKG.ids.count loop
delete from DVFINDTA.F43008_ADT where rowid = DVFINDTA.F43008_ADT_CFRA_PKG.ids(num);
end loop;
end;
create or replace trigger DVFINDTA.F43008_CFRA_SUB
before update on DVFINDTA.F43008_ADT
begin
-- clear the list of rowids
DVFINDTA.F43008_ADT_CFRA_PKG.ids := DVFINDTA.F43008_ADT_CFRA_PKG.empty;
end;
To Copy the audit information to the J environments… or any other environments… Log into the J environment
P9500003
Copy
Open F9500001 in UTB to verify
Remember that the table needs to be checked in to come up in the list.
