I\'m trying to write a trigger function in sql that will update my inventory whe
ID: 669563 • Letter: I
Question
I'm trying to write a trigger function in sql that will update my inventory whenever a purchase is made. I have a purchase table, that handles the purchases and an inventory table. Help writing the correct trigger function?
Here is my item table that is referenced by both the inventory table and the purchase table:
CREATE TABLE item
(
item_id serial NOT NULL,
item_name character varying(100) NOT NULL,
item_price numeric NOT NULL,
last_update timestamp without time zone NOT NULL DEFAULT now(),
CONSTRAINT item_pkey PRIMARY KEY (item_id)
)
This is my inventory table, it is referenced by the purchase table and references the item table:
inventory_id serial NOT NULL,
store_id serial NOT NULL,
item_id serial NOT NULL,
last_update timestamp without time zone NOT NULL DEFAULT now(),
inventory_itemquantity integer,
CONSTRAINT inventory_pkey PRIMARY KEY (inventory_id),
CONSTRAINT inventory_item_id_fkey FOREIGN KEY (item_id)
REFERENCES item (item_id) MATCH SIMPLE
CONSTRAINT inventory_store_id_fkey FOREIGN KEY (store_id)
REFERENCES store (store_id) MATCH SIMPLE
)
This is my purchase table, it references the item table
CREATE TABLE purchase
(
purchase_id integer NOT NULL DEFAULT
purchase_itemquantity numeric NOT NULL,
purchase_date date NOT NULL,
purchase_method character varying(25) NOT NULL,
store_id integer NOT NULL DEFAULT nextval('transaction_store_id_seq'::regclass),
member_id integer NOT NULL DEFAULT nextval('transaction_member_id_seq'::regclass),
purchase_time time without time zone NOT NULL,
item_id serial NOT NULL,
CONSTRAINT transaction_pkey PRIMARY KEY (purchase_id),
CONSTRAINT purchase_item_id_fkey FOREIGN KEY (item_id)
REFERENCES item (item_id) MATCH SIMPLE
CONSTRAINT transaction_member_id_fkey FOREIGN KEY (member_id)
REFERENCES member (member_id) MATCH SIMPLE
CONSTRAINT transaction_store_id_fkey FOREIGN KEY (store_id)
REFERENCES store (store_id) MATCH SIMPLE
)
It seems I'd have to add a foreign key to the purchase table that references the inventory table to make it a bit smoother operation.
This is what I have so far (not much, I know):
CREATE TRIGGER purchase_made
BEFORE UPDATE ON purchase
FOR EACH ROW
EXECUTE PROCEDURE inventoryUpdate();
CREATE OR REPLACE FUNCTION inventoryUpdate(); RETURNS TRIGGER AS
$BODY$
begin
DECLARE
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
Explanation / Answer
CREATE or replace PACKAGE BODY integritypackage AS
END integritypackage;
ALTER TABLE emp ADD update_id NUMBER; -- create flag col.
CREATE TRIGGER dept_cascade1 BEFORE UPDATE OF deptno ON dept
DECLARE
dummy NUMBER;
-- Before updating the DEPT table (this is a statement
-- trigger), generate a new sequence number and assign
-- it to the public variable UPDATESEQ of a user-defined
-- package named INTEGRITYPACKAGE.
BEGIN
SELECT update_sequence.NEXTVAL
INTO dummy
FROM dual;
integritypackage.updateseq := dummy;
END;
CREATE TRIGGER dept_cascade2 AFTER DELETE OR UPDATE
OF deptno ON dept FOR EACH ROW
-- For each department number in DEPT that is updated,
-- cascade the update to dependent foreign keys in the
-- EMP table. Only cascade the update if the child row
-- has not already been updated by this trigger.
BEGIN
IF UPDATING THEN
UPDATE emp
SET deptno = :new.deptno,
update_id = integritypackage.updateseq /*from 1st*/
WHERE emp.deptno = :old.deptno
AND update_id IS NULL;
/* only NULL if not updated by the 3rd trigger
fired by this same triggering statement */
END IF;
IF DELETING THEN
-- Before a row is deleted from DEPT, delete all
-- rows from the EMP table whose DEPTNO is the same as
-- the DEPTNO being deleted from the DEPT table.
DELETE FROM emp
WHERE emp.deptno = :old.deptno;
END IF;
END;
CREATE TRIGGER dept_cascade3 AFTER UPDATE OF deptno ON dept
BEGIN UPDATE emp
SET update_id = NULL
WHERE update_id = integritypackage.updateseq;
END;