-- Create the Triggers used to assign new unique keys to various primary key columns
-- $Id: createSequenceTriggers.sql 888 2007-03-01 16:27:31Z drmills $

CREATE OR REPLACE
TRIGGER ASSIGN_PERSON_ID BEFORE INSERT ON PERSONS
FOR EACH ROW
BEGIN
 IF :NEW.PERSON_ID IS NULL OR :NEW.PERSON_ID < 0 THEN
   SELECT PERSON_SEQ.NEXTVAL
     INTO :NEW.PERSON_ID
     FROM DUAL;
   END IF;
END;
/

CREATE OR REPLACE
TRIGGER ASSIGN_PRODUCT_IMAGE_ID BEFORE INSERT ON PRODUCT_IMAGES
FOR EACH ROW
BEGIN
 IF :NEW.PRODUCT_IMAGE_ID IS NULL OR :NEW.PRODUCT_IMAGE_ID < 0 THEN
   SELECT PRODUCT_IMAGE_SEQ.NEXTVAL
     INTO :NEW.PRODUCT_IMAGE_ID
     FROM DUAL;
   END IF;
END;
/

CREATE OR REPLACE
TRIGGER ASSIGN_ADDRESS_USAGES_ID BEFORE INSERT ON ADDRESS_USAGES
FOR EACH ROW
BEGIN
 IF :NEW.ADDRESS_USAGES_ID IS NULL OR :NEW.ADDRESS_USAGES_ID < 0 THEN
   SELECT ADDRESS_USAGES_SEQ.NEXTVAL
     INTO :NEW.ADDRESS_USAGES_ID
     FROM DUAL;
   END IF;
END;
/

CREATE OR REPLACE
TRIGGER ASSIGN_ADDRESS_ID BEFORE INSERT ON ADDRESSES
FOR EACH ROW
BEGIN
 IF :NEW.ADDRESS_ID IS NULL OR :NEW.ADDRESS_ID < 0 THEN
   SELECT ADDRESS_SEQ.NEXTVAL
     INTO :NEW.ADDRESS_ID
     FROM DUAL;
   END IF;
END;
/

CREATE OR REPLACE
TRIGGER ASSIGN_MEMBERSHIP_ID BEFORE INSERT ON MEMBERSHIPS_BASE
FOR EACH ROW
BEGIN
 IF :NEW.MEMBERSHIP_ID IS NULL OR :NEW.MEMBERSHIP_ID < 0 THEN
   SELECT MEMBER_SEQ.NEXTVAL
     INTO :NEW.MEMBERSHIP_ID
     FROM DUAL;
   END IF;
END;
/

CREATE OR REPLACE
TRIGGER ASSIGN_MEMBERSHIP_TRANS_ID BEFORE INSERT ON MEMBERSHIP_TRANSLATIONS
FOR EACH ROW
BEGIN
 IF :NEW.MEMBERSHIP_TRANSLATIONS_ID IS NULL OR :NEW.MEMBERSHIP_TRANSLATIONS_ID < 0 THEN
   SELECT MEMBER_TRANSLATIONS_SEQ.NEXTVAL
     INTO :NEW.MEMBERSHIP_TRANSLATIONS_ID
     FROM DUAL;
   END IF;
END;
/

CREATE OR REPLACE
TRIGGER ASSIGN_CUSTOMER_INTRSTS_ID BEFORE INSERT ON CUSTOMER_INTERESTS
FOR EACH ROW
BEGIN
 IF :NEW.CUSTOMER_INTERESTS_ID IS NULL OR :NEW.CUSTOMER_INTERESTS_ID < 0 THEN
   SELECT CUSTOMER_INTERESTS_SEQ.NEXTVAL
     INTO :NEW.CUSTOMER_INTERESTS_ID
     FROM DUAL;
   END IF;
END;
/

CREATE OR REPLACE
TRIGGER ASSIGN_DISCOUNT_ID BEFORE INSERT ON DISCOUNTS_BASE
FOR EACH ROW
BEGIN
 IF :NEW.DISCOUNT_ID IS NULL OR :NEW.DISCOUNT_ID < 0 THEN
   SELECT DISCOUNT_SEQ.NEXTVAL
     INTO :NEW.DISCOUNT_ID
     FROM DUAL;
   END IF;
END;
/

CREATE OR REPLACE
TRIGGER ASSIGN_DISCOUNT_TRANS_ID BEFORE INSERT ON DISCOUNT_TRANSLATIONS
FOR EACH ROW
BEGIN
 IF :NEW.DISCOUNT_TRANSLATIONS_ID IS NULL OR :NEW.DISCOUNT_TRANSLATIONS_ID < 0 THEN
   SELECT DISCOUNT_TRANSLATIONS_SEQ.NEXTVAL
     INTO :NEW.DISCOUNT_TRANSLATIONS_ID
     FROM DUAL;
   END IF;
END;
/

