Q:

Check and trigger constraints

Suppose a DB contains two tables A and B as given below:
 A
---------------------
 id        SERIAL
 id_b      INTEGER -- NOT NULL reference to B.id
 ts_update TIMESTAMP

 B
--------------------------
 id             SERIAL
 ts_last_update TIMESTAMP

Now let's say that we'd want Postgres to automatically update ts_last_update in B such that B.ts_last_update is always equal to MAX(A.ts_update) WHERE A.id_b=B.id whenever A receives new rows, updates to existing rows or rows are deleted.  Similarly, it would be desirable to ensure that any updates to ts_last_update in B conform to the same rule above.
I thought of introducing a trigger that is executed on insert, update and delete operations on A.  But it seems that there'd be a need to also introduce a CHECK constraint to ts_last_update in B, however this would mean that whenever the trigger in A runs, the check constraint in B would also run which means that two queries would be executed.
Is my reasoning above correct or is there a better way of enforcing this strange rule?  Is there perhaps a way of specifying some sort of exclusivity where when the trigger runs, the check constraint is ignored?

A:

The requirements are not clear in at least three places:

What should happen if B is updated but no related row in A is updated? Should B.ts_last_update be updated with current timestamp or not? 
What should happen if a row in B has 0 related rows in A (due to that it was just inserted or the related rows from A were deleted? Should B.ts_last_update hold the latest update timestamp of B/A rows or be NULL?
I can't see how a CHECK constraint would help in your case. CHECK constraints are row constraints, they can only be used for checking data from one table and each row independently of other rows, not from two tables.

If the aim is for that column to always hold the MAX(A.ts_update) WHERE A.id_b = B.id value and not to be affected by updates in table B, then you don't really need a trigger at all. You can query both tables every time you need the value or have a view:
--------------------------
CREATE TABLE B
  ( id        SERIAL PRIMARY KEY,
    -- more columns
    -- but no timestamp
  ) ;

---------------------
CREATE TABLE A
  ( id        SERIAL PRIMARY KEY, 
    id_b      INTEGER NOT NULL REFERENCES B (id),
    ts_update TIMESTAMP
  ) ;

-- View: B with  ts_last_update
--------------------------
CREATE VIEW BB
    AS
    SELECT B.*,
           ( SELECT MAX(A.ts_update) 
             FROM A
             WHERE A.id_b = B.id
           ) AS ts_last_update
    FROM B ;

