Create TRIGGER updateTB_ITMC_Status
on TB_ITMC_Status
AFTER UPDATE
as
Declare
@CNT int,
@Num varchar(30),
@wid varchar(30),
@Sgubun varchar(30),
@Sector varchar(30),
@Dept varchar(30),
@Username varchar(30),
@PC_SN varchar(30),
@MN_SN varchar(30),
@IP_Class varchar(30),
@IP_Tail varchar(30),
@OS varchar(30),
@InputDay varchar(30),
@InputNM varchar(30),
@Description varchar(30),
@MS_SN varchar(30)
select @wid = wid from inserted
select @Sgubun = Sgubun from inserted
select @Sector = Sector from inserted
select @Dept = Dept from inserted
select @Username = Username from inserted
select @PC_SN = PC_SN from inserted
select @MN_SN = MN_SN from inserted
select @IP_Class = IP_Class from inserted
select @IP_Tail = IP_Tail from inserted
select @OS = OS from inserted
select @InputDay = InputDay from inserted
select @InputNM = InputNM from inserted
select @Description = Description from inserted
select @MS_SN = MS_SN from inserted
BEGIN
-- PC
if @PC_SN <> ''
BEGIN
SELECT COUNT(*) AS COUNT
INTO CNT
FROM TB_ITMC_HIS
WHERE WID = @wid+'P'
END
IF @CNT > 0
BEGIN
UPDATE TB_ITMC_HIS SET sgubun = sgubun ,
workday = convert(varchar(10), getdate(), 120) ,
devsn = devsn ,
sector = sector ,
dept = dept ,
username = username ,
act = '이전설치' ,
description = description ,
inputday = convert(varchar(10), getdate(), 120) ,
inputnm = inputnm ,
status = '수정'
WHERE WID = @wid+'P'
END
ELSE
BEGIN
INSERT INTO TB_ITMC_his(wid ,sgubun ,workday ,devsn ,
sector ,dept ,username ,act ,
description ,inputday ,inputnm ,status)
VALUES(@wid+'P' ,@sgubun ,convert(varchar(10), getdate(), 120) ,@PC_SN ,
@Sector ,@dept ,@username ,'이전설치' ,
@Description ,convert(varchar(10), getdate(), 120) ,@inputnm, '수정')
END
-- 모니터
if @MN_SN <> ''
BEGIN
SELECT COUNT(*) AS COUNT
INTO CNT
FROM TB_ITMC_HIS
WHERE WID = @wid+'M'
END
IF @CNT > 0
BEGIN
UPDATE TB_ITMC_HIS SET sgubun = sgubun ,
workday = convert(varchar(10), getdate(), 120) ,
devsn = devsn ,
sector = sector ,
dept = dept ,
username = username ,
act = '이전설치' ,
description = description ,
inputday = convert(varchar(10), getdate(), 120) ,
inputnm = inputnm ,
status = '수정'
WHERE WID = @wid+'M'
END
ELSE
BEGIN
INSERT INTO TB_ITMC_his(wid ,sgubun ,workday ,devsn ,
sector ,dept ,username ,act ,
description ,inputday ,inputnm ,status)
VALUES(@wid+'M' ,@sgubun ,convert(varchar(10), getdate(), 120) ,@PC_SN ,
@Sector ,@dept ,@username ,'이전설치' ,
@Description ,convert(varchar(10), getdate(), 120) ,@inputnm, '수정')
END
END
go
처음 만들어 보는건데
TB_ITMC_Status 해당 테이블에 data가 수정되었을시에
TB_ITMC_his 테이블에 기존 data가 있으믄 update 없으면 insert를 할려는데요
프로그램에서 실행을 해도 이상은 없는데 data가 안들어오네요 ㅜㅜ
뭐가 틀린게 있나요?
|