CREATE OR REPLACE
TRIGGER ASSIGN_DISCOUNT_TRANS_ID BEFORE INSERT ON DISCOUNT_TRANSLATIONS
FOR EACH ROW
BEGIN
 IF :NEW.DISCOUNT_TRANSLATIONS_ID IS NULL OR :NEW.DISCOUNT_TRANSLATIONS_ID < 0 THEN
   SELECT DISCOUNT_TRANSLATIONS_SEQ.NEXTVAL
     INTO :NEW.DISCOUNT_TRANSLATIONS_ID
     FROM DUAL;
   END IF;
END;
/

CREATE OR REPLACE
TRIGGER ASSIGN_CATEGORY_ID BEFORE INSERT ON PRODUCT_CATEGORIES_BASE
FOR EACH ROW
BEGIN
 IF :NEW.CATEGORY_ID IS NULL OR :NEW.CATEGORY_ID < 0 THEN
   SELECT CATEGORY_SEQ.NEXTVAL
     INTO :NEW.CATEGORY_ID
     FROM DUAL;
   END IF;
END;
/

CREATE OR REPLACE
TRIGGER ASSIGN_PRODUCT_ID BEFORE INSERT ON PRODUCTS_BASE
FOR EACH ROW
BEGIN
 IF :NEW.PRODUCT_ID IS NULL OR :NEW.PRODUCT_ID < 0 THEN
   SELECT PRODUCT_SEQ.NEXTVAL
     INTO :NEW.PRODUCT_ID
     FROM DUAL;
   END IF;
END;
/

CREATE OR REPLACE
TRIGGER ASSIGN_PAYMENT_OPTION_ID BEFORE INSERT ON PAYMENT_OPTIONS
FOR EACH ROW
BEGIN
 IF :NEW.PAYMENT_OPTION_ID IS NULL OR :NEW.PAYMENT_OPTION_ID < 0 THEN
   SELECT PAYMENT_OPTION_SEQ.NEXTVAL
     INTO :NEW.PAYMENT_OPTION_ID
     FROM DUAL;
   END IF;
END;
/

CREATE OR REPLACE
TRIGGER ASSIGN_ORDER_ID BEFORE INSERT ON ORDERS
FOR EACH ROW
BEGIN
 IF :NEW.ORDER_ID IS NULL OR :NEW.ORDER_ID < 0 THEN
   SELECT ORDER_SEQ.NEXTVAL
     INTO :NEW.ORDER_ID
     FROM DUAL;
   END IF;
END;
/


CREATE OR REPLACE
TRIGGER ASSIGN_LINE_ITEM_ID BEFORE INSERT ON ORDER_ITEMS
FOR EACH ROW
BEGIN
 IF :NEW.LINE_ITEM_ID IS NULL OR :NEW.LINE_ITEM_ID < 0 THEN
   SELECT ORDER_ITEMS_SEQ.NEXTVAL
     INTO :NEW.LINE_ITEM_ID
     FROM DUAL;
   END IF;
END;
/

CREATE OR REPLACE
TRIGGER ASSIGN_SHIPPING_OPTION_ID BEFORE INSERT ON SHIPPING_OPTIONS_BASE
FOR EACH ROW
BEGIN
 IF :NEW.SHIPPING_OPTION_ID IS NULL OR :NEW.SHIPPING_OPTION_ID < 0 THEN
   SELECT SHIPPING_OPTION_SEQ.NEXTVAL
     INTO :NEW.SHIPPING_OPTION_ID
     FROM DUAL;
   END IF;
END;
/

CREATE OR REPLACE
TRIGGER ASSIGN_SHIPPING_TRANS_ID BEFORE INSERT ON SHIPPING_OPTION_TRANSLATIONS
FOR EACH ROW
BEGIN
 IF :NEW.SHIPPING_TRANSLATIONS_ID IS NULL OR :NEW.SHIPPING_TRANSLATIONS_ID < 0 THEN
   SELECT SHIPPING_TRANSLATIONS_SEQ.NEXTVAL
     INTO :NEW.SHIPPING_TRANSLATIONS_ID
     FROM DUAL;
   END IF;
END;
/

CREATE OR REPLACE
TRIGGER ASSIGN_SUPPLIER_ID BEFORE INSERT ON SUPPLIERS
FOR EACH ROW
BEGIN
 IF :NEW.SUPPLIER_ID IS NULL OR :NEW.SUPPLIER_ID < 0 THEN
   SELECT SUPPLIER_SEQ.NEXTVAL
     INTO :NEW.SUPPLIER_ID
     FROM DUAL;
   END IF;
END;
/

CREATE OR REPLACE
TRIGGER ASSIGN_WAREHOUSE_ID BEFORE INSERT ON WAREHOUSES
FOR EACH ROW
BEGIN
 IF :NEW.WAREHOUSE_ID IS NULL OR :NEW.WAREHOUSE_ID < 0 THEN
   SELECT SUPPLIER_SEQ.NEXTVAL
     INTO :NEW.WAREHOUSE_ID
     FROM DUAL;
   END IF;
END;
/
