Hi,

I have a problem with "AFTER INSERT TRIGGER", and ask for your help for resolve
of this problem.
I want to insert one row in the table with the "AFTER INSERT TRIGGER".
For this I turn off the "AFTER INSERT TRIGGER" and do "INSERT INTO table_name...".
After that I turn on the "AFTER INSERT TRIGGER".

It works, but after exit from "AFTER INSERT TRIGGER" I received exception
"could not found trigger %u", and in the log file I have the next:

-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                          /*turn off - successfully*/
2003-12-17 13:25:48 LOG:  enabled_trigger(tai_request_nomenclature, false)

                          /*BEFORE INSERT TRIGGER fired in this table - successfully*/
2003-12-17 13:25:48 LOG:  EVENT: The trigger tbi_request BEFORE INSERT ROW
                    fired, table = request oid = 2421194, id in table 257
CONTEXT:  PL/pgSQL function "tbi_request" line 10 at perform
        PL/pgSQL function "tai_request" line 78 at SQL statement

                          /*turn off - successfully*/
2003-12-17 13:25:48 LOG:  enabled_trigger(tai_request, true)

                          /*row before leave trigger AFTER INSERT - successfully*/
2003-12-17 13:25:48 LOG:  EVENT: The trigger tai_request AFTER INSERT ROW fired,
                    table = request oid = 2421194, id in table 256
CONTEXT:  PL/pgSQL function "tai_request" line 92 at perform

                            /* ????? */
2003-12-17 13:25:48 ERROR:  could not find trigger 1680761888

-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I have PostgreSQL 7.4 compiled from source code on the Linux Redhat 7.3.

-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- AFTER INSERT trigger
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE OR REPLACE FUNCTION tai_request() RETURNS trigger AS'
DECLARE
...
        reserve                 int4;
...
BEGIN
                ...
                ...
                ...
                -- disable trigger
                PERFORM enabled_trigger(\'tai_request\', false);
                raise log \'enabled_trigger(tai_request_nomenclature, false)\';

                INSERT INTO request(made_request_id, num_unit) VALUES (new.made_request_id, reserve);

                -- enable trigger
                PERFORM enabled_trigger(\'tai_request\', true);
                raise log \'enabled_trigger(tai_request_nomenclature, true)\';
                ...
                ...
                ...
    PERFORM sys_log(\'EVENT: The trigger % % % % fired, table = % oid = %, id in table % \', format_param(TG_NAME) || format_param(TG_WHEN) || format_param(TG_OP) || format_param(TG_LEVEL) || format_param(TG_RELNAME) || format_param(TG_RELID) || format_param(new.id));  --args(%) || format_param(TG_ARGV[]));
    RETURN new;
END;
' LANGUAGE 'plpgsql' VOLATILE;

CREATE TRIGGER tai_request AFTER INSERT ON request FOR EACH ROW
  EXECUTE PROCEDURE tai_request();

-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- Body of the function enabled_trigger
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE OR REPLACE FUNCTION enabled_trigger(varchar, bool) RETURNS int4 AS'
DECLARE
    trigger_name            alias FOR $1;
    enabled                 alias FOR $2;
    table_name              varchar(63);
    ret_val                 int4;
BEGIN
    -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    -- Check trigger name and Get table_name
    -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    SELECT 
        relname INTO table_name 
    FROM 
        pg_class pgc 
    JOIN pg_trigger pgt ON pgt.tgrelid = pgc.oid AND pgt.tgname = trigger_name;

    IF table_name IS NULL THEN
       ...
       -- raise exception
       ...
    END IF;
    
    UPDATE pg_trigger SET tgenabled = enabled WHERE tgname = trigger_name; 
    UPDATE pg_class SET reltriggers = 0 WHERE relname = table_name; 
    UPDATE pg_class SET reltriggers = (SELECT count(*) FROM pg_trigger WHERE pg_class.oid = tgrelid)
                WHERE relname = table_name; 
        
    SELECT oid INTO ret_val FROM pg_trigger WHERE tgname = trigger_name;
    RETURN ret_val;
END;
' LANGUAGE 'plpgsql' STABLE;


-- 
Best regards,
 Vladimir                            mailto:vladimir@uptsoft.com


Fri, 19 Dec 2003 14:41:30 +0200
Vladimir <vladimir@uptsoft.com>
"could not found trigger %u"