Trigger after so I make an update on us instead of using TG_OP. Now it works as expected with this function :
create or replace function activite.calcul_alti_us()
returns trigger as
set (alti_base, alti_sommet) = (l.alti - base, l.alti-sommet)
from log l
where us.numlog = l.numlog ;
return NEW ;
language 'plpgsql' ;...
If you suspect this to be as a result of 2 transactions occurring nearly simultaneously, then it would appear to perhaps be them overlapping and the first not having committed before the second queries the RPTs table.
This will be down to your Transaction Isolation Levels for the queries. Top validate this you could include SET TRANSACTION ISOLATION LEVEL ...
The trigger will never move rows that are already in the table when you create the trigger, if that is what you mean.
Apart from that, your trigger works just fine.
INSERT INTO title_basics VALUES ('dsadas', 2020, 'T', 'dsadsa', 2021, 'tit', 42, 'min', 'type', 'orig');
INSERT 0 0
No row was inserted, because the trigger function returned NULL.
SELECT * ...
If you are running MySQL8.0, you could drop the column position, and create a view like this:
CREATE VIEW view_amariadbtable AS
RANK() OVER (PARTITION BY year ORDER BY year DESC,id DESC) as position
BTW, you should NOT use reserved words as column name (key)
Try this on 5.7 +,
CREATE TRIGGER trigger BEFORE INSERT ON table1
FOR EACH ROW
DECLARE v1,v2 INT;
select value,value2 INTO v1,v2
from table2 where id=NEW.id;
SET NEW.value = v1;
SET NEW.value2 = v2;