Thread: trigger question
Hello,
I have a table named foobar and I don't want to allow from DELETE or UPDATE
its rows.
I have a table as described below:
foobar(foobar_id, value, is_deleted);
I don't want to allow directly delete or modify the table's rows. I plan to
make an on before update or delete trigger and
on delete action I update the actual row is_deleted flag, on UPDATE action I
also update the is_deleted flag and I insert a new row with the new values.
Everything is ok, but when I capture the delete action I am execute an
update what triggering the trigger again and I got an unwanted row.
CREATE TRIGGER "tr_foobar" BEFORE UPDATE OR DELETE ON "public"."foobar" FOR
EACH ROW
EXECUTE PROCEDURE "public"."tr_foobar_func"();
BEGIN
IF TG_OP='DELETE' THEN
UPDATE foobar SET is_deleted=TRUE WHERE foobar_id=OLD.foobar;
RETURN NULL;
ELSEIF TG_OP='UPDATE' THEN
INSERT INTO foobar(value) VALUES(NEW.value);
NEW.is_deleted=TRUE;
NEW.value=OLD.value;
RETURN NEW;
END IF;
END;
What is the right solution for this situation. Thank you for the help!
am Tue, dem 16.01.2007, um 18:35:56 +0100 mailte Furesz Peter folgendes: > Hello, > > I have a table named foobar and I don't want to allow from DELETE or > UPDATE its rows. > > I have a table as described below: > foobar(foobar_id, value, is_deleted); > > I don't want to allow directly delete or modify the table's rows. I plan to > make an on before update or delete trigger and > on delete action I update the actual row is_deleted flag, on UPDATE action I think, you should better use a RULE for this: http://www.postgresql.org/docs/8.2/interactive/rules.html Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
I ran into a similar problem and the solution I came up with (which
admittedly feels like a kludge) was to temporarily disable the triggers
on the table being modified while an update was made and then
re-enabling them immediately after the update. I am sure there is
potential for problems with this approach and I too would like to find a
better one, but right now this works as I am still in the development
stage and not dealing with any critical data. Anyway, this is
essentially the code I use (applied now to table foobar) and maybe
sharing it will help inspire a better solution. Please keep the list
and me informed if you have one....Thanks, Joel
Code excerpt from within on delete trigger function for foobar.....
-- Disable triggers on table foobar
UPDATE pg_catalog.pg_class SET reltriggers = 0 WHERE oid =
'foobar'::pg_catalog.regclass';
-- Perform update
UPDATE foobar SET is_deleted=TRUE WHERE foobar_id=OLD.foobar;
-- Re-enable triggers on table foobar
UPDATE pg_catalog.pg_class SET reltriggers = 1 WHERE oid =
'foobar'::pg_catalog.regclass';
-----Original Message-----
From: Furesz Peter [mailto:fureszpeter@srv.hu]
Sent: Tuesday, January 16, 2007 10:36 AM
To: postgres levlista
Subject: trigger question
Hello,
I have a table named foobar and I don't want to allow from DELETE or
UPDATE its rows.
I have a table as described below:
foobar(foobar_id, value, is_deleted);
I don't want to allow directly delete or modify the table's rows. I plan
to make an on before update or delete trigger and on delete action I
update the actual row is_deleted flag, on UPDATE action I also update
the is_deleted flag and I insert a new row with the new values.
Everything is ok, but when I capture the delete action I am execute an
update what triggering the trigger again and I got an unwanted row.
CREATE TRIGGER "tr_foobar" BEFORE UPDATE OR DELETE ON "public"."foobar"
FOR EACH ROW EXECUTE PROCEDURE "public"."tr_foobar_func"();
BEGIN
IF TG_OP='DELETE' THEN
UPDATE foobar SET is_deleted=TRUE WHERE foobar_id=OLD.foobar;
RETURN NULL;
ELSEIF TG_OP='UPDATE' THEN
INSERT INTO foobar(value) VALUES(NEW.value);
NEW.is_deleted=TRUE;
NEW.value=OLD.value;
RETURN NEW;
END IF;
END;
What is the right solution for this situation. Thank you for the help!
Hello, maybe I have found a better solution.
In PostgreSQL 8.2 this current solution is not working properly, because I
got
"too many triggers on table tablename" error. This is the first thing. The
second problem
that if something go wrong between the disable and re-enable the trigger,
the trigger will
stay in disabled state, so we want to find a solution to disable the trigger
for the current session.
You have to put this line in postgresql.conf
custom_variable_classes = 'general'
This is a session variable. If this variable IS FALSE, I check it at TRIGGER
fire time and if it is false,
I allow the trigger to fire, if not I skip the trigger. If it is not set, I
set to FALSE at the trigger first line.
If something goes wrong, the trigger stay in disable for the current session
only.
For example:
CREATE OR REPLACE FUNCTION "public"."tr_logolas" () RETURNS trigger AS
$body$
DECLARE
v_tmp RECORD;
a_trigger_disable BOOLEAN;
BEGIN
SELECT NULLIF(current_setting('general.trigger_tmp'), '') AS trigger_tmp
INTO v_tmp;
IF NOT FOUND OR v_tmp.trigger_tmp IS NULL THEN
EXECUTE 'SET SESSION general.trigger_tmp=FALSE';
a_trigger_disable := FALSE;
ELSE
a_trigger_disable := v_tmp.trigger_tmp;
END IF;
-- ******** END OF SETTING UP TRIGGER ******************
IF TG_OP='DELETE' THEN
--disabling trigger
EXECUTE 'SET SESSION general.trigger_tmp=TRUE';
UPDATE sulyozas
SET torolve = TRUE
WHERE sulyozas_id = OLD . sulyozas_id;
--enabling trigger
EXECUTE 'SET SESSION general.trigger_tmp=FALSE';
END IF;
IF TG_OP='UPDATE' AND a_trigger_disable IS FALSE THEN
--Do something here
END IF;
END;
----- Original Message -----
From: "Lenorovitz, Joel" <Joel.Lenorovitz@usap.gov>
To: "Furesz Peter" <fureszpeter@srv.hu>; <pgsql-general@postgresql.org>
Sent: Friday, January 19, 2007 5:57 PM
Subject: RE: trigger question
I ran into a similar problem and the solution I came up with (which
admittedly feels like a kludge) was to temporarily disable the triggers
on the table being modified while an update was made and then
re-enabling them immediately after the update. I am sure there is
potential for problems with this approach and I too would like to find a
better one, but right now this works as I am still in the development
stage and not dealing with any critical data. Anyway, this is
essentially the code I use (applied now to table foobar) and maybe
sharing it will help inspire a better solution. Please keep the list
and me informed if you have one....Thanks, Joel
Code excerpt from within on delete trigger function for foobar.....
-- Disable triggers on table foobar
UPDATE pg_catalog.pg_class SET reltriggers = 0 WHERE oid =
'foobar'::pg_catalog.regclass';
-- Perform update
UPDATE foobar SET is_deleted=TRUE WHERE foobar_id=OLD.foobar;
-- Re-enable triggers on table foobar
UPDATE pg_catalog.pg_class SET reltriggers = 1 WHERE oid =
'foobar'::pg_catalog.regclass';
-----Original Message-----
From: Furesz Peter [mailto:fureszpeter@srv.hu]
Sent: Tuesday, January 16, 2007 10:36 AM
To: postgres levlista
Subject: trigger question
Hello,
I have a table named foobar and I don't want to allow from DELETE or
UPDATE its rows.
I have a table as described below:
foobar(foobar_id, value, is_deleted);
I don't want to allow directly delete or modify the table's rows. I plan
to make an on before update or delete trigger and on delete action I
update the actual row is_deleted flag, on UPDATE action I also update
the is_deleted flag and I insert a new row with the new values.
Everything is ok, but when I capture the delete action I am execute an
update what triggering the trigger again and I got an unwanted row.
CREATE TRIGGER "tr_foobar" BEFORE UPDATE OR DELETE ON "public"."foobar"
FOR EACH ROW EXECUTE PROCEDURE "public"."tr_foobar_func"();
BEGIN
IF TG_OP='DELETE' THEN
UPDATE foobar SET is_deleted=TRUE WHERE foobar_id=OLD.foobar;
RETURN NULL;
ELSEIF TG_OP='UPDATE' THEN
INSERT INTO foobar(value) VALUES(NEW.value);
NEW.is_deleted=TRUE;
NEW.value=OLD.value;
RETURN NEW;
END IF;
END;
What is the right solution for this situation. Thank you for the help!