Re: Generate a CRUD matrix

David FitzGerald wrote:

> DA Morgan <damorgan_at_exesolutions.com> wrote in message news:<3E5F96AA.3B7EC881_at_exesolutions.com>...
> s.com> wrote in message
>
> > I'd correct you if I thought you wrong ... but in this case I think different
> > people have read the request to be asking different things. It appears the OP
> > should repost and clarify.
>
> Sorry I wasn't as clear as I ought to have been -- I'm still very much
> new to Oracle, and if I try to get too detailed, sometimes I just get
> it plain wrong and confuse others even more!!
>
> > Or at least let us know who did the best job of guessing the intent.
>
> Niall Litchfield seems to have got pretty close, he said :
>
> "Correct me if I'm wrong but running a sql trace on the session and
> then
> TKPROF'ing it will give all the tables accessed during the session as
> originally requested. This is not the same as auditing, but it doesn't
> appear to be auditing that he is after."
>
> This sounds like what I am after. However, I've played around with
> tkprof and it produces huge reports! Absolutely massive. I would
> simply want something along the lines of listing which tables had
> updates performed on them, and which tables had select's performed on
> them. No details of which columns the selects were performed on, or
> what was returned.
>
> Would I need to set a trigger on each table (Am I right in thinking a
> trigger is something fired when the table is accessed?) and from this
> write out to file that a particular action has been taken on this
> table?
>
> Thanks for the continued help,
> David.

There is no way to get this from selects and I question why you would want it anyway. For inserts, updates, and deletes the following would work.

CREATE OR REPLACE TRIGGER test
AFTER INSERT OR UPDATE OR DELETE
ON test_table

DECLARE
   TabName user_tables.table_name%TYPE := test_table;

BEGIN
   IF inserting THEN

      BEGIN
         UPDATE INTO audit_table
     EXCEPTION
         WHEN OTHERS THEN
            INSERT INTO audit_table ....
     END;
   ELSIF updating THEN
      BEGIN
         UPDATE INTO audit_table
     EXCEPTION
         WHEN OTHERS THEN
            INSERT INTO audit_table ....
     END;
   ELSIF deleting THEN
      BEGIN
         UPDATE INTO audit_table
     EXCEPTION
         WHEN OTHERS THEN
            INSERT INTO audit_table ....
     END;

Note I did not include "FOR EACH ROW" as you don't seem to care how many rows are updated.

It is hardly elegant. It will hardly improve performance and scalability, and I wouldn't do it ... but it will work.

Daniel Morgan Received on Mon Mar 03 2003 - 10:23:36 CST