--
--  仮受注明細
--      t_pre_order_detail
--

CREATE TABLE t_pre_order_detail
(
    PreOrderNo integer NOT NULL,    -- PriKey
    PreOrderRowNo integer NOT NULL, -- PriKey
    ItemCd integer,                 -- ForKey
    StructCd integer,               -- ForKey
    StockCd integer,                -- ForKey
    count       integer,

    DelFlg boolean,
    AddDate timestamp without time zone,
    UpdDate timestamp without time zone,
    UpdUsrNm character varying(60),
    UpdPcNm character varying(60),
    UpdCnt integer,

    PRIMARY KEY(PreOrderNo, PreOrderRowNo)
);


-- トリガー用関数: t_pre_order_detail
-- DROP FUNCTION g_pre_order_detail();

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

    SELECT INTO title_rec * FROM t_pre_order_title WHERE preorderno = NEW.preorderno;

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

    -- レーベルCDの取得
    SELECT INTO new_preorderrowno MAX(preorderrowno) +1 FROM t_pre_order_detail WHERE preorderno = NEW.preorderno;

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

    -- 更新
    NEW.preorderrowno := new_preorderrowno;
    RETURN NEW;
END;
$$
LANGUAGE 'plpgsql' VOLATILE;

CREATE TRIGGER trigger_t_pre_order_detail
    BEFORE INSERT
        ON t_pre_order_detail
    FOR EACH ROW
    EXECUTE PROCEDURE g_pre_order_detail();

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