CODEWITHSUNDEEP
sql-serversql-server-2008t-sqllockingblocking
Chris Cannon
Chris Cannon

Reputation: 1167

T-SQL Trigger Results in Blocking

Help! The following trigger results in blocking once executed:

USE [Automation]
GO
/****** Object:  Trigger [dbo].[AfterInsertSendEmail]    Script Date: 08/06/2012 22:05:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Chris Cannon
-- Create date: 20120806
-- Description: TBC
-- =============================================
ALTER TRIGGER [dbo].[AfterInsertSendEmail] 
   ON  [dbo].[Enquiry] 
   AFTER INSERT
AS 
BEGIN;
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for trigger here
    DECLARE @l_profile_name nvarchar(128) = 'Automation Enquiries';
    DECLARE @l_importance varchar(6) = 'High';
    DECLARE @l_sensitivity varchar(12) = 'Personal';
    DECLARE @l_recipients varchar(500) = '[email protected]';
    DECLARE @l_copy_recipients varchar(500) = '[email protected]';
    DECLARE @l_blind_copy_recipients varchar(500) = '[email protected]';
    DECLARE @l_reply_to varchar(500) = (SELECT [EmailAddress] FROM [inserted]);
    DECLARE @l_subject nvarchar(255) = (SELECT 'New Enquiry From ' + [FirstName] + ' ' + [LastName] FROM [inserted]);
    DECLARE @l_body nvarchar(500) = 'TBC';
    DECLARE @l_query nvarchar(500) = 'SELECT TOP(1) * FROM [Automation].[dbo].[Enquiry] ORDER BY [Id] DESC';
    DECLARE @l_query_result_separator char(1) = CHAR(9);
    DECLARE @l_query_result_no_padding bit = 1;
    DECLARE @l_attach_query_result_as_file bit = 1;
    DECLARE @l_query_attachment_filename nvarchar(260) = (SELECT 'automation_enquiry_' + REPLACE(REPLACE(REPLACE(CONVERT(nvarchar(19), [DateSubmitted], 120), '-', ''), ' ', ''), ':', '') + '.csv' FROM [inserted]);
    DECLARE @l_append_query_error bit = 1;

    EXEC [msdb].[dbo].[sp_send_dbmail]
          @profile_name = @l_profile_name
        , @importance = @l_importance
        , @sensitivity = @l_sensitivity
        , @recipients = @l_recipients
        , @copy_recipients = @l_copy_recipients
        , @blind_copy_recipients = @l_blind_copy_recipients
        , @reply_to = @l_reply_to
        , @subject = @l_subject
        , @body = @l_body
        , @query = @l_query
        , @query_result_separator = @l_query_result_separator
        , @query_result_no_padding = @l_query_result_no_padding
        , @attach_query_result_as_file = @l_attach_query_result_as_file
        , @query_attachment_filename = @l_query_attachment_filename
        , @append_query_error = @l_append_query_error;

END;

From what I can tell it looks like this line:

DECLARE @l_query nvarchar(500) = 'SELECT TOP(1) * FROM [Automation].[dbo].[Enquiry] ORDER BY [Id] DESC';

maybe blocking something or the trigger itself blocks that statement.

I've tried several things which no luck.

Please help!

Upvotes: 0

Views: 2334

Answers (2)

Tony Hopkinson
Tony Hopkinson

Reputation: 20320

Wel you are in the middle of a transaction so at least that row is locked, then you send a mail, the mail is running a select top on the same table which you have a lock on...

You could try nolock as losbear suggested, however seeing as the query you ae running is presumably meant to show the details of the record you've just inserted and you have inserted availble in your trigger, why bother with @query at all.

Personally I've never liked the idea of sending a mail in the middle of my transaction. I'd rather queue the request, commit the transaction and then have some "service" do the mailing. That would be a fair bit more work though.

Upvotes: 1

Losbear
Losbear

Reputation: 3315

try using "WITH (NOLOCK)"

SELECT TOP(1) * FROM [Automation].[dbo].[Enquiry] WITH (NOLOCK) ORDER BY [Id] DESC

Upvotes: 1

Related Questions