Re: trigger to access only the last transaction

Hi,

Do as Frank said! It will solve your problem.
Forget your task for a minute and check your command:

update table2
SET
manualdueat = null,
FROM table1,
where table1.idtask = table2.idtask;

Works as a charm, and updates all record in table2 which have an matching
counterpart in table1. :)

In per row trigger procedures you have access to the old and new version of
currently processed record trough the NEW and OLD record variables. In your
trigger procedure you have to update only the record in table2 where
table2.idtask=new.idtask.
And just for fun in BEFORE triggers you can also modify the NEW record and
with returning NEW modify the whole result of the DML command which
activated the trigger, or even refuse it completely with returning NULL;

Regards,
Sándor Daku

On 8 March 2015 at 23:01, avpro avpro <avprowebeden(at)gmail(dot)com> wrote:

> hi all,
>
> i have two tables with several columns:
> table1
> idtaskhistory
> performedat
> ...
> idtask
>
> and
>
> table2
> idtask,
> manualdueat
> .....
>
>
> i created a trigger that does the following:
>
> CREATE TRIGGER del1
> AFTER INSERT
> ON table1
> FOR EACH ROW
> EXECUTE PROCEDURE taskdel;
>
> taskdel:
> CREATE OR REPLACE FUNCTION taskdel()
> RETURNS trigger AS
> $BODY$begin
> update table2
> SET
> manualdueat = null,
> FROM table1,
> where table1.idtask = table2.idtask;
> return new;
> END$BODY$
> LANGUAGE plpgsql VOLATILE
> COST 100;
> ALTER FUNCTION taskdel()
> OWNER TO user1;
>
> my idea was to have the info from column "manualdueat" deleted after the
> table1 has been updated. the problem i face is that all columns from table2
> will be deleted, not only my last entry where the insert has been done (I
> was thinking that table1.idtask = table2.idtask will work, but isn't); my
> question to you would be how to access only the last insert id in table1
> and make this trigger working. or do you have another walk arround?
> I'm using psql 9.4 on a windows system
>
> thank you for your ideas,
> John
>