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:[EMAIL PROTECTED]
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
