• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 287
  • Last Modified:

Having a table that is referenced in several tables, how can I execute a script that attempts to delete every record in that table without terminating on the first exception?

I have a table that is used in several other tables as a foreign key. If the table is referenced in only one specific table, I want the delete to be allowed and to cascade on delete. However, if there exists references in other tables, the delete should fail.

I want to test this with my data set by attempting to delete every record. No records should be deleted except for the last one. However, when I attempt to delete every record, it errors (as expected) and terminates the rest of the statement (doesn't proceed past the error).

How can I write a script that attempts to delete every record in a table and not terminate the statement on the first error?

Regards,

farminsure
0
farminsure
Asked:
farminsure
  • 6
  • 3
  • 2
2 Solutions
 
Scott PletcherSenior DBACommented:
That's tricky.  If they are actual SQL Foreign Key references, SQL validates all that *before* you ever get control back, even in an AFTER trigger.

I think your only chance would be an INSTEAD OF DELETE trigger.  Hopefully SQL will allow that to get control before checking FK restrictions so you could do whatever checks you needed and only re-issue the DELETE on the row(s) you actually wanted to be deleted.
0
 
judgekingCommented:
Are you looking for an actual SQL Trigger?  If so, what about a Stored Procedure instead?  Or are you just looking for a VBScript or Javascript file?
0
 
judgekingCommented:
Any update?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
farminsureAuthor Commented:
This is the script I've settled on; however, it took 15.5 hours to iterate over 277,000 records. Any improvement would be welcome.

 
DECLARE @DeletedID Int

DECLARE @DeletedItems Table
(
	DeletedId int
);

DECLARE ItemsToDelete SCROLL CurSor For
	SELECT ID
	FROM TABLE

Open ItemsToDelete

SELECT COUNT(*)
  FROM TABLE

FETCH NEXT FROM ItemsToDelete INTO @DeletedID
	While @@FETCH_STATUS = 0
BEGIN
	BEGIN TRY
		--ATTEMPT TO DELETE
		DELETE FROM TABLE WHERE ID = @DeletedID;
		Insert into @DeletedItems values (@DeletedID)
	END TRY	
	
	BEGIN CATCH
		--DO NOTHING
	END CATCH
	
	FETCH NEXT FROM ItemsToDelete INTO @DeletedID
END


SELECT COUNT(*)
  FROM TABLE

Select * From @DeletedItems

Close ItemsToDelete

Deallocate ItemsToDelete

Open in new window

0
 
Scott PletcherSenior DBACommented:
You would probably be much better off writing a query / code that could determine all the IDs that you want deleted, and only those rows, and then issue a single DELETE.

That is, use code to check all the (potentially) referenced tables and determine which ID(s) are eligible for deletion under your criteria, then delete just them.
0
 
farminsureAuthor Commented:
Hi Scott,

Do could you produce an example of determining the ID's prior to the delete statement? If this is possible, that would eliminate the fetching individual records and potentially cut script execution time very significantly.
0
 
Scott PletcherSenior DBACommented:
Sure, give me some time.
0
 
Scott PletcherSenior DBACommented:
Code below currently handles only a single FK column (that is, a REFERENCES with only one column, not more than one), but should give you an idea.  I can modify it *later* if needed to add more columns.  Or, based on it, you may be able to code out the rest by hand yourself for multi-column FKs.

USE ctsi_FSobj --specify db name containing table to delete rows from

SET NOCOUNT ON

IF OBJECT_ID('tempdb.dbo.#sql') IS NOT NULL
    DROP TABLE #sql

CREATE TABLE #sql (
    sqlId int IDENTITY(1, 1) NOT NULL,
    sql varchar(8000) NOT NULL
    )

DECLARE @schemaname varchar(128)
DECLARE @tablename varchar(128)
DECLARE @tablename_to_allow_rows_in_and_still_do_delete varchar(128)
DECLARE @sql varchar(8000)
DECLARE @sqlId int

SET @schemaname = 'dbo'
SET @tablename = 'table_to_delete_data_from'
SET @tablename_to_allow_rows_in_and_still_do_delete = 'ref_table_to_ignore'

INSERT INTO #sql SELECT
'DELETE FROM [' + @schemaname + '].[' + @tablename + ']'
INSERT INTO #sql SELECT
'FROM [' + @schemaname + '].[' + @tablename + '] AS tbl'
INSERT INTO #sql SELECT
'WHERE (1 = 1) AND'
INSERT INTO #sql
SELECT '( NOT EXISTS(SELECT TOP (1) NULL FROM ' + 
    '[' + CAST(refer_sch.name AS varchar(128)) + '].' +
    '[' + CAST(refer_o.name AS varchar(128)) + '] AS ref ' +
    'WHERE [ref].[' + refer_c.name + '] = [tbl].[' + parent_c.name + '] ) AND'
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects refer_o ON refer_o.object_id = fkc.referenced_object_id
INNER JOIN sys.schemas refer_sch ON refer_sch.schema_id = refer_o.schema_id
INNER JOIN sys.columns refer_c ON refer_c.object_id = fkc.referenced_object_id AND refer_c.column_id = fkc.referenced_column_id
INNER JOIN sys.columns parent_c ON parent_c.object_id = fkc.parent_object_id AND parent_c.column_id = fkc.parent_column_id
WHERE (@tablename_to_allow_rows_in_and_still_do_delete IS NULL OR 
    refer_o.name <> @tablename_to_allow_rows_in_and_still_do_delete)

SET @sqlId = SCOPE_IDENTITY()

UPDATE #sql
SET sql = LEFT(sql, LEN(sql) - 4) --remove trailing " AND"
WHERE sqlId = @sqlId

SELECT sql AS [--DELETE command(s) to be run, NOT run yet.]
FROM #sql
ORDER BY sqlId

SET NOCOUNT OFF

Open in new window

0
 
Scott PletcherSenior DBACommented:
CORRECTION!
Need to adjust the main SELECT WHERE clause as follows:

WHERE
    fkc.parent_object_id = (SELECT object_id FROM sys.objects o2 INNER JOIN sys.schemas s2 ON s2.schema_id = o2.schema_id
    WHERE o2.name = @tablename AND s2.name = @schemaname)
AND
    fkc.constraint_column_id = 1 --can add other columns by hand to WHERE clause of output if desired
AND
    (@tablename_to_allow_rows_in_and_still_do_delete IS NULL OR refer_o.name <> @tablename_to_allow_rows_in_and_still_do_delete)
0
 
farminsureAuthor Commented:
This will definitely run faster than my generic script; however, I would need to weigh the benefit vs cost of having to maintain the script with accurate joins.
0
 
Scott PletcherSenior DBACommented:
True.  Very best to extend the script to handle *all* columns in the FK, then just generate the full code every time you need it.

But, the code above *does* find *all* FK references for you, so all you have to do by hand is add all columns *past the first one*: luckily, often FKs are only one column.

And, as I'm sure you know, all you need to do for a multi-column FK is add to:
(NOT EXISTS .... WHERE ref.<col1> = parent.<col1>
the other colums:
AND ref.<col2> = parent.<col2> --AND ...
) AND ...

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 6
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now