Home » SQL & PL/SQL » SQL & PL/SQL » Trigger for Changes in Database (Oracle 10gR2 on Windows)
Trigger for Changes in Database [message #289312] Thu, 20 December 2007 11:50 Go to next message
skvd
Messages: 17
Registered: December 2007
Junior Member
Hi,

I am looking at to know if any changes are made to a table on the database. We pull the Oracle table and load it in the mysql database.

So is there any in built trigger or something which notifies me whenever a change is made in the table. Like shooting an email or something like that..so that I can pull the table to the mysql.

Thankis
Re: Trigger for Changes in Database [message #289313 is a reply to message #289312] Thu, 20 December 2007 11:55 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>So is there any in built trigger
NO
Do you control the application code that is doing the Oracle DML?
Re: Trigger for Changes in Database [message #289314 is a reply to message #289313] Thu, 20 December 2007 11:57 Go to previous messageGo to next message
skvd
Messages: 17
Registered: December 2007
Junior Member
No . I do not have any control on the application code that does the DML. Right now what I do, is just refresh the table everyday and compare for modifications like rows etc..

so I wanted to see if I can know where ever there are changes on the table.

Thanks for any help
Re: Trigger for Changes in Database [message #289315 is a reply to message #289312] Thu, 20 December 2007 11:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No there is nothing like that.
You can use FGA (Fine-Grained Auditing) but it is a little bit complicated.
What is your business need? Why do you want to know this? What do you need to know? Do you need to know each modification? Each row modification? When it happens or only from time to time? And so on.

Regards
Michel
Re: Trigger for Changes in Database [message #289316 is a reply to message #289315] Thu, 20 December 2007 12:00 Go to previous messageGo to next message
skvd
Messages: 17
Registered: December 2007
Junior Member
We pull the a Particular sales Oracle table into the MYSQL database to use it for another application.

So I just need to know when every there is any change in the data of the Oracle table so that I can pull the table and refresh the mysql table.
Re: Trigger for Changes in Database [message #289317 is a reply to message #289315] Thu, 20 December 2007 12:03 Go to previous messageGo to next message
skvd
Messages: 17
Registered: December 2007
Junior Member
The changes on the sales table do not occur regularly may be once in 15 days.
Re: Trigger for Changes in Database [message #289318 is a reply to message #289317] Thu, 20 December 2007 12:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can download the table on demand, empty the target table and then load it.

Regards
Michel
Re: Trigger for Changes in Database [message #289319 is a reply to message #289318] Thu, 20 December 2007 12:40 Go to previous messageGo to next message
skvd
Messages: 17
Registered: December 2007
Junior Member
That is what I am doing now. refreshing the table everyday.
The data is changed once in 15 days. So I can pull the data only when the sales data changes.

I am looking at table triggers . I was thinking to create a trigger for the table. But was wondering how I can know when the trigger has fired so that I can pull the table.

THanks
Re: Trigger for Changes in Database [message #289320 is a reply to message #289312] Thu, 20 December 2007 12:54 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>But was wondering how I can know when the trigger has fired so that I can pull the table.

UTL_SMTP
Re: Trigger for Changes in Database [message #289321 is a reply to message #289319] Thu, 20 December 2007 13:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why not add a call to sendmail in your load/update batch?
The way to do it depends on this batch.
Is this shell, Perl, C, Java, or other language custom program, PL/SQL...? Each of these can send a mail.

Regards
Michel
Re: Trigger for Changes in Database [message #289322 is a reply to message #289320] Thu, 20 December 2007 13:00 Go to previous messageGo to next message
skvd
Messages: 17
Registered: December 2007
Junior Member
Thanks for the information. and help.

I never used UTL_SMTP but will read and look at it
Re: Trigger for Changes in Database [message #289323 is a reply to message #289322] Thu, 20 December 2007 13:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Beware, a trigger will send you a mail for at least each update.
Is this what you want?
If your batch makes 1000 updates, do you want to receive 1000 mails?

Regards
Michel
Re: Trigger for Changes in Database [message #289325 is a reply to message #289323] Thu, 20 December 2007 13:07 Go to previous messageGo to next message
skvd
Messages: 17
Registered: December 2007
Junior Member
The updates are done once in a while 15 days on an average and at most 5 or 6 updates are done. So thats not a problem.

We pull the dump of the table using php.
Re: Trigger for Changes in Database [message #289326 is a reply to message #289312] Thu, 20 December 2007 13:14 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Alternatively, the trigger could just INSERT/UPDATE a field in a 'flag' table.
Periodically the PHP code would query this new table for not NULL date.
When date is not NULL then pull the data & set the date back to NULL.
Re: Trigger for Changes in Database [message #289327 is a reply to message #289326] Thu, 20 December 2007 13:36 Go to previous messageGo to next message
skvd
Messages: 17
Registered: December 2007
Junior Member
Thanks for the info. I will look at what I can do..Sorry might be a simple question but a Flag table you mentioned is that a new table that is created when ever change is made...

Thanks
Re: Trigger for Changes in Database [message #289329 is a reply to message #289312] Thu, 20 December 2007 13:39 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
In my mind, the new flag table is made once & remains in the DB "forever"; a 1 row & 1 column table.
Previous Topic: Please help, problems refining searches.
Next Topic: Logic of rewriting a Query!!!
Goto Forum:
  


Current Time: Sat Dec 10 11:12:02 CST 2016

Total time taken to generate the page: 0.20849 seconds