Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying to make sure a trigger doesn't cause trouble. (Quixotic, I know!) Is there more I can do to the trigger below to make it bullet-proof?

Details:

We have a huge table we don't control (maintained by a closed-source 3rd party product) that we have to do ad-hoc reporting against. The reports have to reflect (very nearly) up-to-date data throughout the day. The table is too big and too active for us to do the reports directly against it. It also, sadly, has no combination of column values that we can use to uniquely identify a row.

So the approach we've come up with is:

  1. Use the trigger below to write inserts, updates, and deletes that occur on the big table (MainTable) to a temporary staging table (StagingTable).

  2. Use a stored procedure we run separately, once a minute, to remove rows from the staging table, filter and massage the data, and use that data to update the table we actually run the reports against (ReportingTable). So the staging table typically has very little data in it; it might build up, oh, maybe as many as a thousand rows during any given minute.

So:

-(3rd party product)-> [MainTable] -(trigger)-> [StagingTable] -(sproc)-> [ReportingTable]
                                                                               ^
                                                                               |
                                                                      Reporting Queries

The point is to do the filtering and massaging in our stored procedure and our transaction, not the 3rd party product's. We want to do as little as possible in the trigger because we don't want to disturb the 3rd party product's work. In particular it's vital that we don't introduce errors or delays that might make the 3rd party product's operation on MainTable fail.

Here's the trigger modified with descriptive names:

CREATE TRIGGER TheTrigger
ON MainTable
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    BEGIN TRY
        INSERT INTO dbo.StagingTable
            SELECT RelevantColumns, 'D' AS RowType
            FROM   deleted
        INSERT INTO dbo.StagingTable
            SELECT RelevantColumns, 'I' AS RowType
            FROM   inserted
    END TRY
    BEGIN CATCH
        -- Do nothing
    END CATCH
END
GO

(After reading this question and answer I'm not sure that try/catch is helping us much...)

StagingTable has no indexes, no constraints, and no triggers; its only NOT NULL columns are also NOT NULL in MainTable (in fact, other than the RowType column, the column definitions are identical). And again, it normally doesn't have much data in it.

Our once-a-minute sproc tries to avoid keeping StagingTable busy for any longer than necessary:

-- Not using OUTPUT because at one point we had to support SQL Server 2000
-- (could probably use DELETE...OUTPUT now)
BEGIN TRAN
    INSERT INTO @inserts
        SELECT      *
        FROM        StagingTable
        WITH        (UPDLOCK, HOLDLOCK)
        WHERE       RowType = 'I'
    INSERT INTO @deletes
        SELECT      *
        FROM        StagingTable
        WITH        (UPDLOCK, HOLDLOCK)
        WHERE       RowType = 'D'
    DELETE FROM StagingTable
COMMIT TRAN

...where @inserts and @deletes are table-valued variables in the sproc.

Are there reasonable scenarios where this would disrupt the 3rd party product's inserts, updates, and deletes on MainTable? If so, is there anything further we can do to fix that? Either a tweak to the above, or an entirely different, better approach?

share|improve this question
    
You might be a candidate for Service Broker or an asynchronous CLR trigger but otherwise seems a fair starting point. Do you have a baseline of what your current throughput is without the trigger so that you can observe whether the impact of adding it adversely affects app performance? –  billinkc Nov 19 '13 at 16:29
1  
@billinkc I was thinking Service Broker for this as well. He could remove some complexity by just sending the row a XML to a queue. –  JNK Nov 19 '13 at 16:30
    
@billinkc: We don't, and we ought to have had. The trigger has been in place a while and things have been fine. (There was an earlier version of it that did all its work in the trigger [no staging, no sproc]. That...wasn't fine. :-) ) We've identified what we think may be an issue with the 3rd party product and I'm trying to rule out the trigger as a potential cause (and just generally keep it as lean as possible). –  T.J. Crowder Nov 19 '13 at 16:33
    
Have you considered using: Log Shipping, database snapshots, snapshot isolation, CDC, or Change Tracking? I have a hard time believing none of these are suitable as at least part of your solution. –  Jon Seigel Nov 19 '13 at 16:48
    
@JonSeigel: Well, the above is a form of CDC. Not the form I'd prefer, but lacking a means of uniquely identifying a row limits my options. :-) Can you expand on how you'd expect log shipping, database snapshots, or snapshot isolation to apply? Absolutely open to options. I just keep coming back to that unique row problem. The odds of all columns having exactly the same value across two valid rows is small, though. (None of the key columns, even combined, can be relied upon in that way.) –  T.J. Crowder Nov 19 '13 at 17:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.