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?
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:
Use the trigger below to write inserts, updates, and deletes that occur on the big table (
MainTable) to a temporary staging table (
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.
-(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
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
@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?