실행한 명령 되돌리기

WorkaHolic

실행한 명령 되돌리기

쫌 2010. 4. 8. 22:35

--저자: Gleb Oufimtsev

/*************************************************************************/
/*         Library SQL-UNDO version 2.1                                  */
/*         Copyright (C) 2000 Oufimtsev Gleb                             */
/*                                                                       */
/*            gvu@newmail.ru, http://www.gvu.newmail.ru                 */
/*            +7 (095) 178-40-92, Moscow, Russia                         */
/*************************************************************************/

/********************** Installation script ******************************/

CREATE TABLE HistoryLog (
Id int NOT NULL PRIMARY KEY CLUSTERED ,
IdOperation int NOT NULL ,
ValueId int NOT NULL ,
HLDate datetime NOT NULL ,
Login sysname NOT NULL ,
IdTableName int NOT NULL ,
BatchMarker varchar (60) NULL
)
GO

CREATE TABLE HistoryLogColumns (
Id int NOT NULL PRIMARY KEY CLUSTERED ,
ColumnName sysname NOT NULL UNIQUE
)
GO

CREATE TABLE HistoryLogExcludedTables (
Id int IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED ,
TableName sysname NOT NULL UNIQUE
)
GO

CREATE TABLE HistoryLogTables (
Id int NOT NULL PRIMARY KEY CLUSTERED ,
TableName sysname NOT NULL UNIQUE
)
GO

CREATE TABLE HistoryLogValue (
Id int NOT NULL PRIMARY KEY CLUSTERED ,
IdHistoryLog int NOT NULL ,
OldValue varchar (4000) NULL ,
NewValue varchar (4000) NULL ,
IdColumn int NOT NULL
)
GO

CREATE INDEX idx_HistoryLog_1 ON HistoryLog(HLDate, Login, IdTableName)
CREATE INDEX idx_HistoryLog_2 ON HistoryLog(Login, IdTableName)
CREATE INDEX idx_HistoryLog_3 ON HistoryLog(ValueId, IdTableName)
CREATE INDEX idx_HistoryLog_4 ON HistoryLog(IdTableName, Login)
CREATE INDEX idx_HistoryLog_5 ON HistoryLog(BatchMarker)
GO

CREATE INDEX idx_HistoryLogValue_1 ON HistoryLogValue(IdHistoryLog, IdColumn)
CREATE INDEX idx_HistoryLogValue_2 ON HistoryLogValue(IdColumn)
GO

ALTER TABLE HistoryLog ADD
CONSTRAINT FK_HistoryLog_HistoryLogTables
FOREIGN KEY (IdTableName) REFERENCES HistoryLogTables (Id)
GO

ALTER TABLE HistoryLogValue ADD
CONSTRAINT FK_HistoryLogValue_HistoryLog
FOREIGN KEY (IdHistoryLog) REFERENCES HistoryLog (Id),
CONSTRAINT FK_HistoryLogValue_HistoryLogColumns
FOREIGN KEY (IdColumn) REFERENCES HistoryLogColumns (Id)
GO

GRANT SELECT, INSERT ON HistoryLogValue TO public
GO

-- Do not use this procedure directly.
-- This is internal service for use by other procedures

set nocount on

declare @str nvarchar(4000)
declare @trname sysname
declare @CR nchar(1)
declare @cnt int

select @CR=NCHAR(10), @cnt=0

select @trname='tr_'+@TableName+'_HistoryLog_'+convert(varchar(8),GETDATE(),112)

select @str='/*************************************************************************/'+@CR
           +'/*         Library SQL-UNDO version 2.1                                  */'+@CR
           +'/*         Copyright (C) 2000 Oufimtsev Gleb                             */'+@CR
           +'/*                                                                       */'+@CR
           +'/*            gvu@newmail.ru, http://www.gvu.newmail.ru                 */'+@CR
           +'/*            +7 (095) 178-40-92, Moscow, Russia                         */'+@CR
           +'/*************************************************************************/'+@CR
           +'CREATE TRIGGER '+@trname+' ON '+@TableName+' '+@CR
           +'FOR INSERT, UPDATE, DELETE '+@CR
           +'AS '+@CR
           +'/*                                                         */'+@CR
           +'/*     Trigger of registration of changes                  */'+@CR
           +'/*     Generated by procedure spHLRecreateTriggers         */'+@CR
           +'/*                                                         */'+@CR+@CR
           +'set nocount on '+@CR+@CR
           +'select * into #HL02dt from deleted '+@CR
           +'select * into #HL02it from inserted '+@CR
           +'exec spHLSaveOperation @@PROCID '+@CR

