--
--  シリーズ
--      t_series
--

-- DROP TABLE t_series;

CREATE TABLE t_series
(
    makercd         integer NOT NULL,       -- PriKey
    seriescd        integer NOT NULL,       -- PriKey
    seriesnm        character varying(256),
    seriesnmkana    character varying(256),
    recommenddiv    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(makercd, seriescd)
);


-- シリーズＣ更新用トリガ
-- Function: g_series()

-- DROP FUNCTION g_series();

CREATE OR REPLACE FUNCTION g_series()
  RETURNS "trigger" AS
$BODY$
DECLARE
    maker_rec RECORD;
    new_seriescd integer;
BEGIN
    -- メーカーCDのチェック
    IF NEW.makercd IS NULL THEN
        RAISE EXCEPTION '引数 makercd は必須です';
    END IF;
    SELECT INTO maker_rec * FROM t_maker
     WHERE makercd = NEW.makercd;
    IF NOT FOUND THEN
        RAISE EXCEPTION '引数 makercd % のデータが見つかりません', NEW.makercd;
    END IF;

    if NEW.seriescd IS NULL THEN
      -- シリーズCDの取得
      SELECT INTO new_seriescd MAX(seriescd) +1 FROM t_series
       WHERE makercd = NEW.makercd;

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

      -- 更新
      NEW.seriescd := new_seriescd;
    END IF;

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


-- トリガー:
-- DROP TRIGGER trigger_master_stamp ON t_series;

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


-- DROP TRIGGER trigger_t_series ON t_series;

CREATE TRIGGER trigger_t_series
    BEFORE INSERT
        ON t_series
    FOR EACH ROW
    EXECUTE PROCEDURE g_series();
