Home » SQL & PL/SQL » SQL & PL/SQL » Triggers (Oracle 9i)
Triggers [message #285579] Wed, 05 December 2007 01:36 Go to next message
babuanna
Messages: 28
Registered: October 2007
Junior Member
Hi,

I have a table called employees.

This table has a After Insert trigger called emp_trigg on it.

This trigger has a Merge statement within the After Insert trigger.

like for eg:

MERGE INTO employees_snapshot e
USING hr_records h
ON (e.id = h.emp_id)
WHEN MATCHED THEN
UPDATE SET e.address = h.address
WHEN NOT MATCHED THEN
INSERT (id, address)
VALUES (h.emp_id, h.address);

My Question is:
Will an update statement on employees table trigger the emp_trigg?

In my opinion, there should also be an after update trigger on this table if we need an update to trigger this table.
Re: Triggers [message #285583 is a reply to message #285579] Wed, 05 December 2007 01:44 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You can have a single trigger that does both:
AFTER INSERT OR UPDATE [OF col...] ON table_name


Since the MERGE statement does not reference any of the columns in EMPLOYEES, is it really necessary to trigger the merge on UPDATE? It doesn't even make sense to trigger it on INSERT - if anything, I would have thought that changes to hr_records - the table used in the MERGE - would have triggered the merge.

Are you sure this is a good idea though? That statement is going to perform a FULL TABLE SCAN of both employees_snapshot and hr_records EVERY time you insert/update a row in employees.

Ross Leishman
Re: Triggers [message #285901 is a reply to message #285579] Thu, 06 December 2007 00:54 Go to previous messageGo to next message
babuanna
Messages: 28
Registered: October 2007
Junior Member
thanks rleishman for your reply.

I was just giving an example...above...

Another question, if I use a

AFTER UPDATE [OF col...] ON table_name which also calls the same merge statement and if I fire a update on the employees table will it go into only the UPDATE part of the merge if the condition matches or will it fire the INSERT part of the merge as well and insert a new record?

Thanks in advance for your help..
Re: Triggers [message #285905 is a reply to message #285901] Thu, 06 December 2007 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It goes to the part that matches/not matches the condition given in ON clause.

Regards
Michel
Re: Triggers [message #285918 is a reply to message #285905] Thu, 06 December 2007 01:45 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
There is no intelligent connect between a TRIGGER and a MERGE statement.

MERGE is just another SQL statement, like SELECT, INSERT, UPDATE, or DELETE.

It acquires the ENTIRE row set from the USING clause, and applies EVERY row to the INTO table.

So, if you UPDATE a single row in your base table, it will fire the trigger which may cause 1 MILLION rows to be inserted or updated in another table. It has NOTHING to do with the row you originally updated.

Ross Leishman
Previous Topic: BULK COLLECT getting slower pga memory increasing
Next Topic: Previous Date and Time Calculation
Goto Forum:
  


Current Time: Sun Dec 04 14:29:24 CST 2016

Total time taken to generate the page: 0.07887 seconds