【Postgresql-9.6.8】触发器实例（记录增、删、改）

这个触发器的作用是：当对表中数据进行INSERT、DELETE、UPDATE时，同时更新另一张表中的相关字段。

（此实例参考于PG官方文档）

--主表 - 时间维度和销售事实。
create table time_dimension(
time_key  integer not null,
day_of_week  integer not null,
day_of_month integer not null,
month  integer not null,
quarter  integer not null,
year   integer not null
);
create unique index time_dimension_key on time_dimension(time_key);


create table sales_fact(
time_key  integer not null,
product_key  integer not null,
store_key  integer not null,
amount_sold  numeric(12,2) not null,
units_sold  integer not null,
amount_cost  numeric(12,2) not null
);
create unique index sales_fact_time on sales_fact(time_key);

--汇总表 - 按时间汇总销售
create table sales_summary_bytime(
time_key  integer not null,
amount_sold  numeric (15,2) not null,
units_sold  numeric(12) not null,
amount_cost  numeric(15,2) not null
);
create unique index sales_summary_bytime_key on sales_summary_bytime(time_key);

--在 UPDATE、INSERT、DELETE 时修改汇总列的函数和触发器。

create or replace function maint_sales_summary_bytime() returns trigger
as $maint_sales_summary_bytime$
declare
delta_time_key integer;
delta_amount_sold numeric(15,2);
delta_units_sold  numeric(12) ;
delta_amount_cost  numeric(15,2);
begin
--算出增量/减量数。
if (TG_OP='DELETE') then
	delta_time_key=OLD.time_key;
	delta_amount_sold=-1*OLD.amount_sold;
	delta_units_sold=-1*OLD.units_sold;
	delta_amount_cost=-1*OLD.amount_cost;
elsif (TG_OP='UPDATE') then
--禁止更改 the time_key 的更新
--（可能不会太麻烦，因为大部分的更改是用 DELETE + INSERT 完成的）。
	if (OLD.time_key != NEW.time_key) then
		raise exception 'update of time_key:%->% not allowed',OLD.time_key,NEW.time_key;
	end if;

	delta_time_key=OLD.time_key;
	delta_amount_sold=NEW.amount_sold-OLD.amount_sold;
	delta_units_sold=NEW.units_sold-OLD.units_sold;
	delta_amount_cost=NEW.units_sold-OLD.units_sold;

ELSIF (TG_OP='INSERT') THEN
	delta_time_key=NEW.time_key;
	delta_amount_sold=NEW.amount_sold;
	delta_units_sold=NEW.units_sold;
	delta_amount_cost=NEW.units_sold;
	
END IF;
	
-- 插入或更新带有新值的汇总行。
<<insert_update>>
loop
	update sales_summary_bytime
		set amount_sold = amount_sold + delta_amount_sold,
		units_sold = units_sold + delta_units_sold,
		amount_cost= amount_cost + delta_amount_cost
	where time_key = delta_time_key;

	exit insert_update when found;

	begin
		insert into sales_summary_bytime (
		time_key,
		amount_sold,
		units_sold,
		amount_cost)
		values(
		delta_time_key,
		delta_amount_sold,
		delta_units_sold,
		delta_amount_cost
		);

		exit insert_update;

	exception
		when unique_violation then
			-- 什么也不做
	end;
end loop insert_update;
return null;
end;
$maint_sales_summary_bytime$ language plpgsql;

create trigger maint_sales_summary_bytime
after insert or update or delete on sales_fact
	for each row execute procedure maint_sales_summary_bytime();


insert into sales_fact values (1,1,1,10,3,15);
INSERT INTO sales_fact VALUES(2,2,1,40,15,135);
select * from sales_fact;

SELECT * FROM sales_summary_bytime;
DELETE FROM sales_fact WHERE product_key = 1;
SELECT * FROM sales_summary_bytime;
UPDATE sales_fact SET units_sold = units_sold * 2;
SELECT * FROM sales_summary_bytime;