Triggers [message #285579] |
Wed, 05 December 2007 01:36  |
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   |
rleishman
Messages: 3728 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   |
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 #285918 is a reply to message #285905] |
Thu, 06 December 2007 01:45  |
rleishman
Messages: 3728 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
|
|
|