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