When you delete a row in a SQL table, you may want to keep a copy of that row into an archive table. That is easily achievable by creating a copy of the main table and a trigger that "on delete" stores the deleted row into this new table.
This solution can also be used to simulate the feature "Keep deleted items" that was available in SQList version 5, but removed in version 6.
To save you time, we have created a T-SQL script that you can use to configure this archive feature on any table in your database. See the full script at the end of this article.
Its use is straightforward:
- Copy and paste the script in a new query in SQL Server Management Studio;
- Enter the schema and name of the table you want to enable archiving to into the two variables @schemaName and @tableName (replace the sample names "dbo" and "Portal_Categories");
- Execute the script.
The output of a successful execution looks like this:
Started... Fetching column names...
Archive table created: [dbo].[Portal_Categories_Archive]
Trigger created: [trigger_Portal_Categories_ArchiveDeletedRows]
All rows view created: [dbo].[view_Portal_Categories_AllRows]
All done.
Important: if the structure of the main table changes (e.g. a column is removed or its type changed, or a new column is added), you will need to manually update the trigger and the the view.
Disclaimer: this script is provided 'as-is'. While we (AxioWorks Ltd) may offer support with implementing it, we cannot be held responsible for any problems encountered or losses incurred through the use of this script.
Full T-SQL script (also available for download here):
-- enter your schema and table names here
declare @schemaName as nvarchar(500) = 'dbo';
declare @tableName as nvarchar(500) = 'Portal_Categories';
declare @archivedOnColName as nvarchar(500) = '[__ArchivedOnUTC]';
print 'Started...'
set nocount on;
declare @fullTableName as nvarchar(500) = '[' + @schemaName + '].[' + @tableName + ']';
declare @fullArchiveName as nvarchar(500) = '[' + @schemaName + '].[' + @tableName + '_Archive]';
declare @fullTriggerName as nvarchar(500) = '[trigger_' + @tableName + '_ArchiveDeletedRows]';
declare @fullViewName as nvarchar(500) = '[' + @schemaName + '].[view_' + @tableName + '_AllRows]';
print 'Fetching column names...';
declare @colNames as nvarchar(max) = '';
declare @name as nvarchar(100) = '';
declare cur cursor fast_forward for
select
[name]
from
sys.columns
where
object_id = object_id(@fullTableName)
and
[name] not like 'SQList%' -- exclude SQList system columns
order by
[name];
open cur;
fetch next from cur into @name;
while @@fetch_status = 0
begin
set @colNames = @colNames + char(9) + '[' + @name + '],' + char(13);
fetch next from cur into @name;
end
close cur;
deallocate cur;
declare @sql nvarchar(max) = '';
set @sql =
'select top 0' + char(13) +
@colNames +
' ' + @archivedOnColName + ' = getutcdate()' + char(13) +
char(13) +
' into ' + @fullArchiveName + char(13) +
char(13) +
' from ' + @fullTableName + ';' + char(13);
exec(@sql);
print 'Archive table created: ' + @fullArchiveName;
set @sql =
'create trigger ' + @fullTriggerName + char(13) +
' on ' + @fullTableName + char(13) +
' for delete' + char(13) +
' as' + char(13) +
char(13) +
'insert into ' + @fullArchiveName + char(13) +
' (' + char(13) +
@colNames +
' ' + @archivedOnColName + char(13) +
' )' + char(13) +
char(13) +
'select' + char(13) +
@colNames +
' getutcdate()' + char(13) +
char(13) +
' from [deleted];' + char(13);
exec(@sql);
print 'Trigger created: ' + @fullTriggerName;
set @sql =
'create view ' + @fullViewName + char(13) +
' as' + char(13) +
char(13) +
'select '+ char(13) +
@colNames +
' ' + @archivedOnColName + ' = cast(null as datetime)' + char(13) +
char(13) +
' from ' + @fullTableName + char(13) +
char(13) +
'union' + char(13) +
char(13) +
'select '+ char(13) +
@colNames +
' ' + @archivedOnColName + char(13) +
char(13) +
' from ' + @fullArchiveName + ' ;' + char(13);
exec(@sql);
print 'All rows view created: ' + @fullViewName;
set nocount off;
print 'All done.'