exec(@str)

select @cnt=@cnt+1, @str='Trigger '+@trname+' on table '+@TableName+' was added'

print @str

return @cnt

-- Do not use this procedure directly.
-- This is internal service for use by other procedures

set nocount on

declare @str nvarchar(4000)
declare @trname sysname
declare @cnt int

select @cnt=0

while exists (select * from sysobjects where type='TR' and name like 'tr_'+@TableName+'_HistoryLog_%')
begin
select @trname=name from sysobjects where type='TR' and name like 'tr_'+@TableName+'_HistoryLog_%'
select @str='drop trigger '+@trname
exec(@str)
select @cnt=@cnt+1
select @str='Trigger '+@trname+' on table '+@TableName+' was dropped'
print @str
end

return @cnt

-- Creation and recreation of triggers of record of log

-- If @TableName<>NULL, the trigger will be recreated only
-- in the table @TableName.

-- If @TableName=NULL, the trigger will be recreated on all
-- tables of a database, except for listed in HistoryLogExcludedTables.

set nocount on
set transaction isolation level SERIALIZABLE
set xact_abort on

begin tran

declare @msg varchar(255), @cnt1 int, @cnt2 int

print '         Library SQL-UNDO version 2.0'
print '         Copyright (C) 2000 Oufimtsev Gleb, Moscow, Russia'
print ''
print '             gvu@newmail.ru, http://www.gvu.newmail.ru, +7 (095) 178-40-92'
print ''

if @TableName is NOT NULL
begin
exec spHLDropHistoryTrigger @TableName
exec spHLCreateHistoryTrigger @TableName
end
else
begin

select @cnt1=0

    DECLARE TBL1 CURSOR LOCAL FAST_FORWARD FOR
      select name from sysobjects where type='U'
    OPEN TBL1
    FETCH TBL1 INTO @TableName
    WHILE @@FETCH_STATUS=0
    BEGIN
      exec @cnt2=spHLDropHistoryTrigger @TableName
      select @cnt1=@cnt1+@cnt2
      FETCH TBL1 INTO @TableName
    END
    CLOSE TBL1
    DEALLOCATE TBL1

    select @msg='Dropped: '+rtrim(convert(varchar(10),@cnt1))+' triggers'
    print '______________________'
    print @msg
    print ''

select @cnt1=0

    DECLARE TBL2 CURSOR LOCAL FAST_FORWARD FOR
      select name from sysobjects where type='U'
         and name not in (select TableName from HistoryLogExcludedTables)
         and name not like 'HistoryLog%'
         and name<>'dtproperties'
    OPEN TBL2
    FETCH TBL2 INTO @TableName
    WHILE @@FETCH_STATUS=0
    BEGIN
      exec @cnt2=spHLCreateHistoryTrigger @TableName
      select @cnt1=@cnt1+@cnt2
      FETCH TBL2 INTO @TableName
    END
    CLOSE TBL2
    DEALLOCATE TBL2

    select @msg='Created: '+rtrim(convert(varchar(10),@cnt1))+' triggers'
    print '______________________'
    print @msg
    print ''

end -- else

commit

-- Do not use this procedure directly.
-- This is internal service for use by other procedures

declare @TypeOperation int
declare @TableName sysname
declare @ValueId int
declare @IdTable int
declare @strValueId varchar(10)
declare @sql_header varchar(8000)
declare @sql_footer varchar(8000)
declare @Date datetime
declare @ColName sysname
declare @OldValue varchar(8000)
declare @ColType sysname
declare @ColNames varchar(8000)
declare @ColValues varchar(8000)
declare @CR char(1)

