Can I have an example please? Or a link
Hi
Hello,
Is it possible to call a function inside a trigger function ?
Any idea or link are welcome. Thanks in advance
sure, there is not any limit.
CREATE OR REPLACE FUNCTION allow_update(d date)
RETURNS bool AS $$
BEGIN
RETURN EXTRACT(YEAR FROM d) = EXTRACT(YEAR FROM current_date);
END;
$$ LANGUAGE plpgsql;
-- allow update record only from current year
CREATE OR REPLACE FUNCTION trg_func()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
IF NOT allow_update(NEW.inserted) THEN
RAISE EXCEPTION 'cannot insert';
END IF;
ELSE IF TG_OP = 'UPDATE' THEN
IF NOT allow_update(NEW.inserted) OR NOT allow_update(OLD.inserted) THEN
RAISE EXCEPTION 'cannot update';
END IF;
ELSE
IF NOT allow_update(OLD.inserted) THEN
RAISE EXCEPTION 'cannot delete';
END IF;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER AFTER INSERT OR UPDATE OR DELETE ON sometab
FOR EACH ROW EXECUTE FUNCTION trg_func();
Regards
Pavel
p.s. You can do everything in trigger - Postgres is not Oracle where there were some issues (if my memory serves well). There is only one risk - possible recursion