SQList ver. 5 offered a feature named "Keep delete items" which disabled the deletion of rows in a SQL table when the corresponding item in the SharePoint list was deleted. This was useful if you want to regularly trim large SharePoint lists but still keep all data in the SQL table.
This feature was deprecated in SQList version 6, but there is a workaround that can be used to obtain the same result.
Yon can use and "instead of delete" trigger on the SQL table to skip the deletion of the row. This approach give you much more control on what to do when an item is deleted in SharePoint (e.g. store a copy somewhere, log a message, etc.).
Warning: it's important to understand that if an item is added and then removed from a SharePoint list while SQList is not synchronizing (e.g. due to connection failure or service stoppage), the item won't be archived as it never got to the SQL table in the first place. Please ensure that you keep this in mind while designing your system.
See below a simple example of how to create a simple trigger that skip deletion:
create table Table1 (Num int);
insert into Table1 (Num) values (1);
insert into Table1 (Num) values (2);
insert into Table1 (Num) values (3);
insert into Table1 (Num) values (4);
insert into Table1 (Num) values (5);
select * from Table1;
create trigger dbo.keep_deleted_Table1
on dbo.Table1
instead of delete
as
begin
set nocount on;
set nocount off;
end
delete from Table1 where Num = 3;
select * from Table1; -- Num = 3 is still in the table
You can go a step further and add two columns that indicate that a row is deleted, that would allow you to identify/filter rows that have been deleted:
Add two columns to the SQL table:
alter table Table1
add
[_isDeleted] bit null,
[_deletedOnUTC] datetime null;
Configure SQList's Global Settings not to drop these columns:
Change the trigger as follow:
begin
set nocount on;
update
Table1
set
[_isDeleted] = 1,
[_deletedOnUTC] = getutcdate()
where
[ID] in
(
select [ID] from deleted
)
set nocount off;
end
Important: for tables created by SQList, ensure that you create the trigger for the main table as well as the additional table for many-to-many column and attachments.
If you need assistance or have any questions about this article, please contact us at
support@axioworks.com