Updating multiple rows of the same table in a trigger

802484

Hello All,

I am facing a situation, in which I need to update multiple rows of a same table in which trigger has been written. IS it possbile to do it?

I know that we can use :NEW to update current row, but when we need to update multiple rows on some condtion, it fails.

Thanks in advance for any help.

CREATE OR REPLACE TRIGGER stsc.dfutoskuallocfactor_trig
BEFORE UPDATE
ON stsc.dfutosku
FOR EACH ROW
DECLARE
v_dmdunit CHAR (30);
v_dmdgroup CHAR (30);
v_loc CHAR (30);
BEGIN
--dfutosku
INSERT INTO igpmgr.dfutosku_upd_rec
VALUES (:OLD.dmdunit, :OLD.dmdgroup, :OLD.dfuloc);

--FOR rec IN (SELECT DISTINCT dmdunit, dmdgroup, dfuloc, eff, disc
-- FROM stsc.dfutosku_upd_rec)
--LOOP
-- IF (rec.eff <> '01-Jan-1970' OR rec.eff >= SYSDATE)
-- THEN
-- DBMS_OUTPUT.put_line ('STAGE3 ' || ' ' || rec.eff);
v_dmdunit := 'PLY 4-000';
v_dmdgroup := 'TRD';
v_loc := '48441';

IF (v_dmdunit = 'PLY 4-000' AND v_dmdgroup = 'TRD' AND v_loc = '48441')
THEN
:NEW.udc_error := 1;
END IF;
--igpmgr.

pkg_updateAllocfactor.allocfactor_update(o_return_code OUT NOCOPY PLS_INTEGER);
--END IF;
-- END LOOP;
END;
/


With Regards,
SK

6363

swapneel_kale wrote:

I am facing a situation, in which I need to update multiple rows of a same table in which trigger has been written.

So you want a trigger on a table that executes on updates that updates the table that executes the trigger that updates the table that executes the trigger that updates the table that executes the trigger ... forever?

Why would you want to do that?

802484

No Sir,

I just need a trigger, which can execute when a table is updated and can update ~ 10 rows in a table

Unknown

This approach is seriously flawed and is just begging to end in disaster.
If you want to insert 10 rows, why not use ordinary SQL.
Your request raises suspicions you are trying to do something behind the scenes with canned software you can't change.
Just for your information: Most vendors are very quick in withdrawing support if you do something like that.

Hopefully you didn't build your own house. Likely it collapses when you open the door.

-------------------
Sybrand Bakker
Senior Oracle DBA

802484

Its not "INSERT", its update, as I already mentioned above which updates the rows which are set to obsolete according to a business condition. Please do not try to give a different color to a business scenario.

Thanks for your not very helpful comments as always.

JustinCave

A row-level trigger is not going to be able to modify anything other than the "current" row's data. If you try to update other rows in the table, Oracle is going to throw a mutating table exception.

In general, I would agree with the other folks that have pointed out that it is generally a very bad design to have a table that is designed in such a way that an update to 1 row ought to cause 9 other rows in the same table to be updated. That strongly implies that there is a failure to properly normalize the data. As such, workarounds like the one I'm about to describe are going to be inherently fragile and likely to cause plenty of headaches down the line. Proceed with extreme caution.

You could (although I wouldn't recommend it) create a package, create a collection in that package, and create a set of three triggers that worked with the collection to cause the 9 other rows to be updated when the 1 row is updated.
- The package creates a collection type as well as a package global variable of the collection type to store primary keys of your table
- A before statement trigger empties the collection
- A row-level trigger populates the collection with the current row's primary key
- An after statement trigger can process the elements in this collection and issue whatever updates are necessary

Of course, those updates would cause the same set of triggers to fire again (presumably). Assuming you want to prevent that, you would have to declare another package variable to indicate whether you are already in the triggers, check that value in the row-level trigger and do nothing if the flag is set, set the flag in your after statement trigger before issuing the updates and unset it before the after statement trigger completes.

That's a whole lot of moving pieces to create and maintain and a very complex workflow to work through and debug. It's almost certainly going to create issues down the line that are going to be a pain to debug. It is not something I would recommend. But it is a workaround if you absolutely need to do this.

Justin