AnnA-607068 (5/10/2013)

---

I am rewriting a FOR DELETE trigger. The current trigger, written in 2004 when we were still on SQL Server 2000, assumes only one row will be deleted at a time; it checks tables on another server to see if the item has history in those tables. If it does, an error is raised and the record is not deleted. If not, then a row in a table on another server in another database is deleted and the record in the deleted table is processed.

This is the old trigger:

CREATE TRIGGER [dbo].[del_Test$Vendor] ON [dbo].[Test$Vendor]

FOR DELETE

AS

SET NOCOUNT ON

SET XACT_ABORT ON

DECLARE @TRUE BIT,

@FALSE BIT

SET @TRUE = 1

SET @FALSE = 0

DECLARE @cDbNameVARCHAR(50),

@cServerVARCHAR(50),

@cCommandNVARCHAR(1000),

@cStatementNVARCHAR(1500)

CREATE TABLE #cur_delete(can_delete bit)

SET @cDbName = DB_NAME()

SET @cServer = interface.dbo.cf_db_id(@cDbName)

SET @cStatement = ''

DECLARE @cVendorIdVARCHAR(20)

DECLARE cur_vendor CURSOR LOCAL FOR

SELECT [No_]

FROM deleted

OPEN cur_vendor

FETCH NEXT FROM cur_vendor

INTO @cVendorId

WHILE @@FETCH_STATUS = 0

BEGIN

SET @cStatement = 'Select SQL2000.dbo.cf_ven_delete(''''' + @cVendorId + ''''') as can_delete'

SET @cCommand = 'SELECT can_delete FROM OPENQUERY(' + @cServer + ', ''' + @cStatement + ''')'

Insert into #cur_delete

EXEC sp_executesql @cCommand

IF (SELECT can_delete FROM #cur_delete) = @TRUE

BEGIN

SET @cStatement =

'DELETE FROM ' + interface.dbo.cf_db_id(@cDbName)COLLATE Latin1_General_100_CS_AS + '.taxsystem.dbo.vendor

WHERE vendor_id = ''' + @cVendorId + ''''

Print @cStatement

EXEC sp_executesql @cStatement

END

ELSE

BEGIN

--- Raise an error, do not continue processing

RAISERROR ('Vendor Has History in In-House Systems. Cannot Delete.', 16, 1) WITH NOWAIT

END

FETCH NEXT FROM cur_vendor

INTO @cVendorId

END

CLOSE cur_vendor

DEALLOCATE cur_vendor

When multiple records are deleted, the old trigger fails.

I can't seem to wrap my mind around how to process the records so that the items in the deleted table will only be deleted if there is no history in the other tables. I know how to delete only the records in the related table, but not how to selectively process the records in the deleted table based on the presence/absence of history. Does anyone have any suggestions? The code I've written so far follows. The deleted records are coming from a third party application, so I have no control over the records coming in. Any help would be greatly appreciated!

ALTER TRIGGER [dbo].[del_Test$Vendor] ON [dbo].[Test$Vendor]

FOR DELETE

AS

SET NOCOUNT ON

SET XACT_ABORT ON

IF EXISTS (SELECT 'x'

FROM deleted d

JOIN LTQASQL1.shared.dbo.VendorHistory h

ON d.No_ = h.VendorId COLLATE SQL_Latin1_General_CP1_CI_AS

WHERE h.HasHistory = 1)

BEGIN

--This obviously will prevent all the records in the deleted table from being processed

--not just those that have history

RAISERROR ('Vendor Has History in In-House Systems. Cannot Delete.', 16, 1) WITH NOWAIT

RETURN

END

ELSE

BEGIN

DELETE tcv

FROM LTQASQL1.TaxSystem.dbo.Vendor tcv

JOIN deleted d

ON tcv.vendor_id = d.No_ COLLATE Latin1_General_100_CS_AS

JOIN LTQASQL1.shared.dbo.VendorHistory h

ON d.No_ = h.VendorId COLLATE SQL_Latin1_General_CP1_CI_AS

WHERE h.HasHistory = 0

END