Trigger Functions
- pg_get_triggerdef(oid)
Description: Obtains the definition information of a trigger.
Parameter: OID of the trigger to be queried
Return type: text
Example:
-- Create the tri_insert table. gaussdb=# CREATE TABLE tri_insert (a int, b int) distribute by hash(a); CREATE TABLE -- Create the trigger_func function. gaussdb=# CREATE FUNCTION trigger_func() RETURNS trigger LANGUAGE plpgsql AS ' BEGIN RAISE NOTICE ''trigger_func(%) called: action = %, when = %, level = %'', TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL; RETURN NULL; END;'; CREATE FUNCTION -- Create the before_ins_stmt_trig trigger. gaussdb=# CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON tri_insert FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('before_ins_stmt'); CREATE TRIGGER -- Create the after_ins_when_trig trigger. gaussdb=# CREATE TRIGGER after_ins_when_trig AFTER INSERT ON tri_insert FOR EACH ROW WHEN (new.a IS NOT NULL) EXECUTE PROCEDURE trigger_func('after_ins_when'); CREATE TRIGGER -- View the trigger definition information of the tri_insert table. gaussdb=# SELECT pg_get_triggerdef(oid) FROM pg_trigger WHERE tgrelid = 'tri_insert'::regclass; pg_get_triggerdef -------------------------------------------------------------------------------------------------------------------------------------------------------- CREATE TRIGGER after_ins_when_trig AFTER INSERT ON tri_insert FOR EACH ROW WHEN ((new.a IS NOT NULL)) EXECUTE PROCEDURE trigger_func('after_ins_when') CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON tri_insert FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('before_ins_stmt') (2 rows)
- pg_get_triggerdef(oid, boolean)
Description: Obtains the definition information of a trigger.
Parameter: OID of the trigger to be queried and whether it is displayed in pretty mode
Boolean parameters take effect only when the WHEN condition is specified during trigger creation.
Return type: text
Example:
-- View the trigger definition information of the tri_insert table in non-pretty mode. gaussdb=# SELECT pg_get_triggerdef(oid, false) FROM pg_trigger WHERE tgrelid = 'tri_insert'::regclass; pg_get_triggerdef -------------------------------------------------------------------------------------------------------------------------------------------------------- CREATE TRIGGER after_ins_when_trig AFTER INSERT ON tri_insert FOR EACH ROW WHEN ((new.a IS NOT NULL)) EXECUTE PROCEDURE trigger_func('after_ins_when') CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON tri_insert FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('before_ins_stmt') (2 rows) -- View the trigger definition information of the tri_insert table in pretty mode. gaussdb=# SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'tri_insert'::regclass; pg_get_triggerdef ------------------------------------------------------------------------------------------------------------------------------------------------------ CREATE TRIGGER after_ins_when_trig AFTER INSERT ON tri_insert FOR EACH ROW WHEN (new.a IS NOT NULL) EXECUTE PROCEDURE trigger_func('after_ins_when') CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON tri_insert FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('before_ins_stmt') (2 rows) -- Clear the tri_insert table. gaussdb=# DROP TABLE tri_insert CASCADE; DROP TABLE -- Clear the trigger_func function. gaussdb=# DROP FUNCTION trigger_func; DROP FUNCTION
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot