Determining if an update inside another trigger function triggered another trigger

Question

I would like to know if the insert / update / delete action was called from inside another trigger function, so that I can apply the trigger in table X, which does not allow inserting / updating / deleting actions if the action is not executed from the trigger function in table Y.

In other words, I would like to have a trigger in table Y when the insert / update / delete action is issued, check if the action was called from the trigger of table X, and if so, continue - otherwise, refuse the action.

I also believe that the "request source" information that is available to the trigger function can potentially help in debugging depending on one trigger hierarchy.

Answer 1

, , . GRANT . :

(= , ), "admin" .

CREATE TABLE x (...);
ALTER TABLE x OWNER to admin; -- Not necessary if "admin" created the table

CREATE TABLE y (...);
ALTER TABLE y OWNER to admin;

"admin" . , (, "app_user" ) x , , y, GRANT :

GRANT SELECT ON x TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON y TO app_user;

y, x; , . , "admin" ( , "admin" CREATE TRIGGER y), SECURITY DEFINER. , "app_user" INSERT INTO y ..., , , x, "admin" :

CREATE FUNCTION my_trigger_func RETURNS trigger AS $$
BEGIN
  -- Perform some operation on x
  RETURN NEW; -- or OLD
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

ALTER FUNCTION my_trigger_func OWNER TO admin; -- If needed
REVOKE ALL ON FUNCTION my_trigger_func FROM public;

CREATE TRIGGER my_trigger
  BEFORE INSERT, UPDATE, DELETE ON y -- or AFTER, depending on your needs
  FOR EACH ROW EXECUTE PROCEDURE my_trigger_func();

, GRANT EXECUTE : "app_user" y, , , "app_user" .

, , "app_user" x, - y. "admin" x, , , , x.