pgsql触发器

需求：根据源表的增删改变化，修改目标表数据
step1：创建触发器函数

CREATE OR REPLACE FUNCTION update_res_qrcode_test()
RETURNS TRIGGER AS $$
BEGIN
	IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
		UPDATE res_qrcode_test 
		SET address = NEW.address 
		WHERE ID = NEW.ID;
		IF NOT FOUND THEN
			INSERT INTO res_qrcode_test (ID, address)
			VALUES (NEW.ID, NEW.address);
		END IF;
	ELSIF TG_OP = 'DELETE' THEN
		DELETE FROM res_qrcode_test
		WHERE ID = OLD.ID;
	END IF;
	IF TG_OP = 'DELETE' THEN
		RETURN OLD;
	ELSE
		RETURN NEW;
	END IF;
END;
$$ LANGUAGE plpgsql;

step2：创建触发器

CREATE TRIGGER res_qrcode_test
AFTER INSERT OR UPDATE OR DELETE ON res_qrcode
FOR EACH ROW
EXECUTE FUNCTION update_res_qrcode_test()