Em qua, 29 de mai de 2019 às 08:52, PegoraroF10 <mar...@f10.com.br> escreveu: > > We like to use With to insert, update and return some value to user. But some > informations of those related tables are not available on that time, is that > a bug ? > > with > Master(Master_ID) as (insert into Master(Customer_ID, Field2) values(1, > 'BlaBla') returning Master_ID), > Detail as (insert into Detail(Master_ID, Product_ID, ProductValue) select > Master_ID, 5, 50 from Master) > select Master_ID from Master; > > This code works but not as expected because we have a trigger which does not > see that data yet. > Suppose a trigger on Detail which needs to find any info from a table which > was inserted on this With. That info is not available, like ... > create function DetailOfDetail() returns trigger() as -- this trigger > function is before insert on Detail > begin > new.Discount = (select discount from Customer inner join Master > using(Customer_ID) where Master_ID = new.Master_ID) > end; > This trigger will not work because Master record was not inserted yet. > > If change it to a DO it would work but we would like that result, so ... > This trigger is obviously an example, our tables have more complex > structures but here we want only to understand the way postgres works or if > it´s not working properly. >
To see updated data using a trigger in the same transaction you should create trigger on AFTER event. Regards, -- Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/ PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
