Postgresql – how to select a value in one table with max() to update a row in a second table, with a trigger in PostgreSQL

postgresqltrigger

Here is tbl1 :

gid pm
1   2.1
2   2.0
3   1.95
...

Here is tbl2 I want :

gid num pb  gidTbl1
1    1        1
2    2        1
3    3  2.1   1
4    1        2
5    2        2
6    3        2
7    4  2.0   2
8    1        3
...

On an insert in tbl2, I would like that pb = pm for gidTbl1 = gid AND for the max num in tbl2.
Here are a trigger and the function on tbl2 I wrote :

CREATE OR REPLACE FUNCTION maj_pb()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
begin
IF TG_OP IN ('INSERT')
THEN NEW.pb = pm from tbl1
        where NEW.gidTbl1 = tbl1.gid
        and num = (select max(num) from tbl2
                          where NEW.gidTbl1 = g.gid
                        ) ;
RETURN NEW ;
END IF ;
END ;
$BODY$;

CREATE TRIGGER trg_maj_pb
BEFORE INSERT
ON tbl2
FOR EACH ROW
EXECUTE PROCEDURE activite.maj_pb();

error message is :

num doesn't exist

I presume that the trigger is looking for num in tbl1 (?). But I can't find an other way to write the function.

I add : In fact, whatever I do (insert or update) the max num by gidTbl1 (tbl2) always has to have the pm corresponding in tbl1.

Best Answer

The immediate error is that you have num where it would have to be NEW.num. But there are more problems ...

I add : In fact, whatever I do (insert or update) the max num by gidTbl1 (tbl2) always has to have the pm corresponding in tbl1.

Trying to get this right with triggers is a pain, even without possible concurrent writes.

With possible concurrent writes, it's only even possible with expensive locks (or with SERIALIZABLE snapshot isolation). You can't have multiple concurrent transactions messing with the max num at the same time.

You could force all write access on tbl2 to first take out a row lock on the respective parent row in tbl1 (and keep track of the current max tbl2.num in an additional column tbl1.max_num while being at it) ...
See:

But I wouldn't go there. The safe way to achieve this is to drop the column tbl2.pb altogether. Instead use a VIEW (or MATERIALIZED VIEW) like this to dynamically get the current value, where you need it:

CREATE VIEW tbl2_plus_pb AS
SELECT t2.gid, t2.num, t1.pm AS pb, t2.gid_tbl1
FROM  (
   SELECT *, CASE WHEN max(num) OVER (PARTITION BY gid_tbl1) = num
                  THEN gid_tbl1 END AS link
   FROM   tbl2
   ) t2
LEFT   JOIN tbl1 t1 ON t1.gid = t2.link
ORDER  BY t2.gid;

db<>fiddle here

AFTER INSERT:

USE `TEST`;
DELIMITER $$
DROP TRIGGER IF EXISTS MEDICAMENTO.monthUpdateTriggerAI$$
USE `TEST`$$
    CREATE DEFINER = CURRENT_USER TRIGGER `TEST`.`monthUpdateTriggerAI` AFTER INSERT ON `TestTable` FOR EACH ROW
    BEGIN
    SET @COUNT=(SELECT COUNT(*) FROM TestTable2 WHERE (ItemId=NEW.ItemId AND Year=YEAR(NEW.Date) AND Month=MONTH(NEW.Date)));
    IF @COUNT=0 THEN
        INSERT INTO TestTable2 (ItemId,Year,Month,Open,Close,Increase ) VALUES 
        (NEW.ItemId , YEAR(NEW.Date), MONTH(NEW.Date),NEW.Value,NEW.Close,0.0); 
    ELSE
        UPDATE TestTable2 SET TestTable2.Close=NEW.Close AND Increase=(NEW.Close/(SELECT 
        Open FROM TestTable WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date)) LIMIT 1))
        WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date));
    END IF;
    END;
$$
DELIMITER ;

AFTER UPDATE:

USE `TEST`;
DELIMITER $$
DROP TRIGGER IF EXISTS MEDICAMENTO.monthUpdateTriggerAU$$
USE `TEST`$$
    CREATE DEFINER = CURRENT_USER TRIGGER `TEST`.`monthUpdateTriggerAU` AFTER UPDATE ON `TestTable` FOR EACH ROW
BEGIN

    UPDATE TestTable2 SET TestTable2.Close=NEW.Close AND Increase=(NEW.Close/(SELECT 
    Open FROM TestTable WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date)) LIMIT 1))
    WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date));

END;
$$
DELIMITER ;

Note: Be careful about the SELECT Open FROM TestTable WHERE (Year=YEAR(NEW.Date) and Month=MONTH(NEW.Date)), I've added a LIMIT 1 to avoid duplicate rows.

Change the database TEST.

You can also use ON DUPLICATE KEY on the two triggers execution.

You can get more information in the MySQL's documentation manual.

Sql-server – SQL Server Trigger does not update all table records

Currently, your trigger is updating all rows. Modify it to use the inserted virtual table, something like this:

ALTER TRIGGER [dbo].[TRIG_Update_Inventory_Ind_Trees_Abs_Plot_Numb]
ON [dbo].[Plot_Location]
AFTER INSERT
AS 
BEGIN
    UPDATE Inventory_Ind_Trees
    SET Inventory_Ind_Trees.Abs_Plot_Numb = Plot_Location.Abs_Plot_Numb
    FROM Plot_Location
    WHERE EXISTS (
            SELECT 1 
            FROM inserted i 
            WHERE i.Invent_Plot_ID = Plot_Location.Invent_Plot_ID
        )
        AND Plot_Location.Invent_Plot_ID = Inventory_Ind_Trees.Invent_Plot_ID;
END

This may not be exactly correct; it's hard to tell exactly where to join the inserted virtual table with your other tables. Essentially, the idea is the inserted virtual table only contains rows that were either inserted or updated in the Plot_Location table.

You shouldn't check the @@ROWCOUNT system function at the start. It may not reflect the rowcount you think it does. Instead you should select the count of rows in the inserted table. Like this:

DECLARE @rc INT;
SELECT @rc = COUNT(1)
FROM inserted;

IF @rc > 0
BEGIN
    --body of trigger  goes here.
END

Best Answer

Related Solutions

MySQL Trigger – How to Create Trigger for Update or Insert in Another Table

AFTER INSERT:

AFTER UPDATE:

Sql-server – SQL Server Trigger does not update all table records

Related Question