VladTheLegend
VladTheLegend

Reputation: 1

Trigger Failing when calling Stored Procedure

I am truly hoping someone can help me out...

I have a trigger to handle the insert of a new record to a table. This trigger, as you will see below, inserts a record into another table, which in turns executes a trigger on that table, that calls a stored procedure (I tried to do it within the trigger itself, but it failed and was difficult to test where it was failing, so I moved it into its own little unit.)

Within the stored procedure, there is a call to extract information from the Active Directory database (ADSI) and update the newly inserted record. However, this is where it fails when called by the trigger. When I call it by simply executing it, and passing along the record to be updated, it works great... Can anyone point me in the right direction? Please!!!

Trigger #1 in YYY

USE [YYY]
GO
/****** Object:  Trigger [dbo].[NewCustodian]    Script Date: 08/04/2014 09:38:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[NewCustodian]
ON [YYY].[dbo].[Custodians]
AFTER INSERT
AS BEGIN
    SET NOCOUNT ON;

    DECLARE @CaseID varchar(20);
    DECLARE DBcursor CURSOR  FOR 
            SELECT [XXX].[dbo].[tblCase].CaseID from [XXX].[dbo].[tblCase] Where [XXX].[dbo].[tblCase].SQLSVR_Case_ID = 'YYY';
    Open DBcursor; FETCH DBCursor into @CaseID;
    CLOSE DBcursor; DEALLOCATE DBcursor;

    DECLARE @NAME varchar(255);
    DECLARE @TAG varchar(255);

    SELECT @NAME = name FROM inserted;
    SELECT @TAG = tag FROM inserted;

    IF NOT EXISTS (Select eID from [XXX].[dbo].[tblNames] 
                    WHERE eID = @TAG and CaseID = @CaseID)
    BEGIN
            INSERT INTO [XXX].[dbo].[tblNames] (CaseID, Name, eID) 
                Values (@CaseID, @NAME, @Tag);
        END
END

Trigger #2 in XXX

USE [XXX]
GO
/****** Object:  Trigger [dbo].[tblNames_New]    Script Date: 08/04/2014 08:56:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:       
-- Create date:  
-- Description:  
-- =============================================
ALTER TRIGGER [dbo].[tblNames_New]
ON [XXX].[dbo].[tblNames]
AFTER INSERT

AS BEGIN

    SET NOCOUNT ON;

DECLARE @NamesID varchar(10)
DECLARE @TAG varchar(10);
DECLARE @return_value int

SELECT @NamesID = namesID FROM inserted

EXEC dbo.UpdateNames @NamesID;
End

Stored procedure:

USE [XXX]
GO
/****** Object:  StoredProcedure [dbo].[UpdateNames]    Script Date: 08/04/2014 08:14:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:       
-- Create date: 
-- Description: 
-- =============================================
ALTER PROCEDURE [dbo].[UpdateNames] 
    @NamesID int 
AS
BEGIN
    SET FMTONLY OFF;
    SET NOCOUNT ON;

    DECLARE @eID varchar(10);
    DECLARE @TAG varchar(10);
    DECLARE @SQL nvarchar(555);
    DECLARE @DBresults as table (
        eID nvarchar(100),
        mobile nvarchar(100),
        mail nvarchar(100),
        phone nvarchar(100),
        name nvarchar(50),
        legacyExchangeDN nvarchar(100),
        Title nvarchar(100),
        homeDirectory nvarchar(100));
    DECLARE @mobile nvarchar(100)
    DECLARE @mail nvarchar(100)
    DECLARE @phone nvarchar(100) = 'Error'
    DECLARE @name nvarchar(100)
    DECLARE @legacyExchangeDN nvarchar(100)
    DECLARE @Title nvarchar(100) = 'Error'
    DECLARE @homeDirectory nvarchar(100)

    SET @eID = (Select eID from [XXX].[dbo].[tblNames] Where NamesID = @NamesID)

    SET @SQL = N'SELECT * FROM OpenQuery ( ADSI, ''SELECT homeDirectory,Title,legacyExchangeDN,displayName, telephoneNumber, mail, mobile,samAccountName
      FROM ''''LDAP://domain.com''''
      WHERE objectClass =  ''''User''''  and samAccountName = ''''' + @eID+ ''''''') As tblADSI'

    INSERT INTO @DBresults 
       EXEC sp_executesql @SQL

    DECLARE DBcursor CURSOR  FOR 
        SELECT * from @DBresults;
    Open DBcursor; FETCH DBCursor into @eID, @mobile, @mail, @phone, @Name, @legacyExchangeDN, @Title, @homeDirectory;
    CLOSE DBcursor; DEALLOCATE DBcursor;

    UPDATE XXX.dbo.tblNames
        SET Job_Title = @Title,
            Phone = @Phone
         Where NamesID = @NamesID;
END

Upvotes: 0

Views: 2217

Answers (1)

marc_s
marc_s

Reputation: 755541

As I said in my comment - a trigger should be extremely small, nimble, lean - do not do any extensive and time-consuming processing inside a trigger, and avoid anything that would cause performance bottlenecks, especially cursors!

The reason for this is the fact that a trigger will be triggered whenever an INSERT operation happens, you have no control over when and how many times it gets called. The main app will wait and hang while the trigger is at work - so therefore, don't make this a long time - return very quickly from your trigger to go on with your main app.

My approach would be:

  • create a new separate table where you insert some key pieces of information into from your first original trigger

    CREATE TABLE NewCustodianInserted 
    (
        ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, 
        CaseID VARCHAR(20), 
        Tag VARCHAR(255),
        Handled BIT DEFAULT (0)
    );
    
  • change your original trigger on the Custodians table to just insert those key pieces of information into your new "command" table:

    CREATE TRIGGER [dbo].[NewCustodian]
    ON [YYY].[dbo].[Custodians]
    AFTER INSERT
    AS BEGIN
       SET NOCOUNT ON;
    
       -- insert key pieces about the new custodian into "command" table
       INSERT INTO dbo.NewCustodianInserted (CaseID, Tag)
          SELECT i.CaseId, i.Tag
          FROM Inserted i
          WHERE NOT EXISTS (SELECT * FROM [XXX].[dbo].[tblNames] WHERE eID = i.Tag AND CaseID = i.CaseID)
    END
    
  • in a separate process, e.g. a SQL Server Agent job that is scheduled to run every 5 mînutes (or whatever makes sense for your application), read the "command" table, get the new custodians to handle, call that long-running stored procedure updating Active Directory from it. Here, since this runs asynchronously from your main application, it's ok to use a cursor which you almost have to since you want to call a stored procedure for every row in your new table.

    CREATE PROCEDURE HandleNewCustodians
    AS 
    BEGIN
       SET NOCOUNT ON;
    
       DECLARE @CaseID VARCHAR(20);
       DECLARE @Tag VARCHAR(255);
       DECLARE @NamesID varchar(10);
    
       DECLARE CustodianCursor CURSOR FAST_FORWARD
       FOR 
          SELECT CaseID, Tag FROM dbo.NewCustodianInserted WHERE Handled = 0
    
       OPEN CustodianCursor
    
       FETCH NEXT FROM CustodianCursor INTO @CaseID, @Tag;
    
       WHILE @@FETCH_STATUS = 0
       BEGIN
           SELECT @NamesID = NameID 
           FROM [XXX].[dbo].[tblNames] WHERE eID = @Tag AND CaseID = @CaseID
    
           EXEC dbo.UpdateNames @NamesID;
    
           FETCH NEXT FROM CustodianCursor INTO @CaseID, @Tag;
       END
    
       CLOSE CustodianCursor;
       DEALLOCATE CustodianCursor;
     END
    

Upvotes: 2

Related Questions