Solved

Triggers-Monitoring Table Data transactions

Posted on 2004-09-30
16
1,096 Views
Last Modified: 2012-05-05
Hi every1, Im absolutely and totally new in Oracle, just  4 days OLD:). Any ways I have a task to do I need your help. Here is the background, we are running on 8i, and we have an application developed(by some company) which is exclusively using this Database. All all i do is I just open the EnterPrise Management console and see the data and statistics of each table. BUT NOW I NEED SUMTHING ELSE, now I want to know and OBSERVE whenever any data is coming from the frontend (i.e the application), I need to know and monitor where the data is being saved , therefore before every insert of everytable I need a trigger to tell me that there is an Insert going to be done.

I asked this problem to sum1, he told me it can be done by triggers, I don't know where to write the trigger and how to execute it. Since Im totally anew bie, plz tell me is there any other way to monitor my table data, or if it is with triggers, plz tell me in step by step how to do it and where to write and how to save the trigger and how to execute........plz I NEED YOUR HELP GURU's........
0
Comment
Question by:VBpassion
  • 5
  • 3
  • 3
  • +2
16 Comments
 
LVL 12

Accepted Solution

by:
catchmeifuwant earned 43 total points
ID: 12188891
Looks like a very tedious process to me...why does anyone want to monitor if data is being inserted into tables?Weird !!!

There are a couple of ways to go about

1)At regular take rowcount of all the table

select count(*) from table1;
select count(*) from table2;

This can be done using a script
----
set serveroutput on size 100000

declare
v_sql varchar2(1000);
v_cnt number(10);
begin

for rec in (select table_name from all_tables)
loop
v_sql:= 'select count(*) from '||rec.table_name;
execute immediate v_sql into v_cnt;

dbms_output.put_line(rec.table_name||'-'||v_cnt);
v_cnt:=0;
end loop;
end;
/

------------------- copy paste the above code in sqlplus and execute it

2)Using triggers.

Triggers as the name suggests are triggered on some event (like before/after update/insert/delete etc).

In this case you have to create a trigger on every table and keep the audit log somewhere(presumably another table).Here's an example

-------------
SQL> create table temp(id number);

Table created.

--- Log Table
SQL> create table temp_log (tname varchar2(25), timestamp date, action char(1));

Table created.

-----Create insert trigger
SQL> create trigger trg_temp
  2  before insert on temp
  3  for each row
  4  begin
  5
  6  insert into temp_log(tname,timestamp,action)
  7  values('TEMP',sysdate,'I');
  8
  9  end;
 10  /

Trigger created.

----- Create update trigger
SQL> ed
Wrote file afiedt.buf

  1  create trigger trg_temp_up
  2  before update on temp
  3  for each row
  4  begin
  5  insert into temp_log(tname,timestamp,action)
  6  values('TEMP',sysdate,'U');
  7* end;
SQL> /

Trigger created.

SQL> select * from temp;

no rows selected


SQL> select * from temp;

no rows selected

SQL> select * from temp_log;

no rows selected

SQL> insert into temp(id) values(1);

1 row created.

SQL> select * from temp;

        ID
----------
         1

SQL> select * from temp_log;

TNAME                     TIMESTAMP A
------------------------- --------- -
TEMP                      30-SEP-04 I

SQL> update temp set id = 2;

1 row updated.

SQL> select * from temp;

        ID
----------
         2

SQL> select * from temp_log;

TNAME                     TIMESTAMP A
------------------------- --------- -
TEMP                      30-SEP-04 I
TEMP                      30-SEP-04 U

SQL> commit;

Commit complete.
-----------------
0
 
LVL 1

Author Comment

by:VBpassion
ID: 12188967
hey catchmeifucan, i had helpme on my previous question 2 days back and I accepted it......any ways your answers is really cool this time too...........bue i think kinda forgot I part...........now the trigger thing that I have told me, I have to do that each time whenever I go to the server Admin room and open the SQLPLUS and write the triggers and execute it........I dont want that , I want the triggers to be stored and saved and the oracle databse should prompt me(is is possible), it should prompt me or atleast save in the TEMP table(as u have showed me)......

MY purpose is to know where the data is being saved to which table and in which Field.....so what I'll be doing is I'll be sitting on my desktop with that application open and feeding sum dummy infor and then when I save the data from the application.........the moment the data and goin to be saved in the table ......the ORACLE tables should trigger the event and tell me that "THIS TABLE IS TO BE Inserted".....sumthing like this...................I guess now u got exactly what I want..........I don't want to go to the SQL editor and type that trigger millions of times on each table.....coz I got 19 tables.......now U don't want me to do that and write the insert trigger for each tables all the time ., I CAN DO THAT I TIME NO PROBS....
0
 
LVL 12

Expert Comment

by:catchmeifuwant
ID: 12189066
If you are trying to understand the application and the dataflow by this method..i pity you.You have a tough time ahead...

Where did all the documentation for the application go?You should be provided with that before embarking on a mission like this :-)

Anyway,coming back to the question:

Triggers are created once and they are compiled and stored in the database.So everytime you insert/update data on a table,the triggger is activated and does what it is supposed to do(like storing in a audit log table).

But initially the effort for creating the trigggers for all 19 tables - you cannot avoid it :-)

here's a work around

1) Clone all the 19 tables in your schema,with an additional column as timestamp

