Обсуждение: trigger to access only the last transaction
hi all,
i have two tables with several columns:idtaskhistory
performedat
...
idtask
idtask
and
table2.....
i created a trigger that does the following:
CREATE TRIGGER del1
AFTER INSERT
ON table1
FOR EACH ROW
EXECUTE PROCEDURE taskdel;
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;
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
On Mon, Mar 9, 2015 at 6:01 AM, avpro avpro <avprowebeden@gmail.com> wrote:
manualdueatidtask,table1hi all,i have two tables with several columns:
idtaskhistory
performedat...
idtaskandtable2.....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 systemthank you for your ideas,John
--
Marcos | I love PHP, Linux, and Java
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.idNote this is a join on a scalar value. NEW.id should be a number (e.g. 42) when this is processed
On Mon, Mar 9, 2015 at 1:29 AM, Marcos Almeida Azevedo <marcos.al.azevedo@gmail.com> wrote:
On Mon, Mar 9, 2015 at 6:01 AM, avpro avpro <avprowebeden@gmail.com> wrote:manualdueatidtask,table1hi all,i have two tables with several columns:
idtaskhistory
performedat...
idtaskandtable2.....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 systemthank you for your ideas,John
--Marcos | I love PHP, Linux, and Java
Sorry I forgot to mention: last insert could have multiple rows inserted and I would like to access all of them, not necessarily only the last one. That's my problem. Or I misunderstand the "last entry".
Thank you.
On 9 Mar 2015 08:29, "Marcos Almeida Azevedo" <marcos.al.azevedo@gmail.com> wrote:
On Mon, Mar 9, 2015 at 6:01 AM, avpro avpro <avprowebeden@gmail.com> wrote:manualdueatidtask,table1hi all,i have two tables with several columns:
idtaskhistory
performedat...
idtaskandtable2.....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 systemthank you for your ideas,John
--Marcos | I love PHP, Linux, and Java
I will try and see if works. Thank you
On 9 Mar 2015 08:46, "Frank Pinto" <frank@ayalo.co> wrote:
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.idNote this is a join on a scalar value. NEW.id should be a number (e.g. 42) when this is processedFrankOn Mon, Mar 9, 2015 at 1:29 AM, Marcos Almeida Azevedo <marcos.al.azevedo@gmail.com> wrote:On Mon, Mar 9, 2015 at 6:01 AM, avpro avpro <avprowebeden@gmail.com> wrote:manualdueatidtask,table1hi all,i have two tables with several columns:
idtaskhistory
performedat...
idtaskandtable2.....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 systemthank you for your ideas,John
--Marcos | I love PHP, Linux, and Java
That's what the FOR EACH ROW part of the statement is for. Your trigger function will be executed once per each row that is modified / inserted: http://www.postgresql.org/docs/9.4/static/sql-createtrigger.html.
FrankOn Mon, Mar 9, 2015 at 1:50 AM, avpro avpro <avprowebeden@gmail.com> wrote:
Sorry I forgot to mention: last insert could have multiple rows inserted and I would like to access all of them, not necessarily only the last one. That's my problem. Or I misunderstand the "last entry".
Thank you.On 9 Mar 2015 08:29, "Marcos Almeida Azevedo" <marcos.al.azevedo@gmail.com> wrote:On Mon, Mar 9, 2015 at 6:01 AM, avpro avpro <avprowebeden@gmail.com> wrote:manualdueatidtask,table1hi all,i have two tables with several columns:
idtaskhistory
performedat...
idtaskandtable2.....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 systemthank you for your ideas,John
--Marcos | I love PHP, Linux, and Java
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;
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@gmail.com> wrote:
manualdueatidtask,table1hi all,i have two tables with several columns:
idtaskhistory
performedat...
idtaskandtable2.....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 systemthank you for your ideas,John