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
Gettable_name -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ SELECT
relnameINTO 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_classSET 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