OraFAQ Forum: SQL & PL/SQL » Trigger for Changes in Database

Home » SQL & PL/SQL » SQL & PL/SQL » Trigger for Changes in Database (Oracle 10gR2 on Windows)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Trigger for Changes in Database [message #289312]

Thu, 20 December 2007 11:50

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

Report message to a moderator

Re: Trigger for Changes in Database [message #289313 is a reply to message #289312]

Thu, 20 December 2007 11:55

BlackSwan
Messages: 26766
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?

Report message to a moderator

Re: Trigger for Changes in Database [message #289314 is a reply to message #289313]

Thu, 20 December 2007 11:57

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

Report message to a moderator

Re: Trigger for Changes in Database [message #289315 is a reply to message #289312]

Thu, 20 December 2007 11:57

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Trigger for Changes in Database [message #289316 is a reply to message #289315]

Thu, 20 December 2007 12:00

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.

Report message to a moderator

Re: Trigger for Changes in Database [message #289317 is a reply to message #289315]

Thu, 20 December 2007 12:03

skvd
Messages: 17
Registered: December 2007

Junior Member

The changes on the sales table do not occur regularly may be once in 15 days.

Report message to a moderator

Re: Trigger for Changes in Database [message #289318 is a reply to message #289317]

Thu, 20 December 2007 12:27

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You can download the table on demand, empty the target table and then load it.

Regards
Michel

Report message to a moderator

Re: Trigger for Changes in Database [message #289319 is a reply to message #289318]

Thu, 20 December 2007 12:40

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

Report message to a moderator

Re: Trigger for Changes in Database [message #289320 is a reply to message #289312]

Thu, 20 December 2007 12:54

BlackSwan
Messages: 26766
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

Report message to a moderator

Re: Trigger for Changes in Database [message #289321 is a reply to message #289319]

Thu, 20 December 2007 13:00

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Trigger for Changes in Database [message #289322 is a reply to message #289320]

Thu, 20 December 2007 13:00

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

Report message to a moderator

Re: Trigger for Changes in Database [message #289323 is a reply to message #289322]

Thu, 20 December 2007 13:02

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Trigger for Changes in Database [message #289325 is a reply to message #289323]

Thu, 20 December 2007 13:07

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.

Report message to a moderator

Re: Trigger for Changes in Database [message #289326 is a reply to message #289312]

Thu, 20 December 2007 13:14

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: Trigger for Changes in Database [message #289327 is a reply to message #289326]

Thu, 20 December 2007 13:36

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

Report message to a moderator

Re: Trigger for Changes in Database [message #289329 is a reply to message #289312]

Thu, 20 December 2007 13:39

BlackSwan
Messages: 26766
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.

Report message to a moderator

Previous Topic:	Please help, problems refining searches.
Next Topic:	Logic of rewriting a Query!!!

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Dec 14 14:50:43 CST 2024