Trigger

Question

Trigger

suppiunna

SSC Veteran

Points: 225
More actions
February 4, 2015 at 10:09 am

#294500

Hi All,
I have a master table with after insert trigger on it.. When record is inserted into master table, the trigger fires and is captured in the backoffice table. In case the trigger fails, my record is neither in the master table nor in the back office table..
Is there anyway to capture the record either in the master table or in a separate table.
Many Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

you probably want to fix the trigger., or remove the trigger completely and move the migration from one table to another to a job that runs every x minutes.

as a rule of thumb, if the trigger is declaring an @variable, it's probably not handing multiple rows gracefully.

post your trigger for a bit of peer review, and we can help migrate it to a job or fix the trigger.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

suppiunna SSC Veteran Points: 225 More actions · Answer 2

The payment info from UI enters the Payment master table.. I created CDC on the master table.. So once the record is inserted in to Payments Master Table, the record is captured in the CDC table.. and the trigger on the CDC table writes it to the backoffice table.. At any point of time the no records in Master Table and the back office table should be the same..

In case of trigger fails, the record is neither in the CDC table nor in backoffice table but in the Master table. Hence we are trying to figure out how to capture if the record is not inserted in to backoffice table or the cdc table.

CREATE TRIGGER [cdc].[DataTransfer] ON [cdc].[Mam_MamDetail_CT] AFTER INSERT

AS

BEGIN TRY

SET NOCOUNT ON;

INSERT INTO cdc.MamdetailBackOffice

SELECT *

FROM INSERTED

END

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 3

WHAT has been the cause of the trigger failing? Specifically, what are the error message(s)?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

suppiunna SSC Veteran Points: 225 More actions · Answer 4

As of now Trigger is working fine..

But I am deleting one of the column from the backoffice table and checking for trigger failure conditions..

Lowell SSC Guru Points: 323518 More actions · Answer 5

suppiunna (2/4/2015)
The payment info from UI enters the Payment master table.. I created CDC on the master table.. So once the record is inserted in to Payments Master Table, the record is captured in the CDC table.. and the trigger on the CDC table writes it to the backoffice table.. At any point of time the no records in Master Table and the back office table should be the same..
In case of trigger fails, the record is neither in the CDC table nor in backoffice table but in the Master table. Hence we are trying to figure out how to capture if the record is not inserted in to backoffice table or the cdc table.
CREATE TRIGGER [cdc].[DataTransfer] ON [cdc].[Mam_MamDetail_CT] AFTER INSERT
AS
BEGIN TRY
SET NOCOUNT ON;
INSERT INTO cdc.MamdetailBackOffice
SELECT *
FROM INSERTED
END

the issue here is most likely permissions. where is the master table? is that [cdc].[DataTransfer] or dbo.masterTable somewhere else? i'm just checking whether schemas are involved, as most normal users with access to dbo schema probably don't have access to the cdc schema you created.

i'm not sure how records get inserted, but whether it's an application that uses a specific user, or domain users credentials being apssed through, the issue might be that the user that inserted data into [cdc].[Mam_MamDetail_CT] doesn't have permissions to insert into cdc.MamdetailBackOffice, which is required unless your trigger uses execute as.

another possibility is the schema itself: there's the lazy insert into without a column list, plus select *

are you sure that works? the column list of the two tables are identical,in the exact same order, and MamdetailBackOffice does not have an identity?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

suppiunna SSC Veteran Points: 225 More actions · Answer 6

My code is working fine as of now.. But I am trying to test the scenario for trigger failure and trying to capture the records which did not get inserted