PostgreSQL触发器应用一例

[9812]

CREATE FUNCTION plpgsql_call_handler ()
RETURNS OPAQUE
AS '/usr/lib/pgsql/plpgsql.so'
LANGUAGE 'C';
CREATE LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler
LANCOMPILER 'PL/pgSQL'







CREATE FUNCTION check_shipment_addition () RETURNS opaque AS '
DECLARE
id_number INTEGER;
book_isbn TEXT;
BEGIN

SELECT INTO id_number id FROM customers WHERE id = NEW.customer_id;

IF NOT FOUND THEN
RAISE EXCEPTION ''Invalid customer ID number.'';
END IF;

SELECT INTO book_isbn isbn FROM editions WHERE isbn = NEW.isbn;

IF NOT FOUND THEN
RAISE EXCEPTION ''Invalid ISBN.'';
END IF;

IF TG_OP = ''INSERT'' THEN
UPDATE stock SET stock = stock -1 WHERE isbn = NEW.isbn;
END IF;

RETURN NEW;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER check_shipment
BEFORE INSERT OR UPDATE
ON shipments FOR EACH ROW
EXECUTE PROCEDURE check_shipment_addition();

drop table taba;
create table taba(
id integer,
name varchar(50)
);
insert into taba values(1,'chen');
insert into taba values(2,'chen');
insert into taba values(3,'chen');
insert into taba values(4,'chen');
insert into taba values(5,'chen');
insert into taba values(6,'chen');
insert into taba values(7,'chen');
select * from taba;


drop table tabb;
create table tabb(
id integer,
name varchar(50)
);
insert into tabb values(1,'chen');
insert into tabb values(2,'chen');
insert into tabb values(3,'chen');
insert into tabb values(4,'chen');
insert into tabb values(5,'chen');
insert into tabb values(6,'chen');
insert into tabb values(7,'chen');
select * from tabb;

drop function delete_tab ();
CREATE FUNCTION delete_tab () RETURNS opaque AS '
-- DECLARE
BEGIN
Delete from tabb where id = OLD.id;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';

drop TRIGGER delete_tri on taba;
CREATE TRIGGER delete_tri
BEFORE Delete
ON taba FOR EACH ROW
EXECUTE PROCEDURE delete_tab ();

delete from taba where id = 2;
select * from tabb;