Triggers in SQL Server

In the script below, the body of the email sent out is blank. The SELECT statement in the IF condition for TICKET_NUM fails to execute. Can someone help, thanks:

CREATE TRIGGER [NewCustomerInteractionNotify] ON [dbo].[qw_interaction]
AFTER INSERT AS

DECLARE @SOURCE INT
DECLARE @ID INT
SET @ID = (select qw_interactionid from INSERTED)
SET @SOURCE = (select qw_source from INSERTED)



IF @SOURCE = 7
BEGIN
DECLARE @msg varchar(500)
DECLARE @TICKET_NUM INT
SET @TICKET_NUM= (SELECT qw_ticketid from qw_inter_ticket where qw_interactionid =@ID)
SET NOCOUNT ON
SET @msg = 'A new interaction has been added through CP to Ticket #:'+(SELECT CONVERT(varchar, @TICKET_NUM))
--// CHANGE THE VALUE FOR @recipients
EXEC msdb.dbo.sp_send_dbmail @recipients='jibran@jibran.com', @body= @msg, @subject = 'Ticket Update Notification', @profile_name = 'Test Profile'
END

re: Triggers in SQL Server

I can't see any IF condition for TICKET_NUM

Also, if you just use

SET @msg = 'A new interaction has been added through CP to Ticket #:'

as the body of your email, is it successful? If it is, then the value of your @ticket_num is null which means your subquery is empty.

-- CK

re: Triggers in SQL Server

Yes if i comment out + CONVERT..... from @body, it works fine.

If I manually run a query on TICKET_NUM using SELECT qw_ticketid from qw_inter_ticket where qw_interactionid =@ID), it displays the proper value. Are there any special steps that needs to be followed when running subqueries in trigger script as I am having this issue with some other triggers that don't yield any results.

re: Triggers in SQL Server

What's the data type of qw_interactionid? Is this an IDENTITY column?

-- CK

re: Triggers in SQL Server

qw_interactionid isn't an IDENTITY column, it is the Primary Key of the table qw_interaction. qw_inter_ticket links the qw_ticketid with qw_interaction so I am trying to retrieve the ticket # using qw_interactionid.

re: Triggers in SQL Server

Thought so.

Handling IDENTITY inside triggers is a little bit tricky.

Here's something you can read to help you.

-- CK

re: Triggers in SQL Server

The link didn't help, I still need help on this issue. Have someone else come across a similar position.

re: Triggers in SQL Server

This seems to work:

You have to run the create trigger separately. Try to insert two or more records.

-- CK

re: Triggers in SQL Server

Thanks alot CK, it works now. You're the master!