--
--  名称
--      t_name
--

CREATE TABLE t_name
(
    namediv         integer NOT NULL,       -- prikey
    namecd          integer NOT NULL,       -- prikey
    name            character varying(256),
    disppriority    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(namediv, namecd)
);


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


-- トリガー用関数（追加）: t_name
CREATE OR REPLACE FUNCTION g_name_ins()
  RETURNS "trigger" AS
$BODY$
DECLARE
    new_namecd integer;
    new_disppriority integer;
BEGIN
    -- 必須チェック
    IF NEW.namediv IS NULL THEN
        RAISE EXCEPTION '引数 namediv は必須です';
    END IF;

    -- namecdの自動設定
    IF NEW.namecd IS NULL THEN
        SELECT INTO new_namecd MAX(namecd) +1
          FROM t_name
         WHERE namediv = NEW.namediv;
        IF new_namecd IS NULL THEN
            new_namecd := 1;
        END IF;
        NEW.namecd := new_namecd;
    END IF;

    -- disppriorityの自動設定
    IF NEW.disppriority IS NULL THEN
        IF NEW.namecd > 1 THEN
            SELECT INTO new_disppriority MAX(disppriority) +1
              FROM t_name
             WHERE namediv = NEW.namediv
               AND namecd > 1;
        ELSE
            SELECT INTO new_disppriority MAX(disppriority) +1
              FROM t_name
             WHERE substring(namediv, 1, 1) = substring(NEW.namediv, 1, 1)
               AND namecd = 1;
        END IF;

        IF new_disppriority IS NULL THEN
            new_disppriority := 1;
        END IF;
        NEW.disppriority := new_disppriority;
    END IF;

    -- 更新
    RETURN NEW;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

-- トリガ
DROP TRIGGER trigger_t_name
  ON t_name;
CREATE TRIGGER trigger_t_name
  BEFORE INSERT OR UPDATE
  ON t_name
  FOR EACH ROW
  EXECUTE PROCEDURE g_name_ins();


-- トリガー用関数（削除）: t_name
CREATE OR REPLACE FUNCTION g_name_del()
  RETURNS "trigger" AS
$BODY$
BEGIN
    -- 必須チェック
    IF OLD.namediv IS NULL THEN
        RAISE EXCEPTION '引数 namediv は必須です';
    END IF;

    -- 名称CがNULLか"1"の時はその名称区分全部を削除
    IF OLD.namecd IS NULL OR OLD.namecd = 1 THEN
        DELETE FROM t_name
         WHERE namediv = OLD.namediv;
    END IF;

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

-- トリガ
DROP TRIGGER trigger_t_name2
  ON t_name;
CREATE TRIGGER trigger_t_name2
  AFTER DELETE
  ON t_name
  FOR EACH ROW
  EXECUTE PROCEDURE g_name_del();
