Crazy long trigger

Question

Post reply

Crazy long trigger

DBAmes

Old Hand

Points: 366
More actions
March 22, 2012 at 11:38 am

#391847

I have a staging database where I receive data once a day that needs to be placed in different databases based on a field value. I have a trigger setup on the table that has an IF statement for each database to process. The trigger for a single database update is about 200 lines of code. So with all the extra databases the trigger is over 1000 lines. within the trigger we reference the destination database to pull the correct keys for insert.
I would like to clean this up for better maintainability. Would it be recommended to keep as is, move to dynamic sql in the trigger or to switch to an external SQL stored procedure that is called differently.
thanks.

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

i would agree; an error in the trigger could prevent data from being inserted;

cross-database data migration does not belong in a trigger.

an outside process could error out, but the next time it was called, could process anything not yet processed in the "master" table.

i would definitely move the logic to an outside proc, or SSIS package and call it via a scheduled job.

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!

CapnHector SSCoach Points: 16143 More actions · Answer 2

Lowell (3/22/2012)
i would agree; an error in the trigger could prevent data from being inserted;
cross-database data migration does not belong in a trigger.
an outside process could error out, but the next time it was called, could process anything not yet processed in the "master" table.
i would definitely move the logic to an outside proc, or SSIS package and call it via a scheduled job.

we do a similar process. we upload data using ssis to a staging database manipulate it a bit then over night insert into the production database.

this also lets us check that the data is correct in the staging database during the day and then schedule the job. has kept most errors out of production and lets us script out any changes we need to make to the base data on a continuing basis.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

DBAmes Old Hand Points: 366 More actions · Answer 3

for the Scheduled jobs, it is a single job with Dynamic SQL for each database or do you have seperate Stored procedures for each production database?

CapnHector SSCoach Points: 16143 More actions · Answer 4

ames.f (3/22/2012)
for the Scheduled jobs, it is a single job with Dynamic SQL for each database or do you have seperate Stored procedures for each production database?

we use a separate sql server agent job for each production database. we could use SP's but found that the actual t-sql in the agent jobs was more efficient for what we needed to do. YMMV (Your Mileage May Vary)

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]