declare @errmsg varchar(255)

set nocount on

select @CR=CHAR(10)

select @TableName=t.TableName,
       @TypeOperation=h.IdOperation,
       @ValueId=h.ValueId,
       @strValueId=Rtrim(convert(varchar(10),h.ValueId)),
       @Date=h.HLDate,
       @IdTable=h.IdTableName
from HistoryLogTables t (nolock), HistoryLog h (nolock)
where h.IdTableName=t.Id
and h.Id=@IdHistory

if @TypeOperation=1 -- 岫?insert, 午成嶢, 哀飮孼岫潗 delete
begin
exec('delete from '+@TableName+' with (holdlock) where Id='+@strValueId)
if @@ERROR<>0 or @@ROWCOUNT<>1
begin
    select @errmsg='Undo-runtime error'+@CR+'<<delete from '+@TableName+' with (holdlock) where Id='+@strValueId+'>>'
    raiserror(@errmsg,16,-1)
    return -100
end
end
if @TypeOperation=3 -- 岫?delete, 午成嶢, 哀飮孼岫潗 insert
begin

select @sql_footer='insert into '+@TableName+' with (holdlock) (Id'

if (select 0x80&col.status from syscolumns col (nolock)
        where name='Id' and id=OBJECT_ID(@TableName))=0x80
     select @sql_header='set IDENTITY_INSERT '+@TableName+' ON '+@CR
   else
     select @sql_header=''

declare COLSVALS cursor local fast_forward for
    select c.ColumnName, v.OldValue, t.name
    from HistoryLogColumns c (nolock), HistoryLogValue v (nolock),
         syscolumns col (nolock), systypes t (nolock)
    where v.IdColumn=c.Id
    and c.ColumnName=col.name
    and col.xtype=t.xusertype
    and col.id=OBJECT_ID(@TableName)
    and v.IdHistoryLog=@IdHistory
    and v.OldValue is not NULL
open COLSVALS
fetch COLSVALS into @ColName, @OldValue, @ColType
while @@FETCH_STATUS=0
begin

    select @ColNames=IsNull(@ColNames,'')+','+@ColName
    if @ColType in ('datetime','smalldatetime','char','varchar','nchar','nvarchar')
        select @OldValue=CHAR(39)+@OldValue+CHAR(39)
    select @ColValues=IsNull(@ColValues,'')+','+@OldValue
    fetch COLSVALS into @ColName, @OldValue, @ColType
end
close COLSVALS
deallocate COLSVALS

exec(@sql_header+@sql_footer+@ColNames+') values ('+@strValueId+@ColValues+')')
if @@ERROR<>0 or @@ROWCOUNT<>1
begin
    select @errmsg='Undo-runtime error'+@CR+'<<'+@sql_footer+@ColNames+') values ('+@strValueId+@ColValues+')'+'>>'
    raiserror(@errmsg,16,-1)
    return -100
end
end

if @TypeOperation=2 -- 岫?update, 午成嶢, 哀飮孼岫潗 update
begin
select @sql_header='update '+@TableName+' with (holdlock) set '
select @sql_footer=' where Id='+@strValueId

declare COLSVALS2 cursor local fast_forward for
    select c.ColumnName, v.OldValue, t.name
    from HistoryLogColumns c (nolock), HistoryLogValue v (nolock),
         syscolumns col (nolock), systypes t (nolock)
    where v.IdColumn=c.Id
    and c.ColumnName=col.name
    and col.xtype=t.xusertype
    and col.id=OBJECT_ID(@TableName)
    and v.IdHistoryLog=@IdHistory
