Our use of cookies

We use cookies to tailor your experience, gather analytics, and provide you with live assitance. By clicking "Accept" or continuing to browse our site you agree to the use of cookies. For more details please read our Cookie Policy.

Keep items deleted in a SharePoint list in the corresponding SQL table

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
set nocount on;

set nocount off;

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
[_isDeleted] bit null,
[_deletedOnUTC] datetime null;

Configure SQList's Global Settings not to drop these columns:

Image Placeholder

Change the trigger as follow:

set nocount on;
[_isDeleted] = 1,
[_deletedOnUTC] = getutcdate()
[ID] in
select [ID] from deleted
set nocount off;

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