I am looking into work-arounds for what seems to be a flaw, or
"undocumented feature" of SQL Server replication and Instead of Delete
triggers not playing together. It seems that if you want to use
replication then you cannot use Instead Of triggers as it prohibits the
replication triggers from firing. My delima is that I need to cascade
delete, but first have the record and all child records inserted into
associated delete tables (<tablename>_del). This worked well using
Instead of delete triggers, but now we are moving to a replication
environment and it does not synchronize deletes to the publisher or
subscriber.
The options I am looking into and the stumbling points they introduce
are:
1. Not using Instead Of triggers, but using After to insert the
deleted record into a <tablename>_del table (where of course
<tablename> is the actual table name). The triggers are designed to
cascade delete all related child records. The problem with this
option is that there are relationship constraints that cause the
delete to raise an error complaining about these constraints. Of
course the child records need to be deleted first, but since this
runs after the delete I cannot perform this cascade delete via
trigger (or can I?).
2. Same as above only using SQL Server 2k's "Cascade Delete"
option. My question on this is, will the cascade delete execute the
After Delete triggers for each child table in the relationship? If
not, is there a way to move these deleted records into
<tablename>_del before they are deleted, keeping in mind that it
must be compatible with merge replication.
3. Not yet found any information on this, but is it possible to
manually include information to be replicated after the "Instead Of"
triggers so that the changes are included in the replication?
I really appreciate the advice.
