Solved

Triggers Does not Complete

Posted on 2012-04-03
15
204 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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

746 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