April 30, 2010 at 9:16 am
I have an AFTER INSERT trigger that does inserts on another database and then updates the row that was just inserted, it works fine. My question is if I do a rollback in any part of the trigger it also rolls back the inserted row, can I do a rollback on just the things that happen inside the trigger not the inserted row that caused the trigger to fire in the first place?
for example
1. row is inserted in database Leadcollection table leads2
2. insert trigger is fired
2a. row is inserted in database crm table person
2b. row is inserted in db crm table opportunity and blows up for some reason
3. I want to rollback the row inserted in 2a
4. Leave the row inserted in 1.
does this make sense? and is it possible to do? Below is the trigger I have now if there is any kind of error inside of the trigger the row in step 1 is also rolled back
trigger in current state
ALTER TRIGGER [dbo].[Insert_CreateCRMPersonOpportunity] ON [dbo].[Leads2]
AFTER INSERT
AS
BEGIN
DECLARE @err1 int, @Err2 int, @Err3 int, @Err4 int, @Err5 int
DECLARE @Pers_PersonId INT,
@Oppo_OpportunityId INT
SET NOCOUNT ON;
-- BEGIN TRANSACTION Insert_Lead
BEGIN TRANSACTION
--Get the next identity for the Person table
EXEC @Pers_PersonId = CRM.dbo.crm_next_id
@table_id = 13
SET @err1 = @@ERROR
INSERT INTO CRM.dbo.Person (
Pers_PersonId,
Pers_FirstName,
Pers_LastName,
Pers_PhoneNumber,
Pers_CreatedDate
)
SELECT
@Pers_PersonId,
vcFName,
vcLName,
vcPhoneNo,
GETDATE()
FROM INSERTED
SET @Err2 = @@ERROR
--Get the next identity for the Opportunity table
EXEC @Oppo_OpportunityId = CRM.dbo.crm_next_id
@table_id = 10
SET @Err3 = @@ERROR
INSERT INTO crm.dbo.Opportunity (
Oppo_OpportunityId,
Oppo_PrimaryPersonId,
Oppo_CreatedDate,
oppo_movedate,
oppo_BergerOffice,
oppo_originaddress3,
oppo_origincity,
oppo_originstate,
oppo_originzipcode,
oppo_destinationaddress,
oppo_destinationcity,
oppo_deststate,
oppo_destinationzipcode,
oppo_firstcomments
)
SELECT
@Oppo_OpportunityId,
@Pers_PersonId,
GETDATE(),
dtEstMoveDate,
vcBergerOffice,
vcOrigAddress,
vcOrigCity,
vcOrigState,
vcOrigPostalCode,
vcDestAddress,
vcDestCity,
vcDestState,
vcDestPostalCode,
CASE
WHEN LEN(vcCompName) > 0 THEN vcCompName + '-' + vcNotes
ELSE vcNotes
END
FROM INSERTED
SET @Err4 = @@ERROR
UPDATE dbo.Leads2
SET dtModifyDate = GETDATE(),
vcModifyUser = 'Trigger',
inStatusid = 1,
inCRMPersonID = @Pers_PersonId,
inCRMOppID = @Oppo_OpportunityId
WHERE inLeaduid = (SELECT inLeaduid FROM INSERTED)
SET @Err5 = @@ERROR
--Set @err1 = 99
IF @err1 = 0 AND @Err2 = 0 AND @Err3 = 0 AND @Err4 = 0 AND @Err5 = 0
BEGIN
--COMMIT TRANSACTION Insert_Lead
COMMIT TRANSACTION
END
ELSE
BEGIN
--ROLLBACK TRANSACTION Insert_Lead
ROLLBACK TRANSACTION
END
END
Thanks in advance
April 30, 2010 at 12:00 pm
A rollback inside a trigger will roll back the row that fired the trigger. However, you can use a try/catch block to catch the error and take appropriate action when it happens. You can nest try catch blocks as well.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply