30 pts.
 calling trigger in oracle db 10g
hi all!

I do have a table called customers. I want to delete and update rows in this table. But I want to keep rows' history before operations into another table. I know this will be performed with help of a trigger. How I could do this? I'm waiting for your help.

                  Thanks!



Software/Hardware used:
Software
ASKED: October 16, 2009  7:32 AM
UPDATED: October 16, 2009  6:49 PM

Answer Wiki:
One very simple approach is to create a parallel set of tables that include some tracking information columns (date/time, action [insert, update, delete], user, etc.). I have done this by creating a separate schema (e.g., "Audit"), and then having triggers that copy the affected data row plus the additional "audit" data into the secondary schema. With this approach, the tables in the second schema can have the same names as in the primary schema. Note that the secondary schema must NOT have the primary key or other constraints of the primary schema tables, as you will (probably) want to carry multiple copies of the data rows as they change over time. So, you might have: MyDb.dbo.Customers ( CustName varchar(255) not null, CustID int not null, CustAddr varchar(255), etc.) Then create: MyAuditDb.dbo.Customers ( CustName varchar(255), CustID int, CustAddr varchar(255), etc. AuditDate datetime not null, AuditAction char(1) not null, AuditActionBy <userid information> not null); And the triggers would just copy the data from the primary schema to the secondary schema with the added audit info. (Tip - use the "after" mode for the triggers - that way the secondary schema tables will contain the history of data including the latest values, so audit queries/analyses won't need to go against the primary tables at all.) after insert trigger on MyDb.dbo.Customers ... insert into MyAuditDb.dbo (CustName, CustID, CustAddr, etc., AuditDate, AuditAction, AuditActionBy) values (:new.CustName, :new.CustID, :new.CustAddr, ..., sysdate(), 'I', SYS_CONTEXT('USERENV','CURRENT_USER')); ... You can use the Oracle SYS_CONTEXT function to get various run-time environment settings, like user id or name.
Last Wiki Answer Submitted:  October 16, 2009  6:49 pm  by  Kccrosser   3,830 pts.
All Answer Wiki Contributors:  Kccrosser   3,830 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _