PL/pgSQL can be used to define trigger functions on data changes or database events. A trigger function is created with the CREATE FUNCTION command, declaring it as a function with no arguments and a return type of trigger (for data change triggers) or event_trigger (for database event triggers). Special local variables named TG_something are automatically defined to describe the condition that triggered the call.
The below example will make it more clear. First, we will create a trigger function, then use that in a trigger.
CREATE FUNCTION city_stamp() RETURNS trigger AS $city_stamp$
BEGIN
-- Check that empname and salary are given
IF NEW.name IS NULL THEN
RAISE EXCEPTION 'name cannot be null';
END IF;
IF NEW.pop IS NULL THEN
RAISE EXCEPTION '% cannot have null pop', NEW.pop;
END IF;
IF NEW.pop < 0 THEN
RAISE EXCEPTION '% cannot have a negative pop', NEW.empname;
END IF;
NEW.pop := 100;
RETURN NEW;
END;
$city_stamp$ LANGUAGE plpgsql;
CREATE TRIGGER trg_city_stamp BEFORE INSERT OR UPDATE ON city
FOR EACH ROW EXECUTE FUNCTION city_stamp();
admin
Share post:
PL/pgSQL can be used to define trigger functions on data changes or database events. A trigger function is created with the
CREATE FUNCTIONcommand, declaring it as a function with no arguments and a return type oftrigger(for data change triggers) orevent_trigger(for database event triggers). Special local variables namedTG_are automatically defined to describe the condition that triggered the call.somethingThe below example will make it more clear. First, we will create a trigger function, then use that in a trigger.
CREATE FUNCTION city_stamp() RETURNS trigger AS $city_stamp$ BEGIN -- Check that empname and salary are given IF NEW.name IS NULL THEN RAISE EXCEPTION 'name cannot be null'; END IF; IF NEW.pop IS NULL THEN RAISE EXCEPTION '% cannot have null pop', NEW.pop; END IF; IF NEW.pop < 0 THEN RAISE EXCEPTION '% cannot have a negative pop', NEW.empname; END IF; NEW.pop := 100; RETURN NEW; END; $city_stamp$ LANGUAGE plpgsql;CREATE TRIGGER trg_city_stamp BEFORE INSERT OR UPDATE ON city FOR EACH ROW EXECUTE FUNCTION city_stamp();