Re: Cascade Update Trigger Problems

<dleenhouts_at_gmail.com> wrote in message news:1154978489.497846.77920_at_i3g2000cwc.googlegroups.com...
: CREATE TRIGGER companyupdate ON company FOR UPDATE
: BEGIN
: UPDATE company
: SET companykey=1
: WHERE companykey in (2, 3, 4);
: END;
:
:
: I know updating keys is not the most correct way to do it, but if
: anyone has a better way to merge these I'm all ears.
:
: The decision regarding what companies get merged has already been made,
: & I have a set of keys for each set of "from" & "to" companies. In this
: case, I want to merge all of these companies into one. In the db they
: register as 4 companies, but I'm trying to clean it up so that only GE
: exists. But I also want all the orders/employees/other child records to
: be updated as well, so it's not as easy as just deleting the
: duplicates. Unfortunately I'm stuck with the existing database & I
: can't change its structure, nor can I turn back time & prevent the
: duplicates from getting into the database, so I have to come up with a
: pound of cure instead of an ounce of prevention.
:
:
...

 > Regards,
: > Frank van Bortel
: >
: > Top-posting is one way to shut me up...
:

you might want to look at the last line of Frank's signature... many regular and helpful posters in this forum get real frustrated when you add comments to the top of the discussion rather than to the bottom

that being said, you seem to be using the wrong structure -- a trigger is used when something must happen in connection with a specific event -- in the case of your trigger, you've specified that any time any record in COMPANY is updated, your update statement should be run, which updates a bunch of records in COMPANY, which will cause your trigger to be executed recursively (ie, forever)

you just need a simple update statement, probably just run from SQL*Plus or some other basic interface -- you don't need a trigger for this operation.

++ mcs Received on Mon Aug 07 2006 - 21:50:37 CEST