T-SQL: Double-cocked Trigger, Polling or a .NET Async Thread?

Question

So i bet i got you curious with that title. =)

Here's the scenario - simplified for the cause.

Website: ASP.NET 4 Web Forms.
Database: SQL Server 2008.
DAL: LINQ-SQL.

In the DB, we have two tables: Foo and Bar.

Relationship is 1-1, and Bar has a foreign key constraint to Foo. In other words, you need to create Foo first, then use the PK for Foo when creating Bar. The two tables join to create a business entity called FooBar.

Now, we have a scoring system in place for our website. Certain actions cause the need for scoring to be refreshed for particular FooBar's.

At the moment, we have triggers on both Foo and Bar tables. Both triggers calls a stored procedure which updates the scores for the particular FooBar.

So therein lies the problem - we create a new FooBar on the website, which causes entries in Foo and Bar to be created. Both triggers get fired (Foo first, then Bar), and the same stored procedure is executed twice. Doesnt do any actual harm, just not good for performance.

How can we get around this?

The stored procedure that updates the scores for FooBar's is simple - takes in a FooID.

We cannot perform the scoring until after the entry in Bar is created. Even though it currently does happen for the Foo trigger, the score turns out to be incorrect until its redone on the Bar trigger. Also a thing to note is that doing something on the website may not affect BOTH tables (ie you can edit a FooBar on the website and only end up modifying the Foo table, or only the Bar table, other times both).

The triggers are AFTER INSERT, UPDATE or DELETE.

Here's what i mean for three possible scenarios:

1. User creates a new FooBar:

   • New entry added to Foo
   • Trigger executed on Foo
   • Scoring SP Executed
   • FooBar scored incorrectly (because no Bar yet)
   • New entry added to Bar
   • Trigger executed on Bar
   • Scoring SP Executed
   • FooBar scored correctly

2. User edits the "Foo" for a FooBar:

   • Foo entry updated.
   • Trigger executed on Foo
   • Scoring SP Executed
   • FooBar scored correctly.

3. User edits the "Bar" for a FooBar:

   • Bar entry updated.
   • Trigger executed on Bar
   • Scoring SP Executed
   • FooBar scored correctly.

So scenarios 2 and 3 are fine - its when adding new FooBar's that causes the issue.

Here's my ideas so far:

1. Remove triggers from Foo and Bar. Create Asynchronous thread call in C# post-success creation of Foo and Bar table entries (ie after success of LINQ operations), calling stored procedure. Problem with this is firstly i always try to avoid threading in web sites unless absolutely required, furthermore its adding a dependency to the web server when in fact all of this logic and ownership should be (and currently is) on the database.

2. Create a "staging" table, the triggers would create an entry in the staging table (something like "FooID1", "ReadyToScore" - for example). Then a SQL Job would poll this table (say every 30 seconds) and execute the stored procedure. Obvious problems with this is extra load due to the polling, and more difficulty to debug.

Tough question to understand i know, so hopefully ive explained it ok.

Any thoughts?

Answer 1

You may also consider Asynchronous T-SQL Procedures, which eliminate the need for pooling job(s) and even for staging tables.

Answer 2

Decided to keep it as it is (doing processing twice).

Reasons: - The processing itself is quite efficient (< 1 sec). - Polling, threading, Async T-SQL VS two 1 second calls. No brainer there.