By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,575 Members | 1,948 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,575 IT Pros & Developers. It's quick & easy.

Triggers in SQL Server

P: 30
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
Oct 13 '08 #1
Share this Question
Share on Google+
8 Replies


ck9663
Expert 2.5K+
P: 2,878
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
Oct 13 '08 #2

P: 30
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.
Oct 13 '08 #3

ck9663
Expert 2.5K+
P: 2,878
What's the data type of qw_interactionid? Is this an IDENTITY column?

-- CK
Oct 14 '08 #4

P: 30
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.
Oct 14 '08 #5

ck9663
Expert 2.5K+
P: 2,878
Thought so.

Handling IDENTITY inside triggers is a little bit tricky.

Here's something you can read to help you.

-- CK
Oct 14 '08 #6

P: 30
The link didn't help, I still need help on this issue. Have someone else come across a similar position.
Oct 16 '08 #7

ck9663
Expert 2.5K+
P: 2,878
This seems to work:

Expand|Select|Wrap|Line Numbers
  1. create table tblidentity
  2. (pkidentifier int identity, mycolumn varchar(50))
  3.  
  4.  
  5. create trigger trgtblIdentityAfterInsert
  6. on tblIdentity
  7. after insert
  8. as
  9. begin
  10.     declare @id as int
  11.  
  12.     set @id = (select pkidentifier from inserted)
  13.  
  14.     select  'the value of @id is ' + cast(@id as varchar(5))
  15. end
  16.  
  17.  
  18. insert into tblIdentity (mycolumn) values ('1st')
  19.  
  20. select * from tblIdentity
  21.  
  22. drop table tblIdentity
You have to run the create trigger separately. Try to insert two or more records.

-- CK
Oct 16 '08 #8

P: 30
Thanks alot CK, it works now. You're the master!
Oct 18 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.