Recursive Triggers and Replication

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Recursive Triggers and Replication

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-10-22 : 08:43:08

James writes "Hi,

I have a setup a recursive trigger to remove data in a heirarchy/tree table.

I can get it to work on a single sql server 2000 but it refuses to recursively call on a sql server that has been setup with transactional replication.

Is there anything I need to do to get it work, or are recursive triggers not allowed in a replicated environment, if so what is the alternative?

Cheers
James"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-22 : 12:21:57

Perhaps if you posted your trigger code, we might be able to help you out. Do you need to specify NOT FOR REPLICATION option in the trigger?

Tara

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2003-10-22 : 12:39:05

Check the RECURSIVE_TRIGGERS database option of your database, using DATABASEPROPERTYEX option.

--
HTH,
Vyas
http://vyaskn.tripod.com

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-22 : 14:56:29

So when it's off (I''m assuming it's the default setting), it prevents a trigger that may update a row for the table it's on?

If not, it'll just sit there firing continuously?

Is that right?

Brett

8-)

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2003-10-22 : 16:02:08

Yeah, OFF by default. When ON lets the trigger fire itself, when the trigger insert/update/deletes the same table it is on. There is a limit of 32 levels of nesting. More info at "Using Nested Triggers" in BOL.

--
HTH,
Vyas
http://vyaskn.tripod.com

JHotchkiss
Starting Member

6 Posts

Posted - 2003-10-23 : 04:03:42

The trigger is as follows:

CREATE TRIGGER trActorDelete ON dbo.Actor
FOR DELETE
NOT FOR REPLICATION
AS
BEGIN
IF @@rowcount = 0 RETURN

DELETE Actor
FROM Actor, deleted
WHERE Actor.SuperActorID = deleted.ActorID
END

Yes, I have turned on the 'RECURSIVE_TRIGGERS' dboption

Sample from the table:

ActorID SuperActorID ActorType
10 0 Customer
11 10 Division
74 11 User
75 11 User
76 11 User
77 11 User
78 11 User
79 10 Division
80 79 User

The command:

DELETE Actor WHERE ActorID = 10

Should delete the whole tree (i.e. all records) but on a replicated environment it doesn't call itself again (leaving the Users), turn off replication and it works.

I have also tried it with the NOT FOR REPLICATION and without with the same results.

Any ideas?

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2003-10-23 : 07:12:03

What kind of replication in place? Is this table a published table or subscribing table?

--
HTH,
Vyas
http://vyaskn.tripod.com

JHotchkiss
Starting Member

6 Posts

Posted - 2003-10-23 : 07:27:18

Using transactional replication set to allow immediate and queued updates, have pushed a subscription to the other SQL Server which is set to queue updates.

The Actor table is part of the publication which includes all the user tables, no filters, basically a pretty standard setup.

What I want to know is:

Why is my recursive trigger not working, which leads me to three possible scenarios:

1. Recursive triggers are not allowed in a replication environment (can this be confirmed, and if so what is an alternative to a recursive trigger)

2. The replication hasn't been setup properly.

3. Something else is affecting the trigger.

So if someone could tell me its not the first one that would be a start.

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2003-10-24 : 16:39:41

I take it that the trigger is created on the published table. Also, you/application is deleting rows, not replication (while applying the queued updates). If the above are true, then something is really strange. I'll have to try this Monday and see what results I get.

--
HTH,
Vyas
http://vyaskn.tripod.com

JHotchkiss
Starting Member

6 Posts

Posted - 2003-10-29 : 04:01:26

I created the trigger after I published the table and applied it to both databases and the application runs the SQL Delete command through an ADO connection (I leave the queuing up to SQL Server).

JHotchkiss
Starting Member

6 Posts

Posted - 2004-01-23 : 06:33:22

Hi,

I have just come back to this problem, and I have now found after turning off replication (completly turned off publication as well, etc) I can't even create a simple recursive trigger that works properly (using table in the format id, parentid) the trigger fires once to remove the next layer down then stops, trigger as follows:

CREATE TRIGGER trActorDelete ON dbo.Actor
FOR DELETE
AS
IF @@rowcount = 0 RETURN

DELETE dbo.Actor
FROM dbo.Actor AS A JOIN deleted AS D
ON A.SuperActorID = D.ActorID

There are no other triggers that fire and recursive triggers are enabled on the database. This appears to be some bug, I imagine it caused the original problem.

Any chance someone has come accross this before?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources