Re: trigger to access only the last transaction

On AFTER INSERT per row triggers you have a variable available called NEW
that should have an id. Try adding this to your WHERE clause:

AND table1.id = NEW.id

Note this is a join on a scalar value. NEW.id should be a number (e.g. 42)
when this is processed

Frank

On Mon, Mar 9, 2015 at 1:29 AM, Marcos Almeida Azevedo <
marcos(dot)al(dot)azevedo(at)gmail(dot)com> wrote:

>
>
> On Mon, Mar 9, 2015 at 6:01 AM, 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?
>>
>
> How about querying and order by primary key descending and get the first
> one?
>
>
>
>> I'm using psql 9.4 on a windows system
>>
>> thank you for your ideas,
>> John
>>
>
>
>
> --
> Marcos | I love PHP, Linux, and Java
> <http://javadevnotes.com/java-float-to-string-examples>
>