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@xxxxxxxx>
To: "Furesz Peter" <fureszpeter@xxxxxx>; <pgsql-general@xxxxxxxxxxxxxx>
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@xxxxxx]
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!
