개발 참고자료

ALTER TRIGGER TRG_M_Assets_INUP ON M_500
FOR UPDATE, INSERT
AS
SET NOCOUNT ON

DECLARE @Good varchar(6)
DECLARE @iDate varchar(8)
DECLARE @iPrice numeric(16, 4)
DECLARE @iSeq varchar(20)
DECLARE @Qty numeric(16, 0)
DECLARE @dPrice numeric(16, 4)
DECLARE @iDiv varchar(20)
DECLARE @iDiv_dec varchar(200)
DECLARE @dCnt inta
DECLARE @FDdate varchar(8)
DECLARE @EDdate varchar(8)
DECLARE @dSeq int
DECLARE @Drate numeric(16, 4)
DECLARE @Unit_DPrice numeric(20, 10)
DECLARE @Unit_Price numeric(20, 10)
DECLARE @Curr_Qty numeric(16, 0)
DECLARE @Curr_Price numeric(16, 4)
DECLARE @oQty numeric(16, 0)
DECLARE @iQty numeric(16, 0)
DECLARE @depre_yn varchar(1)
DECLARE @UPD_dt varchar(25)
DECLARE @UPD_emp varchar(20)
DECLARE @INP_dt varchar(25)
DECLARE @INP_emp varchar(20)
DECLARE @trans_desc varchar(500)

DECLARE @SelectCnt int

DECLARE trigger_cur CURSOR
FOR SELECT q_Good, q_iDate, q_iPrice, q_iSeq, q_Qty
, q_dPrice, q_iDiv, q_iDiv_dec, q_dCnt, q_FDdate
, q_EDdate, q_dSeq, q_Drate, q_Unit_DPrice, q_Unit_Price
, q_Curr_Qty, q_Curr_Price, q_oQty, q_iQty, q_depre_yn
, UPD_dt, UPD_emp, INP_dt, INP_emp, trans_desc FROM INSERTED

OPEN trigger_cur
FETCH NEXT FROM trigger_cur
INTO @Good, @iDate, @iPrice, @iSeq, @Qty,
@dPrice, @iDiv, @iDiv_dec, @dCnt, @FDdate,
@EDdate, @dSeq, @Drate, @Unit_DPrice, @Unit_Price,
@Curr_Qty, @Curr_Price, @oQty, @iQty, @depre_yn,
@UPD_dt, @UPD_emp, @INP_dt, @INP_emp, @trans_desc

WHILE(@@FETCH_STATUS = 0)

BEGIN

SELECT @SelectCnt = count(*)
FROM M_Assets
WHERE q_Good = @Good
AND q_iDate = @iDate
AND q_iPrice = @iPrice
AND q_Qty = @Qty

if @SelectCnt >= 1
begin
UPDATE set
M_Assets ( q_Good, q_iDate, q_iPrice, q_Qty, q_dPrice
, q_iDiv, q_iDiv_dec, q_dCnt, q_FDdate, q_EDdate
, q_dSeq, q_Drate, q_Unit_DPrice, q_Unit_Price, q_Curr_Qty
, q_Curr_Price, q_oQty, q_iQty, q_depre_yn
, UPD_dt, UPD_emp, INP_dt, INP_emp, trans_desc)
values ( @Good, @iDate, @iPrice, @Qty, @dPrice,
@iDiv, @iDiv_dec, @dCnt, @FDdate, @EDdate,
@dSeq, @Drate, @Unit_DPrice, @Unit_Price, @Curr_Qty,
@Curr_Price, @oQty, @iQty, @depre_yn,
@UPD_dt, @UPD_emp, @INP_dt, @INP_emp, @trans_desc)
end

else
begin
insert into
M_Assets ( q_Good, q_iDate, q_iPrice, q_Qty, q_dPrice
, q_iDiv, q_iDiv_dec, q_dCnt, q_FDdate, q_EDdate
, q_dSeq, q_Drate, q_Unit_DPrice, q_Unit_Price, q_Curr_Qty
, q_Curr_Price, q_oQty, q_iQty, q_depre_yn
, UPD_dt, UPD_emp, INP_dt, INP_emp, trans_desc)
values ( @Good, @iDate, @iPrice, @Qty, @dPrice,
@iDiv, @iDiv_dec, @dCnt, @FDdate, @EDdate,
@dSeq, @Drate, @Unit_DPrice, @Unit_Price, @Curr_Qty,
@Curr_Price, @oQty, @iQty, @depre_yn,
@UPD_dt, @UPD_emp, @INP_dt, @INP_emp, @trans_desc)
end

FETCH NEXT FROM trigger_cur
INTO @Good, @iDate, @iPrice, @iSeq, @Qty,
@dPrice, @iDiv, @iDiv_dec, @dCnt, @FDdate,
@EDdate, @dSeq, @Drate, @Unit_DPrice, @Unit_Price,
@Curr_Qty, @Curr_Price, @oQty, @iQty, @depre_yn,
@UPD_dt, @UPD_emp, @INP_dt, @INP_emp, @trans_desc
END

CLOSE trigger_cur
DEALLOCATE trigger_cur
SET NOCOUNT OFF