[SOLVED] Deleting Entity fails if there is a trigger on a sql table

Need Help
1

Dear people,

I want to delete a user entity and the DELETE FROM statement doesn’t changes any rows (0). I got this information from the debug tools. If I execute the delete from command in the sql management studio, it successfully deletes the user.

I am using a MS SQL 2016 and created the following trigger for the users table, because I have to set references to this user to null before deleting it.

CREATE TRIGGER [dbo].[user_delete_trigger] ON [dbo].[users]
INSTEAD OF DELETE
AS
BEGIN
	UPDATE [dbo].[product_types] SET [created_user_id] = NULL WHERE [created_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[production_units] SET [created_user_id] = NULL WHERE [created_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[roles] SET [created_user_id] = NULL WHERE [created_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[languages] SET [created_user_id] = NULL WHERE [created_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[inspection_parameters] SET [created_user_id] = NULL WHERE [created_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[sexes] SET [created_user_id] = NULL WHERE [created_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[countries] SET [created_user_id] = NULL WHERE [created_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[inspection_detail_ratings] SET [created_user_id] = NULL WHERE [created_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[users] SET [created_user_id] = NULL WHERE [created_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[severities] SET [created_user_id] = NULL WHERE [created_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[bundlers] SET [created_user_id] = NULL WHERE [created_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[contract_productions] SET [created_user_id] = NULL WHERE [created_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[system_configurations] SET [created_user_id] = NULL WHERE [created_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[inspection_institutions] SET [created_user_id] = NULL WHERE [created_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[inspection_types_product_types] SET [created_user_id] = NULL WHERE [created_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[rewe_employees] SET [created_user_id] = NULL WHERE [created_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[bundlers_farms] SET [created_user_id] = NULL WHERE [created_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[farms_inspection_institutions] SET [created_user_id] = NULL WHERE [created_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[notes] SET [created_user_id] = NULL WHERE [created_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[inspection_details] SET [created_user_id] = NULL WHERE [created_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[inspection_types] SET [created_user_id] = NULL WHERE [created_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[inspection_ratings] SET [created_user_id] = NULL WHERE [created_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[farms] SET [created_user_id] = NULL WHERE [created_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[inspections] SET [created_user_id] = NULL WHERE [created_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[inspection_institutions_inspection_types] SET [created_user_id] = NULL WHERE [created_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[feature_types] SET [created_user_id] = NULL WHERE [created_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[farm_features] SET [created_user_id] = NULL WHERE [created_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[connected_farms] SET [created_user_id] = NULL WHERE [created_user_id] IN (SELECT [deleted].[id] FROM [deleted]);

	UPDATE [dbo].[product_types] SET [modified_user_id] = NULL WHERE [modified_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[production_units] SET [modified_user_id] = NULL WHERE [modified_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[roles] SET [modified_user_id] = NULL WHERE [modified_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[languages] SET [modified_user_id] = NULL WHERE [modified_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[inspection_parameters] SET [modified_user_id] = NULL WHERE [modified_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[sexes] SET [modified_user_id] = NULL WHERE [modified_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[countries] SET [modified_user_id] = NULL WHERE [modified_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[inspection_detail_ratings] SET [modified_user_id] = NULL WHERE [modified_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[users] SET [modified_user_id] = NULL WHERE [modified_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[severities] SET [modified_user_id] = NULL WHERE [modified_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[bundlers] SET [modified_user_id] = NULL WHERE [modified_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[contract_productions] SET [modified_user_id] = NULL WHERE [modified_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[system_configurations] SET [modified_user_id] = NULL WHERE [modified_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[inspection_institutions] SET [modified_user_id] = NULL WHERE [modified_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[inspection_types_product_types] SET [modified_user_id] = NULL WHERE [modified_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[rewe_employees] SET [modified_user_id] = NULL WHERE [modified_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[bundlers_farms] SET [modified_user_id] = NULL WHERE [modified_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[farms_inspection_institutions] SET [modified_user_id] = NULL WHERE [modified_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[notes] SET [modified_user_id] = NULL WHERE [modified_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[inspection_details] SET [modified_user_id] = NULL WHERE [modified_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[inspection_types] SET [modified_user_id] = NULL WHERE [modified_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[inspection_ratings] SET [modified_user_id] = NULL WHERE [modified_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[farms] SET [modified_user_id] = NULL WHERE [modified_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[inspections] SET [modified_user_id] = NULL WHERE [modified_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[inspection_institutions_inspection_types] SET [modified_user_id] = NULL WHERE [modified_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[feature_types] SET [modified_user_id] = NULL WHERE [modified_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[farm_features] SET [modified_user_id] = NULL WHERE [modified_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	UPDATE [dbo].[connected_farms] SET [modified_user_id] = NULL WHERE [modified_user_id] IN (SELECT [deleted].[id] FROM [deleted]);
	
	UPDATE [dbo].[logs] SET [user_id] = NULL WHERE [user_id] IN (SELECT [deleted].[id] FROM [deleted]);

	DELETE FROM [dbo].[users] WHERE [id] IN (SELECT [deleted].[id] from [deleted]);
END
2

Self solved…

It’s important to add the following line into the triggers code:

SET NOCOUNT ON;
– SET NOCOUNT ON added to prevent extra result sets from
– interfering with SELECT statements.

Then also the affected rowCount outputs the correct number :slight_smile: