calling trigger in oracle db 10g

30 pts.
Tags:
ASP.NET 2.0
C#
Oracle 10g
Oracle Database
Oracle Database 10g
Oracle developers
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

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following