'proc exec' Published Stored Procedure Called from Instead-Of-Delete Trigger Issue

Question

Hi,

I am using SQL 2008. I have many tables with interdependencies and instead-of-delete triggers for most of them. These triggers assure the deletion of all dependant data from dependant tables. Because I want to use replication and in some critical cases a lot of delete commands are queued at subscribers (making replication very slow) I thought of calling a stored procedure (SP) in the instead-of-delete trigger. This SP contains now the DELETEs which will call other triggers. The SP is published as 'proc exec'.

e.g.
Having 3 tables and 3 instead-of-delete triggers

CREATE TRIGGER dbo.TR_Test
   ON  dbo.Test
   INSTEAD OF DELETE
AS
BEGIN
    EXEC dbo.SP_Test TestId

    -- delete record from dbo.Test
END


CREATE TRIGGER dbo.TR_Test_Related
   ON  dbo.Test_Related
   INSTEAD OF DELETE
AS
BEGIN
    -- delete record from dbo.Test_Related_Related

    -- delete record from dbo.Test_Related
END

CREATE TRIGGER dbo.TR_Test_Related_Related
   ON  dbo.Test_Related_Related
   INSTEAD OF DELETE
AS
BEGIN
    -- delete records from additional related tables

    -- delete record from dbo.Test_Related_Related
END


CREATE PROCEDURE dbo.SP_Test
    @TestId uniqueidentifier
WITH EXECUTE AS CALLER
AS
BEGIN
    delete dbo.Test_Related from dbo.Test_Related WHERE dbo.Test_Related.TestId = @TestId;
   
    -- additional deletes   
END

The problem is that deleting a record on the publisher from dbo.Test all related records from dbo.Test_Related and dbo.Test_Related_Related are deleted as expected, but on subscribers the appropriate dbo.Test_Related_Related records are not deleted. As if the trigger dbo.TR_Test_Related_Related is not called on subscribers.
Any thoughts on this?

Laurentiu

Answer 1

Cascading deletes should work for you if both tables are being replicated.

Is there a reason you are using a stored procedure/trigger to do the deletes?

---

looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 2

All 3 tables are being replicated. So their triggers should be replicated as well, right (they are not marked 'NOT FOR REPLICATION')?

The reason I am using instead-of-delete triggers to do the deletes is because I want to remove all records from other tables referencing the record I want to delete (e.g. records from junction tables). At the end of the instead-of-delete trigger I delete the record. Unfortunatelly I can not use ON DELETE CASCADE since I have circular references in some of my tables.

Can you reproduce the issue with the above given details?

LaurentiuM

Answer 3

no I can't. Please post the schemas for these tables along with the triggers. Did you do a plain vanilla publication setup?

---

looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 4

I hope you can reproduce the problem with the following code for tables, SP and triggers creation. There is also code to insert data in the tables. In the end I put the code to create the publication and the subscription.
To reproduce the behavior delete the record from dbo.Test_A table. In the main database the records from Table_C and Table_D are deleted. In the replicated database the records from Table_C and Table_D are NOT deleted.

-- Creation of test tables
USE TestDB
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Test_A]'))
    DROP TABLE [dbo].[Test_A]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Test_B]'))
    DROP TABLE [dbo].[Test_B]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Test_C]'))
    DROP TABLE [dbo].[Test_C]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Test_D]'))
    DROP TABLE [dbo].[Test_D]
GO

CREATE TABLE dbo.Test_A
(
    Test_A_Id uniqueidentifier NOT NULL PRIMARY KEY
);

CREATE TABLE dbo.Test_B
(
    Test_B_Id uniqueidentifier NOT NULL PRIMARY KEY,
    Test_A_Id uniqueidentifier
);

CREATE TABLE dbo.Test_C
(
    Test_C_Id uniqueidentifier NOT NULL PRIMARY KEY,
    Test_B_Id uniqueidentifier,
    Test_A_Id uniqueidentifier
);

CREATE TABLE dbo.Test_D
(
    Test_D_Id uniqueidentifier NOT NULL PRIMARY KEY,
    Test_C_Id uniqueidentifier
);

-- Create the dbo.Test_A stored procedure
USE TestDB
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_Test_A]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[SP_Test_A]
GO

CREATE PROCEDURE dbo.SP_Test_A
    @Test_A_Id uniqueidentifier
