467,910 Members | 1,774 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,910 developers. It's quick & easy.

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
Oct 13 '08 #1
  • viewed: 1324
Share:
8 Replies
ck9663
Expert 2GB
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
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 2GB
What's the data type of qw_interactionid? Is this an IDENTITY column?

-- CK
Oct 14 '08 #4
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 2GB
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
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 2GB
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
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.

Similar topics

11 posts views Thread by raulgz | last post: by
1 post views Thread by jason_s_ford | last post: by
4 posts views Thread by Mark Flippin | last post: by
4 posts views Thread by JoeyD | last post: by
3 posts views Thread by rick | last post: by
4 posts views Thread by --CELKO-- | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.