TRIGGER & PROCEDURE

HELP TRIGGER MAPA_INT_ORG_HST;

Name ActionTime Decimal Order Value Creation TimeStamp Event Kind Enabled Comment
Trig_insert_int_org           A 32,767 2009-03-17 16:39:21 I R Y ?


SHOW TRIGGER Trig_insert_int_org

REPLACE TRIGGER msttst.Trig_insert_int_org
AFTER INSERT ON msttst.MAPA_INT_ORG_HST
REFERENCING NEW AS NewRow
FOR EACH ROW
(
CALL msttst.Proc_insert_dummy_int_org(NewRow.id_int_org_pty,NewRow.user_crt,NewRow.dttm_crt,NewRow.user_upd,NewRow.dttm_upd);
);


SHOW PROCEDURE Proc_insert_dummy_int_org

REPLACE PROCEDURE msttst.Proc_insert_dummy_int_org(
IN Internal_Org_Party_Id  CHAR(4),
IN CREATED_BY    VARCHAR(20),
IN CREATION_DATE   TIMESTAMP,
IN UPDATED_BY    VARCHAR(20),
IN UPDATED_DATE    TIMESTAMP
)

EXIT_PROC:

BEGIN

DECLARE var_Key1    VARCHAR(30);
DECLARE var_Key2   VARCHAR(30);

DECLARE var_DmyCdPref   VARCHAR(10);
DECLARE var_DmyCd   VARCHAR(200);

--MAIO_INT_DPT_HST
DECLARE chr_Id_Int_Org_Pty  CHAR(4);
DECLARE var_Id_Int_Dpt_Pty  VARCHAR(50);
DECLARE dat_dt_pty_strt   DATE;
DECLARE dat_dt_pty_end   DATE;
DECLARE var_Nm_Int_Dpt_Eng  VARCHAR(50);
DECLARE var_Nm_Int_Dpt_Int  VARCHAR(50);
DECLARE var_Nm_Int_Dpt_Abr  VARCHAR(50);
DECLARE var_Dsc_Int_Dpt   VARCHAR(50);
DECLARE chr_Cd_Int_Dpt_Tp  CHAR(2);
DECLARE var_Id_Par_Int_Dpt_Pty  VARCHAR(50);
DECLARE var_Flg_Dmy   VARCHAR(1);
DECLARE var_User_Crt   VARCHAR(10);

DECLARE var_Dttm_Crt   TIMESTAMP;

DECLARE var_User_Upd   VARCHAR(10);

DECLARE var_Dttm_Upd   TIMESTAMP;

DECLARE num_rec    INTEGER;

SELECT COUNT(*) INTO num_rec
 FROM msttst.MAPA_INT_ORG_HST
 WHERE id_int_org_pty = Internal_Org_Party_Id;

IF num_rec > 1 THEN
 LEAVE EXIT_PROC;
END IF;

SET var_Key1 = 'ADMIN';
SET var_Key2 = 'MST_DUMMY';

--Get DUMMY CODE
SET var_DmyCdPref = NULL;
SELECT txt1 INTO var_DmyCdPref
 FROM msttst.MACM_GNL_MST
 WHERE key1 = var_Key1 AND
  key2 = var_Key2 AND
  flg_del = '0';

IF var_DmyCdPref IS NULL THEN
 SET var_DmyCdPref = '#';
END IF;

--CREATE DUMMY CODE
SET var_DmyCd = var_DmyCdPref || Internal_Org_Party_Id;

SET chr_Id_Int_Org_Pty  = Internal_Org_Party_Id;
SET var_Id_Int_Dpt_Pty  = var_DmyCd;
SET dat_dt_pty_strt  = '1900-01-01';
SET dat_dt_pty_end  = '9999-12-31';
SET var_Nm_Int_Dpt_Eng  = NULL;
SET var_Nm_Int_Dpt_Int  = NULL;
SET var_Nm_Int_Dpt_Abr  = NULL;
SET var_Dsc_Int_Dpt  = NULL;
SET chr_Cd_Int_Dpt_Tp  = NULL;
SET var_Id_Par_Int_Dpt_Pty = NULL;
SET var_Flg_Dmy   = '1';
SET var_User_Crt  = CREATED_BY;
SET var_Dttm_Crt  = CREATION_DATE;
SET var_User_Upd  = UPDATED_BY;
SET var_Dttm_Upd  = UPDATED_DATE;

INSERT INTO msttst.MAIO_INT_DPT_HST
VALUES(chr_Id_Int_Org_Pty,var_Id_Int_Dpt_Pty,
         dat_dt_pty_strt,dat_dt_pty_end,
                var_Nm_Int_Dpt_Eng,var_Nm_Int_Dpt_Int,
                       var_Nm_Int_Dpt_Abr,var_Dsc_Int_Dpt,
  chr_Cd_Int_Dpt_Tp,var_Id_Par_Int_Dpt_Pty,var_Flg_Dmy,
  var_User_Crt,var_Dttm_Crt,var_User_Upd,var_Dttm_Upd);
END;

Tweet