pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPreventing Deletions with triggers
4+ messages / 4 participants
[nested] [flat]
* Preventing Deletions with triggers
@ 2004-05-19 14:19 Jeff Post <POSTJL@milwaukee.k12.wi.us>
2004-05-21 08:30 ` Re: Preventing Deletions with triggers Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-05-21 15:01 ` Re: Preventing Deletions with triggers Manuel Sugawara <masm@fciencias.unam.mx>
2004-05-21 15:28 ` Re: Preventing Deletions with triggers Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 3 replies; 4+ messages in thread
From: Jeff Post @ 2004-05-19 14:19 UTC (permalink / raw)
To: pgsql-sql
I would like to have a trigger that fires whenever a delete is attempted.
The trigger should update the status field of the tupple (to remove the
tupple form the active set of tupples).
Here is what I got:
CREATE or replace FUNCTION person_fake_delete() RETURNS TRIGGER AS '
BEGIN
OLD.status := 1; -- This does the fake deletion
RETURN NULL; -- I thought this would prevent the delete from
actually happening.
END;
' LANGUAGE 'plpgsql';
create trigger person_fake_delete
before delete on person
for each row EXECUTE PROCEDURE
person_fake_delete();
This however does not work. the tupple is still deleted from the
table. Any Ideas?
Thanks,
Jeff Post
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Preventing Deletions with triggers
2004-05-19 14:19 Preventing Deletions with triggers Jeff Post <POSTJL@milwaukee.k12.wi.us>
@ 2004-05-21 08:30 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2 siblings, 0 replies; 4+ messages in thread
From: Achilleus Mantzios @ 2004-05-21 08:30 UTC (permalink / raw)
To: Jeff Post <POSTJL@milwaukee.k12.wi.us>; +Cc: pgsql-sql
O kyrios Jeff Post egrapse stis May 19, 2004 :
> I would like to have a trigger that fires whenever a delete is attempted.
> The trigger should update the status field of the tupple (to remove the
> tupple form the active set of tupples).
>
A rule would be more appropriate for this task.
>
> Here is what I got:
>
> CREATE or replace FUNCTION person_fake_delete() RETURNS TRIGGER AS '
> BEGIN
> OLD.status := 1; -- This does the fake deletion
> RETURN NULL; -- I thought this would prevent the delete from
> actually happening.
> END;
> ' LANGUAGE 'plpgsql';
>
> create trigger person_fake_delete
> before delete on person
> for each row EXECUTE PROCEDURE
> person_fake_delete();
>
> This however does not work. the tupple is still deleted from the
> table. Any Ideas?
>
> Thanks,
> Jeff Post
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
--
-Achilleus
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Preventing Deletions with triggers
2004-05-19 14:19 Preventing Deletions with triggers Jeff Post <POSTJL@milwaukee.k12.wi.us>
@ 2004-05-21 15:01 ` Manuel Sugawara <masm@fciencias.unam.mx>
2 siblings, 0 replies; 4+ messages in thread
From: Manuel Sugawara @ 2004-05-21 15:01 UTC (permalink / raw)
To: Jeff Post <POSTJL@milwaukee.k12.wi.us>; +Cc: pgsql-sql
Jeff Post <POSTJL@milwaukee.k12.wi.us> writes:
> Here is what I got:
>
> CREATE or replace FUNCTION person_fake_delete() RETURNS TRIGGER AS '
> BEGIN
> OLD.status := 1; -- This does the fake deletion
> RETURN NULL; -- I thought this would prevent the delete from
> actually happening.
>
> END;
> ' LANGUAGE 'plpgsql';
You need to explicitly update the row because changes to the OLD
reference will be lost, for instance:
CREATE or replace FUNCTION person_fake_delete() RETURNS TRIGGER AS '
BEGIN
UPDATE your_table SET status = 1 WHERE primary_key = OLD.primary_key;
RETURN NULL; -- I thought this would prevent the delete from actually happening.
END;
' LANGUAGE 'plpgsql';
> This however does not work. the tupple is still deleted from the table.
> Any Ideas?
Really?, The tuple is still deleted? There should be something that you are
not telling us (may be you are creating the trigger to run AFTER instead of
BEFORE). It have worked for me in each version since the trigger system
were added to postgres.
Regards,
Manuel.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Preventing Deletions with triggers
2004-05-19 14:19 Preventing Deletions with triggers Jeff Post <POSTJL@milwaukee.k12.wi.us>
@ 2004-05-21 15:28 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2004-05-21 15:28 UTC (permalink / raw)
To: Jeff Post <POSTJL@milwaukee.k12.wi.us>; +Cc: pgsql-sql
Jeff Post <POSTJL@milwaukee.k12.wi.us> writes:
> CREATE or replace FUNCTION person_fake_delete() RETURNS TRIGGER AS '
> BEGIN
> OLD.status := 1; -- This does the fake deletion
> RETURN NULL; -- I thought this would prevent the delete from
> actually happening.
> END;
> ' LANGUAGE 'plpgsql';
> create trigger person_fake_delete
> before delete on person
> for each row EXECUTE PROCEDURE
> person_fake_delete();
> This however does not work. the tupple is still deleted from the
> table. Any Ideas?
It works for me, in the sense that returning NULL prevents the
deletion. However that assignment to OLD is a no-op: you can't change
the tuple that way. You'd have to do something like
UPDATE person SET status = 1 WHERE key = OLD.key;
("key" being whatever your primary key for the table is)
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-05-21 15:28 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-05-19 14:19 Preventing Deletions with triggers Jeff Post <POSTJL@milwaukee.k12.wi.us>
2004-05-21 08:30 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-05-21 15:01 ` Manuel Sugawara <masm@fciencias.unam.mx>
2004-05-21 15:28 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox