SQL Trigger Question I have a trigger from a co-worker that isn't working as desired.  When an INSERT is done the insert never completes and the following two lines keep appearing in SQL Profiler over and over.  What is being done wrong?


  SP:StmtStarting   WHILE (SELECT count(*) from INSERTED ) > 0
    22  FINANCE_RPT 773577794   SENDTXT_MSG DLLEMSSQL08     76  2017-03-20
  09:18:26.707   SP:StmtStarting    WITH CTE AS           (     SELECT
  c.PHONE_NUM,c.PROVIDER,b.app_group,b.app_name
        FROM       SENDTXT_GRP b, sendtxt_emp c, sendtxt_emp_grp d
        WHERE c.emp = d.emp
        and (
        b.app_group = d.GRP_NAME or
        b.app_name = d.GRP_NAME
        )     ) SELECT @appname = c.app_name FROM INSERTED i   INNER JOIN CTE c ON i.object_name = c.app_name  where i.MSG_TYPE = 'ERROR'; 
  --EXEC msdb.dbo.sp_send_dbmail  --
          --@profile_name = 'AdventureWorks2012 Administrator',
          --@recipients = 'danw@Adventure-Works.com',
          --@body = 'Don''t forget to print a report for the sales force.',
          --@subject = 'Reminder';  
  
  22  FINANCE_RPT 773577794   SENDTXT_MSG DLLEMSSQL08     76  2017-03-20
  09:18:26.707


Trigger script:

USE [DATABASE_NAME]
GO

DROP TRIGGER [dbo].[SENDTXT_MSG] 
GO

CREATE TRIGGER [dbo].[SENDTXT_MSG]
ON [dbo].[DB_LOG]  
AFTER INSERT  
AS  
DECLARE
@appname varchar(50)
BEGIN
WHILE (SELECT count(*) from INSERTED ) &gt; 0      
WITH CTE AS                  (
       SELECT c.PHONE_NUM,c.PROVIDER,b.app_group,b.app_name
      FROM       SENDTXT_GRP b, sendtxt_emp c, sendtxt_emp_grp d
      WHERE c.emp = d.emp
      and (
      b.app_group = d.GRP_NAME or
      b.app_name = d.GRP_NAME
      ) 
         )
SELECT @appname = c.app_name FROM INSERTED i   INNER JOIN CTE c ON i.object_name = c.app_name  where i.MSG_TYPE = ‘ERROR’;
   --EXEC msdb.dbo.sp_send_dbmail  --
        --@profile_name = 'AdventureWorks2012 Administrator',  
        --@recipients = 'danw@Adventure-Works.com',  
        --@body = 'Don''t forget to print a report for the sales force.',  
        --@subject = 'Reminder';  

insert into test (test) values (@appname);
END
GO
The whole code of your trigger is weird. Why checking if SELECT COUNT(*) FROM INSERTED?, I mean, if the trigger is running is because you inserted some row on the table. Then, you are trying to assign posibly multiple values on a variable, and joining with a CTE when it really doesn't seem necessary. 

The code seems that it can be replaced with just:

CREATE TRIGGER [dbo].[SENDTXT_MSG] ON [dbo].[db_log] after 
INSERT AS 
BEGIN 

INSERT INTO test(test)
SELECT object_name
FROM INSERTED
WHERE msg_type = 'error';


END