一个postgresSQL的触发器（出库--入库--库存）

-- Function: stats_remnant_outstore()

-- DROP FUNCTION stats_remnant_outstore();
--出库表
create table out_store(
oid serial primary key,
o_name text,
o_count integer,
o_time timestamp
)
--入库表
create table in_store(
iid serial primary key,
i_name text,
i_count integer,
i_time timestamp
)
--库存表
create table remnant_inventory(
rid serial primary key,
r_name text,
r_count integer,
r_time timestamp
)
--出库
CREATE OR REPLACE FUNCTION stats_remnant_out_store()
RETURNS trigger AS
$BODY$
declare
check_name varchar(40);
BEGIN
IF(TG_OP='INSERT') THEN
select r_name into check_name from remnant_inventory where r_name=NEW.o_name;
IF (check_name is null) THEN
raise exception'库存无该货物';
ELSE
IF (select r_count from remnant_inventory where r_name=NEW.o_name) >= NEW.o_count THEN
UPDATE remnant_inventory SET r_count=r_count - NEW.o_count,r_time=now() where r_name=NEW.o_name;
ELSE
raise exception'库存不足';
END IF;
END IF;
END IF;
IF(TG_OP='UPDATE') THEN
UPDATE remnant_inventory SET r_count=(r_count + OLD.o_count - NEW.o_count),r_time=now() WHERE r_name=NEW.o_name;
END IF;
return NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION stats_remnant_out_store() OWNER TO postgres;

--入库
CREATE OR REPLACE FUNCTION stats_remnant_in_store()
RETURNS trigger AS
$BODY$
declare
check_name varchar(40);
BEGIN
IF(TG_OP='INSERT') THEN
select r_name into check_name from remnant_inventory where r_name=NEW.i_name;
IF(check_name is not null) THEN
UPDATE remnant_inventory SET r_count=r_count + NEW.i_count,r_time=now() where r_name=NEW.i_name;
ELSE
insert into remnant_inventory (r_name,r_count,r_time) values (NEW.i_name,NEW.i_count,now());
END IF;
END IF;
IF(TG_OP='UPDATE') THEN
UPDATE remnant_inventory SET r_count=(r_count - OLD.i_count + NEW.i_count),r_time=now() WHERE r_name=NEW.i_name;
END IF;
return NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION stats_remnant_in_store() OWNER TO postgres;