The solution proposed by Ziggy will work, but if you don't want to go that deep by having to create a privileged user just to execute the trigger, there's another solution you can try: Create a validation trigger on the product_stock table that checks if the update is coming from the stock_transaction_log trigger. To do that, we can create a temporary table to serve as a "global variable".
CREATE OR REPLACE FUNCTION validate_product_stock() RETURNS trigger AS $$
DECLARE
count_ int;
BEGIN
BEGIN
SELECT COUNT(1) INTO count_ FROM __inside_stl_trigger WHERE note = 'inside trigger';
EXCEPTION WHEN undefined_table THEN
count_ := 0;
END;
IF count_ = 0 THEN
RAISE EXCEPTION 'This table is automatically updated by stock_transaction_log'
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER validate_product_stock_trigger
BEFORE INSERT OR UPDATE ON product_stock
FOR EACH ROW
EXECUTE PROCEDURE validate_product_stock();
CREATE OR REPLACE FUNCTION update_product_stock() RETURNS trigger AS $$
BEGIN
CREATE TEMPORARY TABLE __inside_stl_trigger (note text NOT NULL);
INSERT INTO __inside_stl_trigger (note) VALUES ('inside trigger');
-- Update the product_stock table here
DROP TABLE __inside_stl_trigger;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER update_product_stock_trigger
BEFORE INSERT OR UPDATE ON stock_transaction_log
FOR EACH ROW
EXECUTE PROCEDURE update_product_stock();
Obviously, one could create that temporary table by hand, insert a row on it and do the update, but at least you will make it harder.
