T-SQL trigger, nondeterministic error, SQL Server

Question

Following situation:

We get billing information for some calls from an external source. They only have a timestamp, the caller and the called service number. We have to find a unique ID in our database for the corresponding data set to be able to process the data.

service == msn + ddi

Now the problem:

The following SELECT causes a nondeterministic NULL return if called within an after-insert-trigger on table a.

If called separately the select always gets a result, I verified that manually using the external data.

-> The data from the external source is not the cause!

Any suggestion? I write a SQL script to explain the functionality. You can run this script to understand how it works.

    CREATE TABLE [dbo].[table_b] (
    [id] [int] IDENTITY(1,1) NOT NULL,
    [msn] [varchar](25) NOT NULL,
    [ddi] [varchar](10) NOT NULL,
    [caller] [varchar](25) NOT NULL,
    [timestamp] [datetime] NOT NULL,
CONSTRAINT [PK_table_b] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[table_a](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [table_b_id] [int] NULL,
    [caller] [varchar](25) NOT NULL,
    [service] [varchar](36) NOT NULL,
    [call_time] [datetime] NOT NULL,
CONSTRAINT [PK_table_a] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[table_a]  ADD CONSTRAINT [FK_table_a_ref_table_b] FOREIGN KEY([table_b_id])
REFERENCES [dbo].[table_b] ([id])
ON DELETE SET NULL
GO


CREATE TRIGGER [dbo].[On_table_a_after_insert] ON [dbo].[table_a] AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @inserted CURSOR
    DECLARE @a_id INT, @caller VARCHAR(25), @service VARCHAR(36), @call_time DATETIME, @error VARCHAR(255)
    DECLARE @dt_st DATETIME, @dt_end DATETIME, @b_id INT, @msn VARCHAR(25), @ddi VARCHAR(10)

    SET @inserted = CURSOR FORWARD_ONLY FOR (
        SELECT [id], [caller], [service], [call_time] FROM inserted)

    OPEN @inserted

    FETCH NEXT FROM @inserted INTO @a_id, @caller, @service, @call_time

    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        SET @dt_st = DATEADD(SECOND, -15, @call_time)
        SET @dt_end = DATEADD(SECOND, 15, @call_time)
        --the timestamp in table b can differ, caused by automatically insertion 
        --through an external software

        --some error checks if the data is valid
        --not needed here because I tested the data manually

        BEGIN TRY
            --find best matching data from table_b
            SELECT TOP 1 @b_id=[id], @msn=[msn], @ddi=[ddi]
            FROM [dbo].[table_b]
            WHERE [caller] = @caller AND [timestamp] > @dt_st AND [timestamp] < @dt_end AND ([msn] + [ddi]) = SUBSTRING(@service, 1, LEN([msn] + [ddi]))
            ORDER BY ABS(DATEDIFF(SECOND,[timestamp],@call_time)) ASC
        END TRY
        BEGIN CATCH
            SET @error = 'Failed to retrieve data from [table_b] for table_a ID = ' + CAST(@a_id AS VARCHAR(10)) + '!'
            RAISERROR (@error,11,1)
            FETCH NEXT FROM @inserted INTO @a_id, @caller, @service, @call_time
            CONTINUE
        END CATCH
        IF (@b_id IS NULL)
        BEGIN
            --sometimes this error is raised (with valid data)
            SET @error = 'No data found in [table_b] table for table_a ID = ' + CAST(@a_id AS VARCHAR(10)) + '!'
            RAISERROR (@error,11,1)
            FETCH NEXT FROM @inserted INTO @a_id, @caller, @service, @call_time
            CONTINUE
        END
        --update the reference
        UPDATE [dbo].[table_a]
        SET [table_b_id] = @b_id
        WHERE [id] = @a_id

        FETCH NEXT FROM @inserted INTO @a_id, @caller, @service, @call_time
    END

    CLOSE @inserted
    DEALLOCATE @inserted
END

INSERT INTO [dbo].[table_b] ([caller],[timestamp],[msn],[ddi])
VALUES ('004984199376893','2011-09-01 01:31:21.000','9005778808','8')
INSERT INTO [dbo].[table_b] ([caller],[timestamp],[msn],[ddi])
VALUES ('00494516116143','2011-09-01 08:50:44.000','9005778808','7')
INSERT INTO [dbo].[table_b] ([caller],[timestamp],[msn],[ddi])
VALUES ('004962069090587','2011-09-01 09:25:28.000','9005232464','')
INSERT INTO [dbo].[table_b] ([caller],[timestamp],[msn],[ddi])
VALUES ('004923074387247','2011-09-01 09:32:37.000','9001122567','')
INSERT INTO [dbo].[table_b] ([caller],[timestamp],[msn],[ddi])
VALUES ('004923074387247','2011-09-01 09:48:24.000','9001122567','')
INSERT INTO [dbo].[table_b] ([caller],[timestamp],[msn],[ddi])
VALUES ('004923074387247','2011-09-01 09:50:49.000','9001122567','')
INSERT INTO [dbo].[table_b] ([caller],[timestamp],[msn],[ddi])
VALUES ('00493685704108','2011-09-01 13:30:47.000','9001220774','')
INSERT INTO [dbo].[table_b] ([caller],[timestamp],[msn],[ddi])
VALUES ('004971624629971','2011-09-01 16:04:35.000','9005882230','')
INSERT INTO [dbo].[table_b] ([caller],[timestamp],[msn],[ddi])
VALUES ('004971624629971','2011-09-01 16:11:18.000','9005882230','')
INSERT INTO [dbo].[table_b] ([caller],[timestamp],[msn],[ddi])
VALUES ('004984199376893','2011-09-02 02:14:41.000','9005778808','8')
--to table a with call_time's difference
INSERT INTO [dbo].[table_a] ([caller],[service],[call_time])
VALUES ('004984199376893','90057788088','2011-09-01 01:31:23.000')
INSERT INTO [dbo].[table_a] ([caller],[service],[call_time])
VALUES ('00494516116143','90057788087','2011-09-01 08:50:46.000')
INSERT INTO [dbo].[table_a] ([caller],[service],[call_time])
VALUES ('004962069090587','9005232464','2011-09-01 09:25:33.000')
INSERT INTO [dbo].[table_a] ([caller],[service],[call_time])
VALUES ('004923074387247','9001122567','2011-09-01 09:32:40.000')
INSERT INTO [dbo].[table_a] ([caller],[service],[call_time])
VALUES ('004923074387247','9001122567','2011-09-01 09:48:28.000')
INSERT INTO [dbo].[table_a] ([caller],[service],[call_time])
VALUES ('004923074387247','9001122567','2011-09-01 09:50:53.000')
INSERT INTO [dbo].[table_a] ([caller],[service],[call_time])
VALUES ('00493685704108','9001220774','2011-09-01 13:30:48.000')
INSERT INTO [dbo].[table_a] ([caller],[service],[call_time])
VALUES ('004971624629971','9005882230','2011-09-01 16:04:39.000')
INSERT INTO [dbo].[table_a] ([caller],[service],[call_time])
VALUES ('004971624629971','9005882230','2011-09-01 16:11:21.000')
INSERT INTO [dbo].[table_a] ([caller],[service],[call_time])
VALUES ('004984199376893','90057788088','2011-09-02 02:14:41.000')

Answer 1

You could try the following query instead of your cursor:

WITH cte AS (
  SELECT
    a.id,
    b_id = b.id,
    rnk  = ROW_NUMBER() OVER (
      PARTITION BY a.id
      ORDER BY ABS(DATEDIFF(SECOND, timestamp, a.call_time)) ASC
    )
  FROM inserted a
    INNER JOIN dbo.table_b b
       ON b.caller = a.caller
      AND b.timestamp > DATEADD(SECOND, -15, a.call_time) 
      AND b.timestamp < DATEADD(SECOND, +15, a.call_time)
      AND b.msn + b.ddi = SUBSTRING(a.service, 1, LEN(b.msn + b.ddi))
)
UPDATE dbo.table_a
SET table_b_id = cte.b_id
FROM cte
WHERE cte.id = dbo.table_a.id
  AND cte.rnk = 1
;

Maybe there's something I missed, but at least, when I tested, this UPDATE produced the same results as the cursor in your trigger.

I didn't change your logic too much, but, as a matter of fact, this bit

…
AND b.msn + b.ddi = SUBSTRING(a.service, 1, LEN(b.msn + b.ddi))
…

might render the query non-sargable. I would probably split this particular check into two:

…
AND b.msn = SUBSTRING(a.service, 1,              LEN(b.msn))
AND b.ddi = SUBSTRING(a.service, LEN(b.msn) + 1, LEN(b.ddi))
…

---

Useful reading:

• WITH common_table_expression (Transact-SQL)

• Ranking Functions (Transact-SQL)

  • ROW_NUMBER (Transact-SQL)