Thread: updating table field whenever other table field changes

Hello postgresql.questions group!

What do you think the best way to attack the following is:
parent table a - [status_field]

child table b - [status_field]

I need trigger or similar to achieve the following:
if a.status_field changes than all children of table a have their
b.status_field change to the same value as a.

However if b.status_field changes its parent record, a.status_field,
does not change.

Should I just create a separate update trigger or can I set this up
right when I am declaring these tables to achive this.

I also do not really understand CONSTRAINT TRIGGER and how it differs
from a regular old TRIGGER.  What do these internal TRIGGERS,
particularly "RI_FKey_noaction_upd" do?

Thanks for your time.  Any help or advice is appreciated.

-------
Andrew C.
tech7890@NOTPART@yahoo.com
Please remove characters 'NOTPART' from e-mail address above to e-mail
me

You can do this with a trigger very easily however there is also the ON
UPDATE CASCADE functionality when you create the table. If you add this
to the parent table any changes made to the parent table will be drawn
down to the child table. If you modify the child table it will not
affect the parent table.

The assumptions are the following:

1. child status is foreign key of parent status.
2. if child status changes it will change to a valid parent status.

HTH

Darren Ferguson


Andrew wrote:

>Hello postgresql.questions group!
>
>What do you think the best way to attack the following is:
>parent table a - [status_field]
>
>child table b - [status_field]
>
>I need trigger or similar to achieve the following:
>if a.status_field changes than all children of table a have their
>b.status_field change to the same value as a.
>
>However if b.status_field changes its parent record, a.status_field,
>does not change.
>
>Should I just create a separate update trigger or can I set this up
>right when I am declaring these tables to achive this.
>
>I also do not really understand CONSTRAINT TRIGGER and how it differs
>from a regular old TRIGGER.  What do these internal TRIGGERS,
>particularly "RI_FKey_noaction_upd" do?
>
>Thanks for your time.  Any help or advice is appreciated.
>
>-------
>Andrew C.
>tech7890@NOTPART@yahoo.com
>Please remove characters 'NOTPART' from e-mail address above to e-mail
>me
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to majordomo@postgresql.org so that your
>message can get through to the mailing list cleanly
>
>

On 28 Mar 2003, Andrew wrote:

> Hello postgresql.questions group!
>
> What do you think the best way to attack the following is:
> parent table a - [status_field]
>
> child table b - [status_field]
>
> I need trigger or similar to achieve the following:
> if a.status_field changes than all children of table a have their
> b.status_field change to the same value as a.
>
> However if b.status_field changes its parent record, a.status_field,
> does not change.

How do the rows of a and b relate to each other?

> I also do not really understand CONSTRAINT TRIGGER and how it differs
> from a regular old TRIGGER.  What do these internal TRIGGERS,
> particularly "RI_FKey_noaction_upd" do?

That's the implementation for the ON UPDATE NO ACTION of a foreign key.
Generally speaking you won't want to make CONSTRAINT TRIGGERS (it's meant
as an internal thing for doing the foreign keys) unless you want it to
be deferrable.

Andrew wrote:
>
> Hello postgresql.questions group!
>
> What do you think the best way to attack the following is:
> parent table a - [status_field]
>
> child table b - [status_field]
>
> I need trigger or similar to achieve the following:
> if a.status_field changes than all children of table a have their
> b.status_field change to the same value as a.

If b.status_field allways has to have a value that exists in
a.status_field (or maybe NULL if you allow it to), you want to define a
FOREIGN KEY constraint with the referential action ON UPDATE CASCADE.


Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #