SQL Trigger Question

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 = '[email protected]',
--@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 ) > 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 = '[email protected]', 
 --@body = 'Don''t forget to print a report for the sales force.', 
 --@subject = 'Reminder'; 
insert into test (test) values (@appname);
END
GO

• Scott Hodgin - Retired: The virtual table inserted has more than zero rows, so you're never getting out of your WHILE loop. What's the purpose of the WHILE LOOP?

Answer 1

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