Triggers in SQL Server

[Jibran]

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='[e-mail]', @body= @msg, @subject = 'Ticket Update Notification', @profile_name = 'Test Profile'
END

[ck9663]

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

[Jibran]

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.

[ck9663]

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

-- CK

[Jibran]

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.

[ck9663]

Thought so.

Handling IDENTITY inside triggers is a little bit tricky.

Here's something you can read to help you.

-- CK

[Jibran]

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

[ck9663]

This seems to work:

create table tblidentity
(pkidentifier int identity, mycolumn varchar(50))
 
 
create trigger trgtblIdentityAfterInsert
on tblIdentity
after insert
as
begin
    declare @id as int
 
    set @id = (select pkidentifier from inserted)
 
    select  'the value of @id is ' + cast(@id as varchar(5))
end
 
 
insert into tblIdentity (mycolumn) values ('1st')
 
select * from tblIdentity
 
drop table tblIdentity

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

-- CK

[Jibran]

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