--
--  品目基本属性
--      t_item_basic_attr
--

-- DROP TABLE t_item_basic_attr;

CREATE TABLE t_item_basic_attr
(
    itemcd              serial NOT NULL,            -- PriKey
    itemnm              character varying(256),
    itemnmkana          character varying(256),
    makercd             integer,                    -- ForKey
    itemexp             varchar(4000),

    option01            varchar(1024),
    option02            varchar(1024),
    option03            varchar(1024),
    option04            varchar(1024),
    option05            varchar(1024),
    option06            varchar(1024),
    option07            varchar(1024),
    option08            varchar(1024),
    option09            varchar(1024),
    option10            varchar(1024),

    stock_upddate       timestamp without time zone,
    lowprice            integer,
    oldlowprice         integer,
    newlowprice         integer,
    min_salestartdate   date,

    delflg          boolean,
    adddate         timestamp without time zone,
    upddate         timestamp without time zone,
    updusrnm        character varying(60),
    updpcnm         character varying(60),
    updcnt          integer,

    PRIMARY KEY(itemcd)
);

-- シリアル初期値
SELECT setval('t_item_basic_attr_itemcd_seq', 100000);



-- Trigger: trigger_master_stamp on t_item_basic_attr

-- DROP TRIGGER trigger_master_stamp ON t_item_basic_attr;

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



-- 更新用トリガ
-- Function: g_item_basic_attr()

-- DROP FUNCTION g_item_basic_attr();

CREATE OR REPLACE FUNCTION g_item_basic_attr()
  RETURNS "trigger" AS
$BODY$
DECLARE
BEGIN
    -- 更新
    IF (TG_OP = 'UPDATE' AND OLD.delflg = FALSE AND NEW.delflg = TRUE) THEN
        -- 品目構成
        UPDATE t_item_struct
           SET delflg   = NEW.delflg,
               updusrnm = NEW.updusrnm,
               updpcnm  = NEW.updpcnm
         WHERE itemcd = NEW.itemcd;

        -- 品目ウェブ属性
        UPDATE t_item_web_attr
           SET delflg   = NEW.delflg,
               updusrnm = NEW.updusrnm,
               updpcnm  = NEW.updpcnm
         WHERE itemcd = NEW.itemcd;

        -- 出品在庫明細
        UPDATE t_ex_stock_detail
           SET delflg   = NEW.delflg,
               updusrnm = NEW.updusrnm,
               updpcnm  = NEW.updpcnm
         WHERE itemcd = NEW.itemcd;

        -- 写真
        UPDATE t_image
           SET delflg   = NEW.delflg,
               updusrnm = NEW.updusrnm,
               updpcnm  = NEW.updpcnm
         WHERE typediv   = 1
           AND generalcd = NEW.itemcd;

        -- タグ
        UPDATE t_tag
           SET delflg   = NEW.delflg,
               updusrnm = NEW.updusrnm,
               updpcnm  = NEW.updpcnm
         WHERE itemcd = NEW.itemcd;

        -- レビュー
        UPDATE t_review
           SET delflg   = NEW.delflg,
               updusrnm = NEW.updusrnm,
               updpcnm  = NEW.updpcnm
         WHERE itemcd = NEW.itemcd;

        -- 検索キーワード
        UPDATE t_search_key
           SET delflg   = NEW.delflg,
               updusrnm = NEW.updusrnm,
               updpcnm  = NEW.updpcnm
         WHERE itemcd = NEW.itemcd;

        -- ジャンル品目
        UPDATE t_genre_item
           SET delflg   = NEW.delflg,
               updusrnm = NEW.updusrnm,
               updpcnm  = NEW.updpcnm
         WHERE itemcd = NEW.itemcd;

        -- キャスト品目
        UPDATE t_cast_item
           SET delflg   = NEW.delflg,
               updusrnm = NEW.updusrnm,
               updpcnm  = NEW.updpcnm
         WHERE itemcd = NEW.itemcd;

        -- ウォッチリスト
        UPDATE t_watch_list
           SET delflg   = NEW.delflg,
               updusrnm = NEW.updusrnm,
               updpcnm  = NEW.updpcnm
         WHERE itemcd = NEW.itemcd;

        -- リクエスト
        UPDATE t_request
           SET delflg   = NEW.delflg,
               updusrnm = NEW.updusrnm,
               updpcnm  = NEW.updpcnm
         WHERE itemcd = NEW.itemcd;

        -- アクセス数
        UPDATE t_access
           SET delflg   = NEW.delflg,
               updusrnm = NEW.updusrnm,
               updpcnm  = NEW.updpcnm
         WHERE itemcd = NEW.itemcd;

        -- お勧め
        UPDATE t_recommend
           SET delflg   = NEW.delflg,
               updusrnm = NEW.updusrnm,
               updpcnm  = NEW.updpcnm
         WHERE itemcd = NEW.itemcd;

    END IF;

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


-- DROP TRIGGER trigger_t_item_basic_attr ON t_item_basic_attr;

CREATE TRIGGER trigger_t_item_basic_attr
    AFTER UPDATE
        ON t_item_basic_attr
    FOR EACH ROW
    EXECUTE PROCEDURE g_item_basic_attr();
