AFTER INSERT Failure Rollbacks entire batch

Question

AFTER INSERT Failure Rollbacks entire batch

s-387006

SSC Rookie

Points: 44
More actions
July 8, 2007 at 2:43 am

#372758

I have a trigger like this:
CREATE TRIGGER trgInsertPRT_T ON PRT_T AFTER INSERT AS
INSERT INTO tblNComponent(PRT_CODE) SELECT PRT_CODE FROM inserted
GO
I want to Insert Statement in PRT_T table committed even if the trigger fails. I do not want to use IF NOT EXISTS in my trigger like this:
CREATE TRIGGER trgInsertPRT_T ON PRT_T AFTER INSERT AS
IF NOT EXISTS (SELECT PRT_CODE FROM tblNComponent
WHERE PRT_CODE=(SELECT PRT_CODE FROM inserted))
INSERT tblNComponent(PRT_CODE) SELECT PRT_CODE FROM inserted
GO
how can i do this?
thanks

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

By definition, if the trigger fails, the WHOLE transaction fails. I'm sure there are ways to go around this but I'm more interested as the WHY do you want to do this?

What is the problem you need to solve with this trigger?

s-387006 SSC Rookie Points: 44 More actions · Answer 2

Here is the Condition:

I have a Published table named PRT_T from a transactional replication from oracle publisher. PRT_T has one field(PRT_CODE) that is PK.

The table tblNComponent must have the same PRT_CODE that PRT_T have.

I want Update and Insert operations in PRT_T cascade in tblNComponent but Delete operation does not.

With FK I could not do this and use trigger. but when Replicating one PRT_CODE that exists in tblNComponent, trigger fails.

is there a better way while:

I DO NOT want to merge PRT_T and tblNComponent in One Table?

Thanks

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

Your trigger is assuming there's only one row inserted. If there's more than one, the not exists could return true (for 1 of the records) but on another there's a conflict.

Try this rather

CREATE TRIGGER trgInsertPRT_T ON PRT_T AFTER INSERT AS

INSERT tblNComponent(PRT_CODE)

SELECT PRT_CODE

FROM inserted i LEFT OUTER JOIN tblNComponent c ON i.PRT_CODE = c.PRT_CODE

WHERE c.PRT_CODE IS NULL

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

s-387006 SSC Rookie Points: 44 More actions · Answer 4

s-387006

SSC Rookie

Points: 44

July 15, 2007 at 12:02 pm

#719533

Thanks.