Postgresql trigger update whole table after delete

functionsplpgsqlpostgresqltriggerview

I have a table "measurements" which looks like this (with empty cells in the last col)

ser_nr | meas_ser | meas_value | last_meas
-------+----------+------------+-----------
  A1   |    1     |    12      |  (False)
  A1   |    2     |    5.5     |  (False)
  A1   |    3     |    5.12    |  (False)
  A1   |    4     |    5.01    |  (True)
  B1   |    1     |    2       |  (False)
  B1   |    2     |    2.03    |  (True)

http://sqlfiddle.com/#!15/bcd6b (Link for create table etc.)

I need this to query "select … where last_meas=True"

I wrote a trigger for "after insert" and this works for the newly inserted rows. But what can I do to update the whole table?

For example: I want to delete the A1 row with meas_value "5.01". There has to be a function which executes for the whole remaining table and updates the last_meas column (in this example the third row should be True now). I can trigger a function using "after delete for each row" but using this method i can only access all deleted rows.

I found a workaround by generating a view using

SELECT * FROM measurements AS m LEFT OUTER JOIN (SELECT m.ser_nr, 
    max(m.meas_ser) AS meas_ser, TRUE AS last_meas FROM measurements AS 
    m GROUP BY m.ser_nr ) AS last_ms USING (ser_nr, meas_ser);

but this only generates "True" and not "False".

Is there a more easy way doing this?

Best Answer

You can use this simpler / cheaper query for your view or CTE or simply a subquery:

SELECT ser_nr, meas_ser, meas_value
     , row_number() OVER (PARTITION BY ser_nr ORDER BY meas_ser DESC) = 1 AS last_meas
FROM   measurements;

SQL Fiddle.

There are no corner case problems with duplicate or NULL values here while you have that PK constraint you added in the fiddle:

ALTER TABLE measurements ADD CONSTRAINT measurements_pk PRIMARY KEY (ser_nr,meas_ser);

The associated index will also help to make it fast if you only need a small selection of ser_no from a big table.

And yes, this dynamic approach has so much less potential for headache (consistency in the face of concurrent write access!) than trying to keep rows in your base table current.

Related Solutions

Postgresql – how to delete all rows with empty field via function/trigger combo in Postgres v 9.3

There are several things wrong with this trigger.

First: your delete statement. You can't compare NULL using =. You need to use IS NULL:

DELETE FROM test2 WHERE email IS NULL;

Second: a trigger function (quote from the manual) "must return either NULL or a record/row value having exactly the structure of the table the trigger was fired for."

So return test2; should be return new;.

Third: you created a row level trigger, which is going to be very bad for performance. As you are not dependent on the actual values that are changed by the update, a statement level trigger will be much more efficient, because it only fires once for each UPDATE statement rather than once for each row that has been changed.

In a statement level trigger the return value is ignored, so the suggested return new; from above becomes a return null; as there is no new or old record available in a statement level trigger.

Putting it all together:

CREATE OR REPLACE FUNCTION clean_emp() 
  RETURNS trigger AS                                                                           $$
BEGIN
  DELETE FROM test2 WHERE email IS NULL;
  return NULL; -- as we are now using a statement level trigger, null is fine
END;
$$
LANGUAGE plpgsql VOLATILE;

CREATE TRIGGER trig_empty2
AFTER UPDATE OF name ON test2 
  FOR EACH STATEMENT --<< fire only once for each statement, not row
  EXECUTE PROCEDURE clean_emp();

If you also want to disallow empty values ( '' is something different than 'NULL') you would need to change the condition to where coalesce(email, '') = '')

But the trigger approach is wrong to begin with: you should declare the email column as NOT NULL and then nobody will ever be able to put NULL values into that column and therefore you don't need the trigger at all.

Mysql – Unable to update table after creating an After update trigger

Thats correct you can not update the same table where the trigger is getting executed, however if you use before update you can set the st to 0 by checking the conditions something as

DELIMITER $$

USE `smsdev`$$

DROP TRIGGER /*!50032 IF EXISTS */ `pageprivilege_update`$$

CREATE
    /*!50017 DEFINER = 'smsdev'@'%' */
    TRIGGER `pageprivilege_update` BEFORE UPDATE ON `pageprivilege` 
    FOR EACH ROW 
    BEGIN
     if (new.ad = 0 AND new.ed =0 AND new.dl =0 AND new.rd = 0) then
       set new.st=0 ;
     end if;
END;
$$

DELIMITER ;

Best Answer

Related Solutions

Postgresql – how to delete all rows with empty field via function/trigger combo in Postgres v 9.3

Mysql – Unable to update table after creating an After update trigger

Related Question