Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
Given a function like this:
CREATE OR REPLACE FUNCTION foo()
RETURNS TRIGGER AS $$
BEGIN
-- do something fancy
END;
$$
LANGUAGE plpgsql;
which is used in a TRIGGER:
CREATE TRIGGER bar
BEFORE INSERT
ON test
FOR each ROW
EXECUTE PROCEDURE foo();
Does the TRIGGER need to be re-created (DROP + CREATE) if the function foo() is changed (using REPLACE)?
Was coming across this while reading about partitioning.
-- Create the function foo()
CREATE OR REPLACE FUNCTION foo()
RETURNS TRIGGER AS $$
BEGIN
-- do something fancy
-- You should include the appropriate logic here.
RETURN NEW; -- For example, return NEW; if it's a row-level trigger.
END;
$$
LANGUAGE plpgsql;
-- Create the trigger bar
CREATE TRIGGER bar
BEFORE INSERT
ON test
FOR EACH ROW
EXECUTE PROCEDURE foo();
use the CREATE OR REPLACE FUNCTION syntax to modify the function foo(), the trigger bar will automatically use the updated function without needing to be re-created (DROP + CREATE). The trigger will automatically reference the latest version of the function definition.
