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 [NewCustomerInte ractionNotify] ON [dbo].[qw_interaction]
AFTER INSERT AS

DECLARE @SOURCE INT
DECLARE @ID INT
SET @ID = (select qw_interactioni d 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_interactioni d =@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_sen d_dbmail @recipients='ji bran@jibran.com ', @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_interactioni d =@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_interactioni d? Is this an IDENTITY column?

-- CK

[Jibran]

qw_interactioni d 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_interactioni d.

[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!