--
--  レーベル
--  t_label
--

-- DROP TABLE t_label;

CREATE TABLE t_label
(
    MakerCd integer NOT NULL,       -- PriKey
    LabelCd integer NOT NULL,       -- PriKey
    LabelNm character varying(256),
    LabelNmKana 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, LabelCd)
);


-- トリガー用関数: t_label
-- DROP FUNCTION g_lable();

CREATE OR REPLACE FUNCTION g_lable()
    RETURNS "trigger" AS
$$
DECLARE
    maker_rec RECORD;
    new_labelcd 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;

    -- レーベルCDの取得
    SELECT INTO new_labelcd MAX(labelcd) +1 FROM t_label
     WHERE makercd = NEW.makercd;

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

    -- 更新
    NEW.labelcd := new_labelcd;
    RETURN NEW;
END;
$$
LANGUAGE 'plpgsql' VOLATILE;


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

CREATE TRIGGER trigger_t_label
    BEFORE INSERT
        ON t_label
    FOR EACH ROW
    EXECUTE PROCEDURE g_lable();

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