Triggers Does not Complete

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
LVL 2
xeroxcanadaAsked:
Who is Participating?
 
xeroxcanadaConnect With a Mentor Author Commented:
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
 
Anthony PerkinsCommented:
Do I need to point out to you that this query will never function when more than one row is INSERTed into temp?
0
 
Anthony PerkinsCommented:
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
xeroxcanadaAuthor Commented:
It will only return one row.
What about the other inserts?
0
 
xeroxcanadaAuthor Commented:
I tried the new code and it still just hangs when I insert a new record from a web page.
0
 
Anthony PerkinsCommented:
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
 
xeroxcanadaAuthor Commented:
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
 
Anthony PerkinsCommented:
You can definitely call a Stored Procedure from a TRIGGER.
0
 
xeroxcanadaAuthor Commented:
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
 
Anthony PerkinsCommented:
It will return to the next step after the call to the Stored Procedure.
0
 
Scott PletcherSenior DBACommented:
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
 
Anthony PerkinsCommented:
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
 
xeroxcanadaAuthor Commented:
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
 
Anthony PerkinsCommented:
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
 
xeroxcanadaAuthor Commented:
Solved it on my own
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.