Re: trigger question - Mailing list pgsql-general

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!