dbspec이라는 테이블에서 값을 가져와서
while 문 사용해 트리거 생성 후 atrail테이블에 발생된 값을 넣는 문인데
*dbspec
1 DG scrno nvarchar(4) NULL
2 DG Initial nvarchar(3) NULL
3 SV scrno nvarchar(4) NULL
4 SV visit int NULL
5 SV vdt nvarchar(10) NULL
6 PI scrno nvarchar(4) NULL
7 PI visit int NULL
8 PI birth nvarchar(10) NULL
9 PI gender int 1,2
10 PI ht nvarchar(3) NULL
11 PI wt nvarchar(5) NULL
*atrail
create table Atrail(
Scrno nvarchar(5),
visit int,
serial int,
tbl nvarchar(10),
col nvarchar(10),
input nvarchar(10),
entrytime nvarchar(20))
declare @e nvarchar(max)
declare @declares nvarchar(max)
declare @crtbls nvarchar(100)
set @crtbls = ' create trigger trg_DG on DG after insert, update, delete as '
set @declares = 'declare @scrno nvarchar(10)
declare @visit nvarchar(10)
declare @serial nvarchar(10)
declare @tbl nvarchar(10)
declare @entrytime nvarchar(20) '
set @e = @crtbls + @declares
declare @i nvarchar(10)
set @i = 1
while(@i <(select count(*) from dbspec))
begin
declare @declare nvarchar(max)
declare @aa nvarchar(100)
declare @bb nvarchar(100)
declare @cc nvarchar(100)
declare @dd nvarchar(100)
declare @ee nvarchar(100)
declare @ff nvarchar(100)
declare @gg nvarchar(100)
declare @hh nvarchar(100)
declare @w varchar(max)
declare @a varchar(max)
declare @b varchar(max)
declare @tbl nvarchar(100)
declare @tbl_next varchar(100)
declare @col varchar(100)
declare @col_next varchar(100)
declare @typ varchar(100)
declare @typ_next varchar(100)
declare @crtbl varchar(100)
declare @crtbl_next varchar(100)
declare @space varchar(100)
declare @c varchar(max)
set @tbl = (select tbl from dbspec where num = @i)
set @tbl_next = (select tbl from dbspec where num = @i + 1)
set @col = (select col from dbspec where num = @i)
set @col_next = (select col from dbspec where num = @i+ 1)
set @crtbl = ' create trigger trg_' + @tbl + ' on '+ +@tbl + ' after insert, update, delete as '
set @crtbl_next = ' create trigger trg_' + @tbl_next + ' on '+ +@tbl_next + ' after insert, update, delete as '
set @declare = 'declare @scrno nvarchar(10)
declare @visit nvarchar(10)
declare @serial nvarchar(10)
declare @tbl nvarchar(10)
declare @entrytime nvarchar(20) '
If(@tbl = @tbl_next)
begin
if(@col = 'visit')
begin
set @aa = 'set @visit = (select visit from inserted)'
set @bb = ' declare @input'+@i+' nvarchar(10) '+' set @input'+@i+ ' = '+ @aa
set @gg = ' declare @col'+@i+' nvarchar(10) '+' set @col'+@i+' = (select '+ @col + ')'
set @e = @e +@aa+ @bb +@gg
end
else if(@col = 'serial')
begin
set @aa= 'set @serial = (select serial from inserted)'
set @bb = ' declare @input'+@i+' nvarchar(10) '+' set @input'+@i+ ' = '+ @aa
set @gg = ' declare @col'+@i+' nvarchar(10) '+' set @col'+@i+' = (select '+ @col + ')'
set @e = @e +@aa+ @bb +@gg
end
else if(@col = 'scrno')
begin
set @aa = ' set @scrno = (select scrno from inserted)'
set @bb = ' declare @input'+@i+' nvarchar(10) '+' set @input'+@i+ ' = '+ @aa
set @gg = ' declare @col'+@i+' nvarchar(10) '+' set @col'+@i+' = (select '+ @col + ')'
set @e = @e +@aa+ @bb +@gg
end
else
begin
set @aa = ' declare @input'+@i+' nvarchar(10) '+' set @input'+@i+ ' = (select '+@col +' from inserted)'
set @gg = ' declare @col'+@i+' nvarchar(10) '+' set @col'+@i+' = (select '+ @col + ')'
set @e = @e + @aa +@gg
end
set @ff = ' set @tbl = ''' + @tbl + ''''
set @a = ' insert into atrail values(@scrno, @visit, @serial, @tbl, @col'+@i+', @input'+@i+', convert(nvarchar(20), getdate(),120))'
set @e = @e + @ff+ @a
set @i = @i +1
end
else
begin
set @cc = ' declare @input'+@i+' nvarchar(10) '+' set @input'+@i+ ' = (select '+ @col+ ' from inserted)'
set @gg = ' declare @col'+@i+' nvarchar(10) '+' set @col'+@i+' = (select '+ @col + ')'
set @e = @e + @cc + @gg
set @a = ' insert into atrail values(@scrno, @visit, @serial, @tbl, @col'+@i+', @input'+@i+', convert(nvarchar(20), getdate(),120)) go'
set @e = @e + @a
set @b = @crtbl_next + @declare
set @e = @e +@b
set @i = @i + 1
end
end
set @cc = ' declare @input'+@i+' nvarchar(10) '+' set @input'+@i+ ' = (select '+ @col_next+ ' from inserted)'
set @ff = ' set @tbl = (select ' + @tbl + ')'
set @gg = ' declare @col'+@i+' nvarchar(10) '+' set @col'+@i+' = (select '+ @col_next + ')'
set @e = @e + @cc + @dd + @ee + @ff + @gg
set @a = ' insert into atrail values(@scrno, @visit, @serial, @tbl, @col'+@i+', @input'+@i+', convert(nvarchar(20), getdate(),120)) go'
set @e = @e + @a
select(@e)
|