basirana
asked on
Audit Changes using triggers
Hi
I have written trigger to audit changes for example if the user changes information.
Update or Delete or Insert I want to put the changes in Audit table.
The AUDIT_TABLE(ID, USERNAME, DESCIPTION (saves the changes), CHANGE_TYPE(insert, delete, update)
The table I am auditing contains 16 columns.
So everytime some update is done we will save the changes to audit.
I am trying something like below
if(old:col_name != new:col_name) then
-- save changes to Audit table
..
..
end if
the problem here is I have 16 columns every column need to be checked. the code really messed up. it does not look good...
Is there any predefine function in Oracle which will chack changes and save them to audit. OR
Is there any other way we can audit changes.
Please help me with examples
Thanks
I have written trigger to audit changes for example if the user changes information.
Update or Delete or Insert I want to put the changes in Audit table.
The AUDIT_TABLE(ID, USERNAME, DESCIPTION (saves the changes), CHANGE_TYPE(insert, delete, update)
The table I am auditing contains 16 columns.
So everytime some update is done we will save the changes to audit.
I am trying something like below
if(old:col_name != new:col_name) then
-- save changes to Audit table
..
..
end if
the problem here is I have 16 columns every column need to be checked. the code really messed up. it does not look good...
Is there any predefine function in Oracle which will chack changes and save them to audit. OR
Is there any other way we can audit changes.
Please help me with examples
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
"Is there any predefined function in Oracle which will check changes and save them to audit.?"
No.
"Is there any other way we can audit changes?".
Yes.
"what does "REFERENCING OLD AS OLD NEW AS NEW" does mean?
This is an optionally line in triggers that you do not need (and in fact this is the default functionality even if you do not include this line). You really only need a line like this in your trigger(s) if you have a schema name or or table name that is either "OLD" or "NEW", then you might want it to look like this (assuming your tables name was "old"):
REFERENCING OLD AS PRIOR NEW AS NEW
FOR EACH ROW
BEGIN
INSERT INTO TBL_HISTO VALUES (:PRIOR.col1,:PRIOR.col2,:
END;
No.
"Is there any other way we can audit changes?".
Yes.
"what does "REFERENCING OLD AS OLD NEW AS NEW" does mean?
This is an optionally line in triggers that you do not need (and in fact this is the default functionality even if you do not include this line). You really only need a line like this in your trigger(s) if you have a schema name or or table name that is either "OLD" or "NEW", then you might want it to look like this (assuming your tables name was "old"):
REFERENCING OLD AS PRIOR NEW AS NEW
FOR EACH ROW
BEGIN
INSERT INTO TBL_HISTO VALUES (:PRIOR.col1,:PRIOR.col2,:
END;
REFERENCING OLD AS OLD NEW AS NEW refers to the before and after images of the statement that is being run.
Think of old and new as bind variables that point to records of the rows in the table. The old record has all the values before the update or delete and the new record has the values after the insert or update. Note that the old record is null for inserts and the new record is null for deletes.
Think of old and new as bind variables that point to records of the rows in the table. The old record has all the values before the update or delete and the new record has the values after the insert or update. Note that the old record is null for inserts and the new record is null for deletes.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I agree that auditing inserts is not recommended, but where I am now does not understand the concept of a history table and also cannot handle the concept of joining the two together. This obviously duplicates the current record in both tables, which they do not understand either. I have offered to create a view for them to see the data the way they want, but that doesn't fly either.
I noticed that markgeer's last solution is not auditing deletes. It may be a table that is never deleted, but that is something that is a must have. You need to know who deleted it.
I noticed that markgeer's last solution is not auditing deletes. It may be a table that is never deleted, but that is something that is a must have. You need to know who deleted it.
To johnsone:
You apparently missed this part of my previous comment: "if deletes are blocked or not allowed, you don't need to audit deletes either. But if deletes are allowed in your database and/or application, you will want to audit them".
We don't allow deletes from our table that we audit like this. But, it would be easy to add a pre-delete trigger that copies the entire record into the audit table along with the username and timestamp.
You apparently missed this part of my previous comment: "if deletes are blocked or not allowed, you don't need to audit deletes either. But if deletes are allowed in your database and/or application, you will want to audit them".
We don't allow deletes from our table that we audit like this. But, it would be easy to add a pre-delete trigger that copies the entire record into the audit table along with the username and timestamp.
I read your entire post and I missed that. I'm sorry.
ASKER
Thanks for your reply