/**********************************************************************************************
-- Title : [2k5] insert/update/delete에서 OUTPUT절의 활용
-- Reference : mcpworld.com
-- Key word : insert update delete output into from deleted inserted
**********************************************************************************************/
use tempdb;
go
-->> TEST1
drop table ttt, log;
create table ttt (id int, nm nvarchar(10))
create table log_tbl( id int not null identity, ins_id int, ins_nm nvarchar(10), del_id int, del_nm nvarchar(10));
insert into ttt values (1,'유재석');
insert into ttt values (2,'박명수');
insert into ttt values (3,'노홍철');
select * from ttt;
update ttt
set id = 4
, nm = '2인자'
output inserted.id, inserted.nm, deleted.id, deleted.nm
into log_tbl(ins_id, ins_nm, del_id, del_nm)
where id = 2;
select * from ttt;
select * from log_tbl;
insert into ttt
output inserted.id, inserted.nm, null, null
into log_tbl(ins_id, ins_nm, del_id, del_nm)
values (44, '하하');
select * from ttt;
select * from log_tbl;
delete from ttt
output null, null, deleted.id, deleted.nm
into log_tbl(ins_id, ins_nm, del_id, del_nm)
where id = 44;
select * from ttt;
select * from log_tbl;
-->> TEST2
create table output1 (c1 int identity(1, 1), c2 int default (0));
go
insert output1 default values;
go 10
select * from output1;
go
-- delete된 행 확인
delete output1
output deleted.*
where c1 >= 9;
select count(*) as [rows in table] from output1;
go
-- delete된 행을 테이블에 저장
create table changelog (
c1 int,
c2_old int,
c2_new int,
opdescr varchar(10),
opdate smalldatetime default getdate(),
opuser varchar(100) default suser_sname());
go
delete output1
output deleted.c1, 'delete'
into changelog (c1, opdescr)
where c1 = 5;
go
select * from changelog;
go
update output1
set c2 = c2 + 100
output inserted.c1, deleted.c2, inserted.c2, 'insert'
into changelog (c1, c2_old, c2_new, opdescr)
where c1 = 1;
go
select * from changelog;
go
-- 클린업
drop table output1, changelog;
go