1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37
|
CREATE OR REPLACE TRIGGER DEV.AUDITTRAILBONDS
BEFORE UPDATE ON DEV.BONDS
REFERENCING NEW AS NEWREC OLD AS OLDREC
FOR EACH ROW
DECLARE
m_SeqAuditTrail NUMBER;
m_ColumnName VARCHAR(50);
CURSOR p_BondsColumns IS
SELECT COLUMN_NAME
FROM ALL_TAB_COLUMNS
WHERE OWNER='DEV' AND TABLE_NAME = 'BONDS';
BEGIN
FOR CurrentColumn IN p_BondsColumns LOOP
m_ColumnName := CurrentColumn.COLUMN_NAME;
IF :OLDREC.m_ColumnName <> :NEWREC.m_ColumnName THEN
SELECT SEQ_AUDITTRAIL.NEXTVAL INTO m_SeqAuditTrail FROM DUAL;
INSERT INTO DEV.AUDITTRAIL (AUDITTRAIL_ID, USER_ID, AUDITTRAIL_TABLE,
AUDITTRAIL_FIELD, AUDITTRAIL_OLDVALUE, AUDITTRAIL_NEWVALUE,
AUDITTRAIL_DATETIME, AUDITTRAIL_PK_VALUE, AUDITTRAIL_LASTMODIFICATION,
AUDITTRAIL_RECSTATUS, AUDITTRAIL_USERMODIF)
VALUES (m_SeqAuditTrail, 0, 'BONDS',
m_ColumnName, :OLDREC.m_ColumnName, :NEWREC.m_ColumnName,
sysdate, 'BONDS_ID = ' || :OLDREC.m_ColumnName, sysdate,
'ACT', 0);
END IF;
END LOOP;
END; |
Partager