trigger to access only the last transaction

From: avpro avpro <avprowebeden(at)gmail(dot)com>
To: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>, pgsql-sql(at)postgresql(dot)org
Subject: trigger to access only the last transaction
Date: 2015-03-08 22:01:11
Message-ID: CAAQdDnnYZe+jkgEMiG2U5kJrcmq=fGNroGp0QCWUnUHBK7r+KA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice pgsql-sql

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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Marcos Almeida Azevedo 2015-03-09 07:29:17 Re: trigger to access only the last transaction
Previous Message Marcos Almeida Azevedo 2015-03-05 01:34:24 Re: Split the result of a query in 2 rows

Browse pgsql-sql by date

  From Date Subject
Next Message Marcos Almeida Azevedo 2015-03-09 07:29:17 Re: trigger to access only the last transaction
Previous Message s d 2015-03-07 16:42:21 Re: Find inconsistencies in data with date range