open COLSVALS2
fetch COLSVALS2 into @ColName, @OldValue, @ColType
while @@FETCH_STATUS=0
begin
    if @OldValue is NULL select @OldValue='NULL'
    else if @ColType in ('datetime','smalldatetime','char','varchar','nchar','nvarchar')
        select @OldValue=CHAR(39)+@OldValue+CHAR(39)
    exec(@sql_header+@ColName+'='+@OldValue+@sql_footer)
    if @@ERROR<>0 or @@ROWCOUNT<>1
    begin
      select @errmsg='Undo-runtime error'+@CR+'<<'+@sql_header+@ColName+'='+@OldValue+@sql_footer+'>>'
      raiserror(@errmsg,16,-1)
      close COLSVALS2
      deallocate COLSVALS2
      return -100
    end
    fetch COLSVALS2 into @ColName, @OldValue, @ColType
end
close COLSVALS2
deallocate COLSVALS2
end

delete from v from HistoryLog h, HistoryLogValue v
where v.IdHistoryLog=h.Id
and h.ValueId=@ValueId
and (h.Login=SUSER_SNAME() or IS_MEMBER('db_owner')=1)
and h.HLDate>=@Date
and h.IdTableName=@IdTable

delete from HistoryLog
where ValueId=@ValueId
and (Login=SUSER_SNAME() or IS_MEMBER('db_owner')=1)
and HLDate>=@Date
and IdTableName=@IdTable

-- Do not use this procedure directly.
-- This is internal service for use by other procedures

   @TypeOperation int,
   @ColName sysname,
   @ValueId int,
   @IdHistory int

declare @CR char(1)
declare @IdColumn int

declare @sql_part1 varchar(8000)
declare @sql_part2 varchar(8000)
declare @sql_part3 varchar(8000)

set nocount on

select @CR=CHAR(10)

select @IdColumn=Id from HistoryLogColumns where ColumnName=@ColName
if @IdColumn is NULL
begin
begin tran
select @IdColumn=IsNull(max(Id),0)+1 from HistoryLogColumns (updlock, holdlock)
insert into HistoryLogColumns (Id, ColumnName) values (@IdColumn, @ColName)
commit
end
select @sql_part1='declare @o varchar(8000), @n varchar(8000), @IdHistoryValue int '+@CR

if @TypeOperation=1
begin
select @sql_part2= 'select @n=Rtrim(convert(varchar(8000),'+@ColName+')) '+@CR
select @sql_part2=@sql_part2+'from #HL02it where Id='+rtrim(convert(varchar(10),@ValueId))+' '+@CR
select @sql_part2=@sql_part2+'if @n is not NULL '+@CR
end

if @TypeOperation=3
begin
select @sql_part2= 'select @o=Rtrim(convert(varchar(8000),'+@ColName+')) '+@CR
select @sql_part2=@sql_part2+'from #HL02dt where Id='+rtrim(convert(varchar(10),@ValueId))+' '+@CR
select @sql_part2=@sql_part2+'if @o is not NULL '+@CR
end

if @TypeOperation=2
begin
select @sql_part2= 'select @n=Rtrim(convert(varchar(8000),'+@ColName+')) '+@CR
select @sql_part2=@sql_part2+'from #HL02it where Id='+rtrim(convert(varchar(10),@ValueId))+' '+@CR
select @sql_part2=@sql_part2+'select @o=Rtrim(convert(varchar(8000),'+@ColName+')) '+@CR
select @sql_part2=@sql_part2+'from #HL02dt where Id='+rtrim(convert(varchar(10),@ValueId))+' '+@CR
select @sql_part2=@sql_part2+'if (@n<>@o) or (@n is NULL and @o is not NULL) or (@n is not NULL and @o is NULL) '+@CR
end

select @sql_part3=           'begin '+@CR
select @sql_part3=@sql_part3+' begin tran '+@CR
select @sql_part3=@sql_part3+'    select @IdHistoryValue=IsNull(max(Id),0)+1 from HistoryLogValue (updlock, holdlock) '+@CR
select @sql_part3=@sql_part3+'    insert HistoryLogValue (Id,IdHistoryLog,NewValue,OldValue,IdColumn) '+@CR
select @sql_part3=@sql_part3+'         values (@IdHistoryValue,'+rtrim(convert(varchar(10),@IdHistory))
select @sql_part3=@sql_part3+'               ,@n,@o,'+rtrim(convert(varchar(10),@IdColumn))+') '+@CR
select @sql_part3=@sql_part3+' commit '+@CR
select @sql_part3=@sql_part3+'end '

