Solved

Triggers Does not Complete

Posted on 2012-04-03
15
205 Views
Last Modified: 2012-04-15
I have an on Insert trigger which seems to just hang.
Can someone have a look to see where I went wrong.
I have included the code below.

ALTER TRIGGER [IS_USA]
   ON  [dbo].[temp]
   AFTER INSERT


AS

-- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

Declare @IS_USA INT
Declare @LOCKED INT
Declare @IMPF_VALIDATED INT
Declare @WCT_AGENT_VALIDATED  INT
Declare @WCT_GIS_VALIDATED  INT
Declare @ESCAL_ID  INT
Declare @Comments NVARCHAR (19)
Declare @Date DATETIME
Declare @USER_ID INT
Declare @MTSS_NAME varchar(50)


SELECT @ESCAL_ID = (SELECT ESCAL_ID FROM Inserted)
SELECT @IS_USA = (SELECT IS_USA FROM Inserted)
SELECT @LOCKED = (SELECT LOCKED FROM Inserted)
SELECT @IMPF_VALIDATED = (SELECT IMPF_VALIDATED FROM Inserted)
SELECT @WCT_AGENT_VALIDATED = (SELECT WCT_AGENT_VALIDATED FROM Inserted)
SELECT @WCT_GIS_VALIDATED = (SELECT WCT_GIS_VALIDATED FROM Inserted)
SELECT @USER_ID = (SELECT USER_ID FROM Inserted)
SELECT @MTSS_NAME = (SELECT MTSS_NAME FROM INSERTED)


IF @IS_USA = '1' AND @LOCKED = '0' AND @IMPF_VALIDATED = '1' AND @WCT_AGENT_VALIDATED = '1' AND @WCT_GIS_VALIDATED = '1'

BEGIN

INSERT temp_email (Entry, EMAIL)

SELECT Entry, email
FROM Analyst  
WHERE ManagerName = @MTSS_NAME  

(select * from temp_email)

declare @sqlstr varchar(max),
            @crtid integer, @Getmail varchar (max)

set  @sqlstr =  ''

DECLARE email_list CURSOR FOR (SELECT Entry  FROM temp_email)
OPEN email_list
      FETCH next FROM email_list INTO @crtid
      WHILE @@fetch_status=0
      BEGIN
            set @sqlstr = @sqlstr + (select email from temp_email where Entry = @crtid) + ';'
      FETCH next FROM email_list INTO @crtid
      END
CLOSE email_list
DEALLOCATE email_list
DELETE temp_email
Print @sqlstr
SET @GetMail = @sqlstr

UPDATE temp
SET DISTRICT_EMAIL=@GETMAIL
WHERE ESCAL_ID = @ESCAL_ID


 INSERT into ACTIVE
       select * from TEMP where ESCAL_ID = @ESCAL_ID
   delete from TEMP where ESCAL_ID = @ESCAL_ID

INSERT into Site_updates(COMMENTS, DATESTAMP, ESCAL_ID, USER_ID)
VALUES ('Initial System Send', GETDATE(), @ESCAL_ID, @USER_ID)


END
0
Comment
Question by:xeroxcanada
  • 7
  • 7
15 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37804379
Do I need to point out to you that this query will never function when more than one row is INSERTed into temp?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37804407
If your answer is "I don't care"  than at least show that you know it by using code like this:
ALTER TRIGGER [IS_USA] ON [dbo].[temp]
    AFTER INSERT
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON ;

DECLARE @IS_USA INT 
DECLARE @LOCKED INT 
DECLARE @IMPF_VALIDATED INT 
DECLARE @WCT_AGENT_VALIDATED INT 
DECLARE @WCT_GIS_VALIDATED INT 
DECLARE @ESCAL_ID INT
DECLARE @Comments NVARCHAR(19)
DECLARE @Date DATETIME 
DECLARE @USER_ID INT
DECLARE @MTSS_NAME varchar(50)

SELECT	TOP (1)				-- Pick the "first" one, whatever that means
	@ESCAL_ID = ESCAL_ID,
	@IS_USA = IS_USA,
	@LOCKED = LOCKED,
	@IMPF_VALIDATED = IMPF_VALIDATED,
	@WCT_AGENT_VALIDATED = WCT_AGENT_VALIDATED,
	@WCT_GIS_VALIDATED = WCT_GIS_VALIDATED,
	@USER_ID = USER_ID,
	@MTSS_NAME = MTSS_NAME
FROM	Inserted
WHERE	IS_USA = '1'
	AND LOCKED = '0'
	AND IMPF_VALIDATED = '1'
	AND WCT_AGENT_VALIDATED = '1'
	AND WCT_GIS_VALIDATED = '1'

