CODEWITHSUNDEEP
sqlpostgresqltriggerssql-update
Muhsin Emre
Muhsin Emre

Reputation: 1

Cascade UPDATE on a table on another table

I'm a newbie here and with PostgreSQL as well. I have two different boolean columns in two different tables. When I update my boolean column as true in tableone, I must also set a boolean column in tabletwo to true.

For example: (these are related by sipariskod)

UPDATE tabletwo 
SET kalemonay = tableone.sonaydurum 
FROM  tableone 
WHERE tabletwo.sipariskod='10'

I used this SQL query, but it didn't work. I don't know why. :(

I need a function or trigger to execute when I update tableone's boolean to also update the boolean in tabletwo- if it exists with the same sipariskod.

I'd be grateful for any help.

Upvotes: 0

Views: 418

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658472

The best solution cannot be guessed from the limited information you provided.

In any case, your query fails on principal. There is no condition connecting the two tables, so it results in an expensive and nonsensical Cartesian product - the same as a CROSS JOIN. Effectively, instead of justa single update, you get one for every row in tableone, and it is not defined which of these comes last to have a lasting effect.

Since you provided:

(these are related by sipariskod)

.. your query could look like this:

UPDATE tabletwo t2
SET    kalemonay = t1.sonaydurum 
FROM   tableone t1
WHERE  t2.sipariskod = t1.sipariskod
AND    t1.??? = ???

But this statement would look different inside a trigger function where you can use the NEW object. For instance, inside a trigger function for a trigger

CREATE TRIGGER foo
AFTER UPDATE ON TABLE ON tableone
FOR EACH ROW EXECUTE PROCEDURE foo();

The trigger function could look something like this:

CREATE OR REPLACE FUNCTION foo()
  RETURNS trigger AS
$func$
BEGIN

IF NEW.sonaydurum IS DISTINCT FROM OLD.sonaydurum THEN
   UPDATE tabletwo t2
   SET    kalemonay = NEW.sonaydurum 
   WHERE  t2.sipariskod = NEW.sipariskod;
   AND    kalemonay IS DISTINCT FROM NEW.sonaydurum;
END IF;

RETURN NULL;

END
$func$ LANGUAGE plpgsql;

I added some checks to avoid empty updates.

Foreign key

But the whole thing might be cleaner with a foreign key constraint and ON UPDATE CASCADE. Depends on the details. You'd have to clear up offending values from the referencing table, and create a unique (or primary) index on the referenced column(s), before you can create the foreign key. Are you aware that a foreign key can be built on multiple columns?

Upvotes: 1

Related Questions