--
--  ダウンロード履歴
--      t_dl_history
--

-- DROP TABLE t_dl_history;

CREATE TABLE t_dl_history
(
  orderno           integer NOT NULL,               -- PriKey
  orderrowno        integer NOT NULL,               -- PriKey
  dlhistoryno       integer NOT NULL,               -- PriKey
  dldate            timestamp without time zone,
  dluserip          varchar(256),
  dluseragent       varchar(512),
  
  delflg        boolean,
  adddate       timestamp without time zone,
  upddate       timestamp without time zone,
  updusrnm      varchar(60),
  updpcnm       varchar(60),
  updcnt        integer,
  
  PRIMARY KEY (orderno, orderrowno, dlhistoryno)
);

--
-- トリガ
--

-- DROP TRIGGER trigger_master_stamp ON t_dl_history;

CREATE TRIGGER trigger_master_stamp
  BEFORE INSERT OR UPDATE
  ON t_dl_history
  FOR EACH ROW
  EXECUTE PROCEDURE g_master_stamp();

-- トリガー用関数: t_dl_history
CREATE OR REPLACE FUNCTION g_dl_history()
  RETURNS "trigger" AS
$BODY$
DECLARE
    dl_order_rec RECORD;
    dlhistoryno_new integer;
BEGIN
    IF NEW.orderno IS NULL THEN
        RAISE EXCEPTION '引数 orderno は必須です';
    END IF;
    IF NEW.orderrowno IS NULL THEN
        RAISE EXCEPTION '引数 orderrowno は必須です';
    END IF;
    SELECT INTO dl_order_rec * FROM t_dl_order WHERE orderno = NEW.orderno AND orderrowno = NEW.orderrowno;
    IF NOT FOUND THEN
        RAISE EXCEPTION 'ダウンロード受注データが見つかりません';
    END IF;

    SELECT INTO dlhistoryno_new MAX(dlhistoryno) +1 FROM t_dl_history WHERE orderno = NEW.orderno AND orderrowno = NEW.orderrowno;

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

    -- æ›´æ–°
    NEW.dlhistoryno := dlhistoryno_new;
    RETURN NEW;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

-- トリガ
CREATE TRIGGER trigger_t_dl_history
  BEFORE INSERT
  ON t_dl_history
  FOR EACH ROW
  EXECUTE PROCEDURE g_dl_history();