Re: Cascade Update Trigger Problems

dleenhouts_at_gmail.com wrote:
> 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.

I don't know about better because this won't work for several reasons.

First being you wrote a statement trigger rather than row level. This will fire once per statement and provide essentially no information as to which rows to update (2, 3, 4 being hard coded). One would properly use a ROW LEVEL trigger and use :NEW and :OLD.

But more critically there may be foreign key relationships that need to be maintained. Any update to a primary key, either to the parent or the child, will generate an exception unless the constraint is DEFERRABLE INITIALLY DEFERRED.

The best advice is to not update primary keys. You need to justify doing so by citing the business case which you have not done.

With respect to top posting ... it is not something Google needs to do: It is something you need to do. Scroll to the bottom of the page. Open a new line. Then enter your comments. Failure to do so may cause others to ignore you.

HTH

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org