create table xxx_clone
as
select a.*,sysdate as timestamp,'X' as action
from xxx a      
where 1=2

This create a copy of the table xxx, as xxx_clone (with just the structure no data) and all columns from xxx + additional column called timestamp.

2)Create a triggger on each of the 19 tables that will record the audit information..

Here's a sample with the same table temp

-----------------------------

1) Create a clone table

create table temp_clone
as
select a.*,sysdate as timestamp,'X' action
from temp a
where 1=2

2)Create a trigger

create trigger trg_temp
before insert or update or delete on temp
for each row
begin

--- If updating record then insert the updated records

if updating then
insert into temp_clone(id,type,timestamp,action)
values(:new.id,:new.type,sysdate,'U');
end if;

--- If deleting record then insert the deleted records

if deleting then
insert into temp_clone(id,type,timestamp,action)
values(:old.id,:old.type,sysdate,'D');
end if;

--- If inserting record then insert the new records

if inserting then
insert into temp_clone(id,type,timestamp,action)
values(:new.id,:new.type,sysdate,'I');
end if;


end;
/


----------------------

Then when you do some specific action,note down the time and query these table against the "timestamp" field to see whats happening in your database


NOTE:This is not a foolproof method.....it's best to obtain the documentation for your application :-)
0
 
LVL 1

Author Comment

by:VBpassion
ID: 12189298
hi there catchmeifuwant, i hihgly appriciate your responses......but Im sorry to say that you still didn't get me....your answer seems to be absolutely correct...and Im ready to do and write the code for all th 19 tables but for the FIRST TIME ONLY. I dont mind doing it for the first time............but as I said I will be testing and putting the dummy data from the application for few couple of days...so do you mean to say that I will have to do all of this again and again for all 19 tables each and every day...ofcourse NOT.......tell me sumthing which I will have to do only once.....

SECONDLY, you explained me that "Triggers are created once and they are compiled and stored in the database", now as I said in totally new bie.....Kindly tell me , or plz guide me in steps HOW TO compile the TRIGGERS, WHERE to STORE and HOW TO STORE. ...Im sorry for being a pain in ass.....but plz reply and HELP me..................
0
 
LVL 1

Author Comment

by:VBpassion
ID: 12189316
HEY catchmeifuwant,.....can I have ur MSN ID.........i need to talk to plz if u don't mind...u r very helpful
0
 
LVL 6

Expert Comment

by:morphman
ID: 12189363
Create a materialized view log on the appropriate tables including new vales as follows

create materialized view log on table with rowid(column1, column2 etc...)  including new values;

Then just query the table it creates

select * from MLOG$_ALLBENS

This will show all changes updates, inserts to the table you want without having to create triggers... It will work in 8i
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Author Comment

by:VBpassion
ID: 12189398
hiiii morphman, ok once I have to tell you the same thing , plz tell me how to write this statement an where to store it in the database so that it always runs and fires when ever there is insert in the tables it gets me the result.......plz tell me in steps.....how and where to write and how to store it in the database.
0
 
LVL 6

Expert Comment

by:morphman
ID: 12189413
You just need to run it once(per table you want to monitor) through sqlplus. Once done, you can just keep checking the MLOG$ tables whenever you like... Everytime someone does an update/delete/insert it will automatically logged into the MLOG$_table.
0
 
LVL 1

Author Comment

by:VBpassion
ID: 12189440
hiiii morphman,............you statements "create materialized view log on table with rowid(column1, column2 etc...)  including new values", how will I get the new values.......and I don't want to, whenever there is an update it should do it...........
0
 
LVL 6

Assisted Solution

by:morphman
morphman earned 41 total points
ID: 12190347
All values will be retreived and placed on the MLOG$_TABLE table. You can look at whatever you wish as all activity will be recorded there.

This MLOG tables act as normal tables so you can interrogate them as and when required.
0
 
LVL 34

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 41 total points
ID: 12190349
You don't need to worry about where the triggers are stored in Oracle.  Oracle will do that for you in its SYSTEM tablespace when you run a statement like:
create trigger... on table...

You also only need to do this once per table.  You don't need to repeat the "create trigger..." statements.  Oracle will fire them reliably whenever an event (like: insert, update or delete) occurs on that table *IF* your trigger was created to detect those events and if the trigger includes the clause: "for each row".

I agree with an earlier comment, that this looks like a complicated way to do auditing and it will certainly have a performance impact on the application.  Will the users notice (and complain) about the performance impact?  Maybe, but maybe not.  This depends on what response times they are used to, how many transactions there are, how fast the CPUs and disks are, etc.

Also, if you do take this approach, be aware that you could be doubling the size of the database and doubling the volume of transactions.  You (or someone) may need to monitor space in the database and space for archived redo logs, if your database is running in archive log mode.  The time needed to recover from a disk crash by restoring a backup then applying the archived redo logs will also be longer now with a larger transaction volume.
0
 
LVL 1

Expert Comment

by:mtae
ID: 12198605
What about using LOGMINER?  Perhaps this would help?  I have not used it yet, but it would allow you to monitor the activity in the redo logs and then see what's happening to the database?
0
 
LVL 12

Expert Comment

by:catchmeifuwant
ID: 12214704
oops...sorry I wasn't around for a couple of days..looks like much has progressed...

VBPassion..it's against the EE rules to take discussions to a personal level...please continue here (which I'm sure you are doing already)

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now