exec(@sql_part1+@sql_part2+@sql_part3)

-- Do not use this procedure directly.
-- This is internal service for use by other procedures

    @TableName sysname,
    @TypeOperation int,
    @ValueId int,
    @BatchMarker varchar(60)=NULL

declare @IdTable int
declare @IdHistory int

set nocount on

select @IdTable=Id from HistoryLogTables where TableName=@TableName
if @IdTable is NULL
begin
begin tran
select @IdTable=IsNull(max(Id),0)+1 from HistoryLogTables (updlock, holdlock)
insert into HistoryLogTables (Id, TableName) values (@IdTable, @TableName)
commit
end

begin tran
select @IdHistory=IsNull(max(Id),0)+1 from HistoryLog (updlock, holdlock)
insert into HistoryLog(Id, IdTableName, IdOperation, ValueId, HLDate, Login, BatchMarker)
values (@IdHistory, @IdTable, @TypeOperation, @ValueId, GETDATE(), SUSER_SNAME(), @BatchMarker)
commit

return @IdHistory

-- Do not use this procedure directly.
-- This is internal service for use by triggers

declare @IdHistory int

declare @ValueId int

declare @InsCount int
declare @DelCount int

declare @TypeOperation int -- 1-insert, 2-update, 3-delete
declare @TableName sysname
declare @IdTable int
declare @ColName sysname

declare @BatchMarker varchar(60)

set nocount on

select @InsCount=0, @DelCount=0

select @DelCount=count(*) from #HL02dt
select @InsCount=count(*) from #HL02it

if @InsCount=0 and @DelCount=0 RETURN
--It is possible, that inserted and deleted have 0 records simultaneously.

if @InsCount>10 or @DelCount>10 RETURN
--Mass operation - administrative operation. To not serve..

if @InsCount>0 and @DelCount=0 select @TypeOperation=1
else if @InsCount>0 and @DelCount>0 select @TypeOperation=2
else if @InsCount=0 and @DelCount>0 select @TypeOperation=3

select @TableName=t.name, @IdTable=t.id
from sysobjects t, sysobjects tr
where tr.parent_obj=t.id
and t.type='U'
and tr.type='TR'
and tr.id=@IdTrigger

select @BatchMarker=convert(varchar(3),spid)+'|'+convert(varchar(3),suid)+'|'
+convert(varchar(23),login_time,121)+'|'+convert(varchar(23),last_batch,121)
from master..sysprocesses where spid=@@SPID

if @DelCount=1 or @InsCount=1 -- Was change only 1 row
-- Facilitated fast algorithm
begin

select @ValueId=Id from #HL02it
if @ValueId is NULL select @ValueId=Id from #HL02dt

exec @IdHistory=spHLSetHistory @TableName, @TypeOperation, @ValueId, @BatchMarker

    declare COL cursor local fast_forward for
       select c.name from syscolumns c, systypes t
       where c.xusertype=t.xusertype
       and c.id=@IdTable
       and c.name<>'Id'
       and t.name<>'timestamp'
    open COL
    fetch COL into @ColName
    while @@FETCH_STATUS=0
    begin
      exec spHLSetColumnInfo @TypeOperation, @ColName, @ValueId, @IdHistory
      fetch COL into @ColName
    end
    close COL
    deallocate COL

end

if @DelCount>1 or @InsCount>1    -- More than one row is changed
begin
    declare RWS cursor local fast_forward for
       select distinct Id from #HL02it
       union
       select distinct Id from #HL02dt
    open RWS
    fetch RWS into @ValueId
    while @@FETCH_STATUS=0
    begin

exec @IdHistory=spHLSetHistory @TableName, @TypeOperation, @ValueId, @BatchMarker

      declare MCOL cursor local fast_forward for
         select c.name from syscolumns c, systypes t
         where c.xusertype=t.xusertype
         and c.id=@IdTable
         and c.name<>'Id'
         and t.name<>'timestamp'
      open MCOL
      fetch MCOL into @ColName
      while @@FETCH_STATUS=0
      begin

