这个触发器的作用是:当对表中数据进行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;