Thread: ENABLE / DISABLE ALL TRIGGERS IN DATABASE
Hi,
I think this has been asked before, but I can't find the answer from arcive nor google. How to disable/enable all the riggers in a database? I have problem with disabled triggers after database restore. If there is no simple way, could this be made in a function where you find the table names and construct the needed commands in strings. If so, how to get all the tablenames from database?
Best regards and thanks!
Teemu Juntunen
Hi,
I made the function myself. Here is it, if anyone else has a need for this.
Teemu
/* Enable/disable all the triggers in database */
CREATE OR REPLACE FUNCTION fn_triggerall(DoEnable boolean) RETURNS integer AS
$BODY$
DECLARE
mytables RECORD;
BEGIN
FOR mytables IN SELECT relname FROM pg_class WHERE reltriggers > 0 AND NOT relname LIKE 'pg_%'
LOOP
IF DoEnable THEN
EXECUTE 'ALTER TABLE ' || mytables.relname || ' ENABLE TRIGGER ALL';
ELSE
EXECUTE 'ALTER TABLE ' || mytables.relname || ' DISABLE TRIGGER ALL';
END IF;
END LOOP;
CREATE OR REPLACE FUNCTION fn_triggerall(DoEnable boolean) RETURNS integer AS
$BODY$
DECLARE
mytables RECORD;
BEGIN
FOR mytables IN SELECT relname FROM pg_class WHERE reltriggers > 0 AND NOT relname LIKE 'pg_%'
LOOP
IF DoEnable THEN
EXECUTE 'ALTER TABLE ' || mytables.relname || ' ENABLE TRIGGER ALL';
ELSE
EXECUTE 'ALTER TABLE ' || mytables.relname || ' DISABLE TRIGGER ALL';
END IF;
END LOOP;
RETURN 1;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION fn_triggerall(DoEnable boolean) OWNER TO postgres;
COMMENT ON FUNCTION fn_triggerall(DoEnable boolean) IS 'Enable/disable all the triggers in database';
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION fn_triggerall(DoEnable boolean) OWNER TO postgres;
COMMENT ON FUNCTION fn_triggerall(DoEnable boolean) IS 'Enable/disable all the triggers in database';
----- Original Message -----From: Teemu JuntunenTo: PostgreSQLSent: Wednesday, August 27, 2008 11:24 AMSubject: [GENERAL] ENABLE / DISABLE ALL TRIGGERS IN DATABASEHi,I think this has been asked before, but I can't find the answer from arcive nor google. How to disable/enable all the riggers in a database? I have problem with disabled triggers after database restore. If there is no simple way, could this be made in a function where you find the table names and construct the needed commands in strings. If so, how to get all the tablenames from database?Best regards and thanks!Teemu Juntunen
On Wednesday 27 August 2008 04:24, Teemu Juntunen wrote: > Hi, > > I think this has been asked before, but I can't find the answer from arcive > nor google. How to disable/enable all the riggers in a database? I have > problem with disabled triggers after database restore. If there is no > simple way, could this be made in a function where you find the table names > and construct the needed commands in strings. If so, how to get all the > tablenames from database? > > Best regards and thanks! > Teemu Juntunen You can accomplish this by manipulating the run time parameter, session_replication_role. For example, from within your psql session: SET SESSION session_replication_role = replica; This will prevent all triggers from firing for the entire session except those defined as "replica". We use this all the time. HTH... -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 terry@turbocorp.com www.turbocorp.com
Teemu Juntunen wrote: > I think this has been asked before, but I can't find the > answer from arcive nor google. How to disable/enable all the > riggers in a database? I have problem with disabled triggers > after database restore. If there is no simple way, could this > be made in a function where you find the table names and > construct the needed commands in strings. If so, how to get > all the tablenames from database? Use the information_schema, see http://www.postgresql.org/docs/current/static/information-schema.html There is a view called "triggers" that will be useful. Yours, Laurenz Albe