IF @@ROWCOUNT > 0 
BEGIN
    INSERT  temp_email(Entry, EMAIL)
    SELECT	Entry,
	email
    FROM	Analyst
    WHERE	ManagerName = @MTSS_NAME  

    --select * from temp_email

    DECLARE @sqlstr varchar(max),
	@crtid integer,
	@Getmail varchar(max)

    SELECT	ISNULL(@sqlstr, ';') + email
    FROM	temp_email
    WHERE	[Entry] = @crtid

    DELETE  temp_email
    
    PRINT @sqlstr
    SET @GetMail = @sqlstr
END

Open in new window

0
 
LVL 2

Author Comment

by:xeroxcanada
ID: 37805412
It will only return one row.
What about the other inserts?
0
 
LVL 2

Author Comment

by:xeroxcanada
ID: 37805471
I tried the new code and it still just hangs when I insert a new record from a web page.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37805638
It will only return one row.
You are missing the point.  A single INSERT statement that affects 100 rows fires the TRIGGER just once.  This is a critical difference that many new T-SQL developers miss.

I tried the new code and it still just hangs when I insert a new record from a web page.
I am afraid I have no idea, you are going to have to do some debugging.
0
 
LVL 2

Author Comment

by:xeroxcanada
ID: 37805790
I tried the portion of the code that updates the record and adds the email as a stored procedure and it works fine.

So is there a way that within the trigger I can call the SP and then return to the trigger to do the other Inserts?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37805870
You can definitely call a Stored Procedure from a TRIGGER.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 2

Author Comment

by:xeroxcanada
ID: 37807098
OK...How and when the SP is finished does it return to the trigger to continue on or is there a command that I put in the SP to have it return to the trigger?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37808518
It will return to the next step after the call to the Stored Procedure.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 37808548
You can do that, but if the sp hangs up for any reason, the trigger will also stall and you could tie up the table.

You might want to consider writing a message or using some other mechanism to pass the processing to an *async* process, so that the trigger could return immediately without waiting for the stored proc code to finish.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37808863
Absolutly!  Loading all that code in a TRIGGER plus all you have not shown us (and God forbid actually sending an email as well) and you have a recipe for disaster.
0
 
LVL 2

Author Comment

by:xeroxcanada
ID: 37823035
Can anyone propose a better way of doing this or give me sample code that will work?

It seem that it gets caught in a loop and never finishes.
0
 
LVL 2

Accepted Solution

by:
xeroxcanada earned 0 total points
ID: 37828459
Got this to work.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER TRIGGER [IS_USA]
ON [dbo].[temp]
AFTER INSERT
AS

DECLARE @p1 varchar (50)  
DECLARE @p2 varchar (10)


BEGIN
  SELECT                        
      @p1 = MTSS_Name,
      @p2 = Escal_ID,

FROM Inserted

      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here



INSERT temp_email (Entry, Email)

SELECT Entry, Email
FROM Analyst  
WHERE ManagerName = @p1

--(select * from temp_email)

declare @sqlstr varchar(max),
            @crtid integer, @Getmail varchar (max)

set  @sqlstr =  ''

DECLARE email_list CURSOR FOR (SELECT Entry  FROM temp_email)
OPEN email_list
      FETCH next FROM email_list INTO @crtid
      WHILE @@fetch_status=0
      BEGIN
            set @sqlstr = @sqlstr + (select email from temp_email where Entry = @crtid) + ';'
      FETCH next FROM email_list INTO @crtid
      END
CLOSE email_list
DEALLOCATE email_list
DELETE temp_email
--Print @sqlstr
EXEC CreateDL @sqlstr,@p2
END
--------------------------------------------------------------------------------------------------

SP_CreateDL

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[CreateDL]
      -- Add the parameters for the stored procedure here
          @EmailList NVARCHAR(MAX),
    @Escal integer
    AS
    BEGIN
    UPDATE dbo.temp SET DISTRICT_EMAIL=@EmailList WHERE ESCAL_ID=@Escal;
    END
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37830517
I see that this is a duplcate question, see here:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_27667585.html#a37830508

However, future readers should be aware that this code has two basic flaws:
1. It does not support INSERTs that affect more than one row.
2. In general it is considered that CURSORs should only be used as a last resort.  For obvious reasons, this is particulary important when writing the code for a TRIGGER.
0
 
LVL 2

Author Closing Comment

by:xeroxcanada
ID: 37847991
Solved it on my own
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now