--
--  受注明細
--      t_order_detail
--

-- DROP TABLE t_order_detail;

CREATE TABLE t_order_detail
(
    orderno     integer not null,   -- PriKey
    orderrowno  integer not null,   -- PriKey
    typediv     integer,
    itemcd      integer,            -- ForKey
    structcd    integer,            -- ForKey
    stockcd     integer,            -- ForKey
    price       integer,
    count       integer,

    preorderflg      boolean,

    delflg      boolean,
    adddate     timestamp without time zone,
    upddate     timestamp without time zone,
    updusrnm    character varying(60),
    updpcnm     character varying(60),
    updcnt      integer,

    PRIMARY KEY(OrderNo, OrderRowNo)
);

CREATE OR REPLACE FUNCTION g_order_detail()
    RETURNS "trigger" AS
$$
DECLARE
    title_rec RECORD;
    new_orderrowno integer;
BEGIN
    -- メーカーCDのチェック
    IF NEW.orderno IS NULL THEN
        RAISE EXCEPTION '引数 orderno は必須です';
    END IF;

    SELECT INTO title_rec * FROM t_order_title WHERE orderno = NEW.orderno;

    IF NOT FOUND THEN
        RAISE EXCEPTION '引数 orderno % のデータが見つかりません', NEW.orderno;
    END IF;

    -- レーベルCDの取得
    SELECT INTO new_orderrowno MAX(orderrowno) +1 FROM t_order_detail WHERE orderno = NEW.orderno;

    IF new_orderrowno IS NULL THEN
        new_orderrowno := 1;
    END IF;

    -- 更新
    NEW.orderrowno := new_orderrowno;
    RETURN NEW;
END;
$$
LANGUAGE 'plpgsql' VOLATILE;

CREATE TRIGGER trigger_t_order_detail
    BEFORE INSERT
        ON t_order_detail
    FOR EACH ROW
    EXECUTE PROCEDURE g_order_detail();

-- トリガ
CREATE TRIGGER trigger_master_stamp
  BEFORE INSERT OR UPDATE
  ON t_order_detail
  FOR EACH ROW
  EXECUTE PROCEDURE g_master_stamp();



-- トリガー用関数: t_order_detail
-- 品目基本情報の最低価格を更新
-- DROP g_order_detail2();
CREATE OR REPLACE FUNCTION g_order_detail2()
    RETURNS "trigger" AS $BODY$
DECLARE
    _price  integer;
BEGIN
    -- 更新
    IF NEW.delflg = FALSE AND NEW.itemcd IS NOT NULL THEN

        -- 全て
        SELECT * INTO _price FROM s_get_lowprice(1, NEW.itemcd);
        IF NOT FOUND THEN
            _price := NULL;
        END IF;
        UPDATE t_item_basic_attr SET lowprice = _price
         WHERE itemcd = NEW.itemcd;

        -- 中古
        SELECT * INTO _price FROM s_get_lowprice(2, NEW.itemcd);
        IF NOT FOUND THEN
            _price := NULL;
        END IF;
        UPDATE t_item_basic_attr SET oldlowprice = _price
         WHERE itemcd = NEW.itemcd;

        -- 新品
        SELECT * INTO _price FROM s_get_lowprice(3, NEW.itemcd);
        IF NOT FOUND THEN
            _price := NULL;
        END IF;
        UPDATE t_item_basic_attr SET newlowprice = _price
         WHERE itemcd = NEW.itemcd;

    END IF;

    RETURN NULL;
END;
$BODY$ LANGUAGE 'plpgsql' VOLATILE;


-- トリガ登録
CREATE TRIGGER trigger_order_detail2
    AFTER INSERT OR UPDATE
    ON t_order_detail
    FOR EACH ROW
    EXECUTE PROCEDURE g_order_detail2();