exec spHLSetColumnInfo @TypeOperation, @ColName, @ValueId, @IdHistory

        fetch MCOL into @ColName
      end
      close MCOL
      deallocate MCOL

      fetch RWS into @ValueId
    end
    close RWS
    deallocate RWS

end

GRANT EXECUTE ON spHLSaveOperation TO public
GO

-- This is main working procedure for use by users

-- Undoes action of the user, if not a lot of time has passed

-- @IdHistory - specifies concrete operation in HistoryLog

declare @BatchMarker varchar(60)
declare @retval int

set nocount on
set xact_abort on

if exists (select * from HistoryLog (nolock)
where Id=@IdHistory and IS_MEMBER('db_owner')=0)
begin
raiserror('You have no the right to undo operation of other user',16,-1)
return -100
end

select @BatchMarker=BatchMarker from HistoryLog (nolock) where Id=@IdHistory

create table #histkeys (Id int not NULL Primary Key CLUSTERED,
IdTable int not NULL,
ValueId int not NULL)

if @BatchMarker is NULL
     insert into #histkeys (Id,IdTable,ValueId)
     select Id, IdTableName, ValueId from HistoryLog (nolock)
          where Id=@IdHistory
else
     insert into #histkeys (Id,IdTable,ValueId)
     select Id, IdTableName, ValueId from HistoryLog (nolock)
          where BatchMarker=@BatchMarker

if exists (select * from #histkeys (nolock)
where not exists (select * from HistoryLogValue (nolock)
where IdHistoryLog=#histkeys.Id))
begin
raiserror('There is no kept information on importance of fields',16,-1)
return -100
end

if exists (select * from #histkeys h (nolock)
           where exists (select * from HistoryLog sh (NOLOCK)
                          where sh.ValueId=h.ValueId
                          and sh.IdTableName=h.IdTable
                          and sh.Id>h.Id
                          and sh.Id not in (select h1.Id from #histkeys h1 (nolock))))
begin
raiserror('Attempt to undo operations later already changed',16,-1)
return -100
end

declare RECS cursor local fast_forward for
select Id from #histkeys (nolock) order by 1 DESC
open RECS
fetch RECS into @IdHistory

begin tran

while @@FETCH_STATUS=0
begin
    exec @retval=spHLSeparateUndo @IdHistory
    if @@ERROR<>0 or @retval<0
    begin
      raiserror('Error of a undo of batch',16,-1)
      close RECS
      deallocate RECS
      if @@TRANCOUNT>0 rollback
      return @retval
    end
    fetch RECS into @IdHistory
end

commit

close RECS
deallocate RECS

drop table #histkeys

-- This is the second basic procedure for use by users
-- Undoes last action of the current user, if not a lot of time has passed

declare @IdHistory int
declare @mdate datetime

set nocount on

select @IdHistory=max(Id) from HistoryLog where Login=SUSER_SNAME()
select @mdate=HLDate from HistoryLog where Id=@IdHistory

if DATEDIFF(mi,@mdate,GETDATE())>20
begin
raiserror('You made changes for a long time',16,-1)
return -100
end

if @IdHistory is not NULL exec spHLUndo @IdHistory

[출처] 실행한 명령 되돌리기 |작성자 진카자마

저작자표시 비영리 변경금지

현재글실행한 명령 되돌리기

Well Factory

글의 대부분은 펌으로 작업합니다.. 문제 발생 시 자삭합니다~~

Excel, 루어, c#, 낚시, Git, windows 2008 server, datetime, DYNAMIC SQL, SQL, MSSQL2005, Linux, 트위터, Oracle, Procedure, Vs, 채비, 저수지, MSSQL, Twitter, Eclipse,

Today :
Yesterday :

일	월	화	수	목	금	토
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30	31

Well Factory

실행한 명령 되돌리기

'WorkaHolic'의 다른글

티스토리툴바

실행한 명령 되돌리기

'WorkaHolic'의 다른글

관련글

티스토리툴바