WITH EXECUTE AS CALLER
AS
BEGIN
    DELETE dbo.Test_B FROM dbo.Test_B WHERE dbo.Test_B.Test_A_Id = @Test_A_Id;
END

-- Create the dbo.Test_A instead-of-delete trigger

IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[TR_Test_A]'))
    DROP TRIGGER [dbo].[TR_Test_A]
GO

CREATE TRIGGER dbo.TR_Test_A
   ON  dbo.Test_A
   INSTEAD OF DELETE
AS
BEGIN
    DECLARE @DeletedTest_A TABLE
    (
        Test_A_Id uniqueidentifier
    );
   
    DECLARE @DeletedRow uniqueidentifier;
   
    INSERT INTO @DeletedTest_A
        SELECT Test_A_Id FROM deleted;
       
    SELECT TOP 1 @DeletedRow = Test_A_Id FROM @DeletedTest_A

    EXEC dbo.SP_Test_A @DeletedRow

    DELETE dbo.Test_A FROM dbo.Test_A
        INNER JOIN @DeletedTest_A d ON
        dbo.Test_A.Test_A_Id = d.Test_A_Id;
END

-- Create the dbo.Test_B instead-of-delete trigger
USE TestDB
GO

IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[TR_Test_B]'))
    DROP TRIGGER [dbo].[TR_Test_B]
GO

CREATE TRIGGER dbo.TR_Test_B
   ON dbo.Test_B
   INSTEAD OF DELETE
AS
BEGIN
    DECLARE @DeletedTest_B TABLE
    (
        Test_B_Id uniqueidentifier
    );
   
    INSERT INTO @DeletedTest_B
        SELECT Test_B_Id FROM deleted;
   
    DELETE dbo.Test_C FROM dbo.Test_C INNER JOIN @DeletedTest_B d ON dbo.Test_C.Test_B_Id = d.Test_B_Id;
    DELETE dbo.Test_B FROM dbo.Test_B INNER JOIN @DeletedTest_B d ON dbo.Test_B.Test_B_Id = d.Test_B_Id;
END

-- Create the dbo.Test_C instead-of-delete trigger
USE TestDB
GO

IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[TR_Test_C]'))
    DROP TRIGGER [dbo].[TR_Test_C]
GO

CREATE TRIGGER dbo.TR_Test_C
   ON  dbo.Test_C
   INSTEAD OF DELETE
AS
BEGIN
    DECLARE @DeletedTest_C TABLE
    (
        Test_C_Id uniqueidentifier
    );
   
    INSERT INTO @DeletedTest_C
        SELECT Test_C_Id FROM deleted;
   
    DELETE dbo.Test_D FROM dbo.Test_D INNER JOIN @DeletedTest_C d ON dbo.Test_D.Test_C_Id = d.Test_C_Id;
    DELETE dbo.Test_C FROM dbo.Test_C INNER JOIN @DeletedTest_C d ON dbo.Test_C.Test_C_Id = d.Test_C_Id;
END

-- Insert some sample data
USE TestDB
GO

INSERT INTO dbo.Test_A VALUES('A0b674b6-6818-4e75-8932-143e20f3f2b4');

INSERT INTO dbo.Test_B VALUES('B7e30cf4-1823-4297-a4cb-ddbeaaaa8994', 'A0b674b6-6818-4e75-8932-143e20f3f2b4');

INSERT INTO dbo.Test_C VALUES('C91f40a8-454f-47ae-bb26-6e00c7cada4b', 'B7e30cf4-1823-4297-a4cb-ddbeaaaa8994', 'A0b674b6-6818-4e75-8932-143e20f3f2b4');

INSERT INTO dbo.Test_D VALUES('D5fb1851-4227-4386-871e-4071bf61db85', 'C91f40a8-454f-47ae-bb26-6e00c7cada4b');

-- Creation of the Publication

/****** Scripting replication configuration. Script Date: 02.11.2009 14:00:11 ******/
/****** Please Note: For security reasons, all password parameters were scripted with either NULL or an empty string. ******/

/****** Installing the server as a Distributor. Script Date: 02.11.2009 14:00:11 ******/
use master
exec sp_adddistributor @distributor = N'LAURENTIUM', @password = N''
GO
exec sp_adddistributiondb @database = N'distribution', @data_folder = N'D:\mssql\data', @log_folder = N'D:\mssql\data', @log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @security_mode = 1
GO

use [distribution]
if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U '))
    create table UIProperties(id int)
