Hi I am trying to create a log of changes in a table by using oracle triggers
The log table consist of the ,time of action ,tablename,actiontype and xmldata (clob)
Iam trying to convert the new row to xml and save it as xmldata
create or replace TRIGGER EVAL_CHANGE_TriggerActual_DYNAMIC
AFTER INSERT OR UPDATE OR DELETE
ON PROJ_TEST
REFERENCING NEW AS new OLD AS old
FOR EACH ROW
DECLARE
log_action varchar(10);
p_xmldata XMLtype;
P_NEWROWDATA clob;
p_newrowxml clob;
BEGIN
select rtrim(xmlelementcol,',') into p_newrowxml from ( Select LISTAGG(str, '') as xmlelementcol from (select 'XMLElement("'||cols.column_Name||'", :NEW.'||cols.column_name||'),' as str
from SYS.ALL_TAB_COLS cols where upper(cols.owner)=upper('DEV_CUSTOM') and upper(cols.table_name)=upper('PROJ_TEST') order by column_id ));
p_newrowxml:=CONCAT('select XMLElement("ResearchTable",',p_newrowxml);
p_newrowxml:=CONCAT(p_newrowxml,')from dual');
DBMS_OUTPUT.PUT_LINE(p_newrowxml);
EXECUTE IMMEDIATE p_newrowxml into p_xmldata;
p_newrowdata:=p_xmldata.getClobVal();
IF INSERTING THEN
log_action := 'Insert';
ELSIF UPDATING THEN
log_action := 'Update';
ELSIF DELETING THEN
log_action := 'Delete';
ELSE
DBMS_OUTPUT.PUT_LINE('This code is not reachable.');
END IF;
INSERT INTO audits(table_name, transaction_name, by_user, transaction_date,xmldata,TRIGGERNAMEdesc)
VALUES('PROJ_TEST', log_action, USER, SYSDATE,p_newrowdata,'EVAL_CHANGE_TriggerDynamic');
END;
Now if If i remove the below code
'XMLElement("'||cols.column_Name||'", :NEW.'||cols.column_name||'),'
to
'XMLElement("'||cols.column_Name||'", 1),'
Its working otherwise not thowing error at EXECUTE IMMEDIATE. Can any one help
while my first answer works, it would depend heavily on table-columns, not really usable if many tables have to be audited. Based on a comment from @Sayan Malakshinov and the linked artikle StevenFeuerstein&CompoundTrigger. the final audit-loop might cause performance-trouble if many rows are processed in one update .... mass-update could be moved to a more optimized update-compound-trigger writing all update-rows with one single "insert into tblaudit() select auditColumns from tblone where id in (recorded id´s)"
-- prepare test-scenario
CREATE TABLE TBLONE
(
ID NUMBER(19),
POSTALCODE VARCHAR2(20 CHAR),
STREET VARCHAR2(255 CHAR),
HOUSENUMBER VARCHAR2(25 CHAR),
CITY VARCHAR2(255 CHAR)
)
;
ALTER TABLE TBLONE ADD (
PRIMARY KEY
(ID)
USING INDEX);
insert into tblone values (1,'123123','street1','1a','DevVille1');
insert into tblone values (2,'345','street2','2b','DevVille2');
insert into tblone values (3,'678','street3','3c','DevVille3');
CREATE TABLE TBLAUDIT
(
ID NUMBER(19),
log_action VARCHAR2(10 CHAR),
log_user varchar(100 char),
log_data clob
)
;
-- ... timestamp of modification might be missing ...
CREATE OR REPLACE TRIGGER trg_audit_tblone
FOR UPDATE OR INSERT OR DELETE ON tblone
COMPOUND TRIGGER
TYPE id_rt IS RECORD (
id_column NUMBER(19) -- assuming all primary-keys are of same type ....
,log_action varchar(10)
);
TYPE row_level_info_t IS TABLE OF id_rt INDEX BY PLS_INTEGER;
g_row_level_info row_level_info_t;
AFTER EACH ROW IS
BEGIN
g_row_level_info (g_row_level_info.COUNT + 1).id_column := :NEW.id; -- store primary key only
-- remember the type of trigger-action
IF INSERTING THEN
g_row_level_info (g_row_level_info.COUNT).log_action := 'Insert';
ELSIF UPDATING THEN
g_row_level_info (g_row_level_info.COUNT).log_action := 'Update';
ELSIF DELETING THEN
g_row_level_info (g_row_level_info.COUNT).log_action := 'Delete';
end if;
END AFTER EACH ROW;
AFTER STATEMENT IS
l_rowdata clob;
BEGIN
-- for all row-actions recorded
FOR indx IN 1 .. g_row_level_info.COUNT
LOOP
if g_row_level_info (indx).log_action= 'Delete' then
-- no row selectable, direct insert to audit
insert into TBLAUDIT values(g_row_level_info(indx).id_column,sys_context('USERENV','SESSION_USER') , g_row_level_info (indx).log_action,null);
else
select xmltype(cursor(select * from tblone where id=g_row_level_info (indx).id_column)).getclobval() into l_rowdata from dual;
insert into TBLAUDIT values(g_row_level_info(indx).id_column,sys_context('USERENV','SESSION_USER') , g_row_level_info (indx).log_action,l_rowdata);
end if;
END LOOP;
END AFTER STATEMENT;
END trg_audit_tblone;
update tblone
set street='newStreet'
where id=1;
select id,log_action,log_user,dbms_lob.substr(log_data,200) from tblaudit;
update tblone
set street='newStreet';
select id,log_action,log_user,dbms_lob.substr(log_data,200) from tblaudit;
delete from tblone where id=2;
select id,log_action,log_user,dbms_lob.substr(log_data,200) from tblaudit;
In case update with many-rows have really to be considered:
create TYPE type_audit_entry as object (
id_column NUMBER(19) -- assuming all primary-keys are of same type ....
,log_action varchar(10)
);
create type typeTable_audit as table of type_audit_entry;
CREATE OR REPLACE TRIGGER trg_audit_update_tblone
FOR UPDATE ON tblone
COMPOUND TRIGGER
g_row_level_info typeTable_audit := typeTable_audit();
BEFORE STATEMENT IS
BEGIN
g_row_level_info:=typeTable_audit();
END BEFORE STATEMENT;
AFTER EACH ROW IS
BEGIN
g_row_level_info.extend;
g_row_level_info (g_row_level_info.COUNT):=type_audit_entry(:NEW.id,null);
END AFTER EACH ROW;
AFTER STATEMENT IS
l_rowdata clob;
BEGIN
insert into TBLAUDIT(ID,log_action,log_user,log_time,log_data)
select stored_audit.id_column,'Update',sys_context('USERENV','SESSION_USER'),sysdate,xmltype(cursor(select * from tblone where id=stored_audit.id_column)).getclobval()
from TABLE(g_row_level_info) stored_audit;
END AFTER STATEMENT;
END trg_audit_update_tblone;
delete from tblaudit;
update tblone
set street='newStreet';
select id,log_action,log_time,log_user,dbms_lob.substr(log_data,200) from tblaudit;