-- idea is to prevent deletion of coitems if there is 


CREATE OR REPLACE FUNCTION coitem_delete_trigger() RETURNS trigger 
AS $BODY$
DECLARE
     v_tmp_id INT;
BEGIN
         
    IF (
            (OLD.coitem_qtyshipped > 0)
            AND
            (OLD.coitem_qtyreturned !=  OLD.coitem_qtyshipped)
    ) THEN
        RAISE EXCEPTION 'You can not delete order lines that have already been shipped';
    END IF;
    
   RETURN OLD;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
ALTER FUNCTION  coitem_delete_trigger()
  OWNER TO admin;


GRANT ALL ON FUNCTION coitem_delete_trigger() TO xtrole;

CREATE TRIGGER _coitem_delete_trigger 
    BEFORE DELETE  ON  coitem
        FOR EACH ROW EXECUTE PROCEDURE coitem_delete_trigger();
        