Sql-server – SQL Trigger Question

sql servertrigger

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 ) > 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

Best Answer

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
Related Question