if (exists (select * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null)))
    EXEC sp_updateextendedproperty N'SnapshotFolder', N'\\LAURENTIUM\LAURENTIUM_TEST_PUBLICATION', 'user', dbo, 'table', 'UIProperties'
else
    EXEC sp_addextendedproperty N'SnapshotFolder', N'\\LAURENTIUM\LAURENTIUM_TEST_PUBLICATION', 'user', dbo, 'table', 'UIProperties'
GO

exec sp_adddistpublisher @publisher = N'LAURENTIUM', @distribution_db = N'distribution', @security_mode = 1, @working_directory = N'\\LAURENTIUM\LAURENTIUM_TEST_PUBLICATION', @trusted = N'false', @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER'
GO

use [TestDB]
exec sp_replicationdboption @dbname = N'TestDB', @optname = N'publish', @value = N'true'
GO
-- Adding the transactional publication
use [TestDB]
exec sp_addpublication @publication = N'Test_Publication', @description = N'Transactional publication of database ''TestDB'' from Publisher ''LAURENTIUM''.', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
GO

exec sp_addpublication_snapshot @publication = N'Test_Publication', @frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 1

use [TestDB]
exec sp_addarticle @publication = N'Test_Publication', @article = N'Test_A', @source_owner = N'dbo', @source_object = N'Test_A', @type = N'logbased', @description = N'', @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'Test_A', @destination_owner = N'dbo', @status = 0, @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_dboTest_A', @del_cmd = N'CALL sp_MSdel_dboTest_A', @upd_cmd = N'SCALL sp_MSupd_dboTest_A'
GO

use [TestDB]
exec sp_addarticle @publication = N'Test_Publication', @article = N'Test_B', @source_owner = N'dbo', @source_object = N'Test_B', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'Test_B', @destination_owner = N'dbo', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_dboTest_B', @del_cmd = N'CALL sp_MSdel_dboTest_B', @upd_cmd = N'SCALL sp_MSupd_dboTest_B'
GO

use [TestDB]
exec sp_addarticle @publication = N'Test_Publication', @article = N'Test_C', @source_owner = N'dbo', @source_object = N'Test_C', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'Test_C', @destination_owner = N'dbo', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_dboTest_C', @del_cmd = N'CALL sp_MSdel_dboTest_C', @upd_cmd = N'SCALL sp_MSupd_dboTest_C'
GO

use [TestDB]
exec sp_addarticle @publication = N'Test_Publication', @article = N'Test_D', @source_owner = N'dbo', @source_object = N'Test_D', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'Test_D', @destination_owner = N'dbo', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_dboTest_D', @del_cmd = N'CALL sp_MSdel_dboTest_D', @upd_cmd = N'SCALL sp_MSupd_dboTest_D'
GO

use [TestDB]
exec sp_addarticle @publication = N'Test_Publication', @article = N'SP_Test_A', @source_owner = N'dbo', @source_object = N'SP_Test_A', @type = N'proc exec', @description = N'', @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x0000000008000001, @destination_table = N'SP_Test_A', @destination_owner = N'dbo'
GO

-- Create Subscription

-----------------BEGIN: Script to be run at Publisher 'LAURENTIUM'-----------------
use [TestDB]
exec sp_addsubscription @publication = N'Test_Publication', @subscriber = N'LAURENTIUM', @destination_db = N'Test_Subscription', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'Test_Publication', @subscriber = N'LAURENTIUM', @subscriber_db = N'Test_Subscription', @job_login = null, @job_password = null, @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20091102, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor'
GO
-----------------END: Script to be run at Publisher 'LAURENTIUM'-----------------

Laurentiu

Answer 5

Why aren't the records deleted from the Test_C and Test_D tables on the Test_Subscription side? What happens with the calls from the triggers on the TestDB? Wouldn't they supposed to be replicated? At least sp_MSdel_dboTest_C and sp_MSdel_dboTest_D should be called. Why aren't they called?
I hope this diagram will give a better understanding of my question:
http://img104.imagevenue.com/img.php?image=42717_Replication_122_515lo.jpg

LaurentiuM

Answer 6

Hilary

Was the supplied information enough to reproduce the issue?

Does anybody have any idea why this is happening?

LaurentiuM

Answer 7

The only solution I found is to nest the stored procedures. This way they will be called on the replicated database and will detele the appropriate records.
Does anybody have a better solution to this problem?

LaurentiuM