Nested Triggers - SQL Server Forums

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)

Nested Triggers

Author

Topic

igork
Starting Member

3 Posts

Posted - 2006-03-02 : 18:19:41

I am trying to implement trigger which can handle multirow updates and which is running on replicated table. So I want it never fails as trigger failure brakes replication.

So:

CREATE TRIGGER on_person_update

BEGIN

-- create temp table

-- populate temp table with Inserted values (I do not need Deleted as PK never change)

COMMIT TRAN

-- Am I right that this insures updates on replicated table will never be rollback after this commit?

BEGIN TRAN A

-- Make a checkpoint here to be able to rollback at any time to this point if something wrong inside loop.

SAVE TRAN MyTran

-- Start looping in temp table

-- RUN DML statement to make neccesary changes for each record in temp table

-- Does it make any sense to do this (IF @ERR below)? When I am trying in DML insert string value into integer column it never gets to IF statement - terminates straight away.

-- Reason why I think I need it as this trigger might be called by another trigger and top level trigger will get an error and can make a decision based on this.

IF @ERR <> 0
BEGIN
ROLLBACK TRAN MyTran
RAISERROR('Insert or Update failed in on_person_sls_update trigger with error: %s', 16, 1, @ERR)
RETURN
END

-- End looping temp

-- Do I need here COMMIT TRAN A or trigger will make commit anyway?

END

Why all of this?

Data changed on distributor and arrive to subscriber as a transaction.

We have a trigger on replicated table which will update replicated table in any way but after that it will update another database on subscriber.

This trigger should be able to handle multirow updates.

When this trigger updates another database it runs DML which fires other triggers so they become nested, if I am right. Our trigger should always accept changes from distributor as if it fails replication brakes but after data saved in temp table none or all changes have to be made.

May be I am copmpletely wrong with this template - hope somebody will help.

Thank you,

Igor

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-03-02 : 22:37:06

i believe you need a begin tran before the named transactions
and close that one after all the named transactions have been closed,

basically, you need to either commit or rollback all of them

--------------------
keeping it simple...

igork
Starting Member

3 Posts

Posted - 2006-03-02 : 22:57:49

More I read less understand.
This is an example from BOL (chapter Rollbacks in stored procedures and triggers)
_______________________________________________
You must use the SAVE TRANSACTION statement to do a partial rollback in a trigger, even if it is always called in autocommit mode. This is illustrated by the following trigger:

CREATE TRIGGER TestTrig ON TestTab FOR UPDATE AS
SAVE TRANSACTION MyName
INSERT INTO TestAudit
SELECT * FROM inserted
IF (@@error <> 0)
BEGIN
ROLLBACK TRANSACTION MyName
END
_______________________________________________________

If INSERT will fail IF statement will never ever be executed. Am I right?
Because trigger will issue rollback and quit.
What is a point if writing IF after INSERT in this example?

I understand when you check business logic with IF EXISTS, for example, and you find a problem -
you can RAISERROR and than ROLLBACK TRANSACTION MyName.
But when DML fails inside trigger what is a point of checking @@ERROR?

And, if I am right, this demonstrates one concept by breaking others.

Igor

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources