Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: last time a table was modified
mike7411_at_gmail.com wrote:
> What is the easiest way to maintain a time stamp variable that keeps
> track of the last time a table was modified?
>
> Is it to set up triggers that fire upon insert, update, and delete?
>
> Thank you.
Version matters. If all you want is to know is what you state which is "When last modified" then you don't need triggers and you don't need auditing: You just need 10g.
CREATE TABLE demo (
mycol VARCHAR2(20))
ROWDEPENDENCIES;
INSERT INTO demo (mycol) VALUES ('ABC');
COMMIT;
SELECT mycol, SCN_TO_TIMESTAMP(ora_rowscn)
FROM demo;
UPDATE demo
SET mycol = 'XYZ';
COMMIT;
Or you could use a Flashback Transaction Query.
All of these are documented in Morgan's Library at www.psoug.org
Using ROWDEPENDENCIES will cost you 6 bytes per row. A small price to pay.
-- Daniel A. Morgan University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Mon Aug 14 2006 - 13:26:59 CDT