SQL Event send Email

August 26, 2011 at 13:21:25
Specs: Win7, 6GB
I am trying to create a stored procedure to launch an email when a ticket is created by the 'portal' user. The email part works. I can just run this part of the script and this works, DB mail and all that is setup properly. I am stuck on the logic part of how to launch the email when this event happens. If anyone has ideas let me know?

Createuser ID: = ''UBQHXA00300P'
Table: sysdba.ticket
Field: ticket.createuser

USE [DBName]
GO
/****** Object:  StoredProcedure [dbo].[sendPortalEmail]    Script Date: 08/25/2011 11:39:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sendPortalEmail] AS 
  DECLARE @portaluser varchar(10) 
  
 <pre>

 IF UPDATE(CREATEUSER) 
	BEGIN
		SELECT 
		@portaluser = 'UBQHXA00300P'
  
  EXEC msdb.dbo.sp_send_dbmail @profile_name='portal',
  @recipients='email@domain.com', 
  @subject = 'Portal Ticket Created' 
 END


See More: SQL Event send Email

Report •

#1
August 26, 2011 at 13:47:14
It sounds like you're asking about database triggers.

How To Ask Questions The Smart Way


Report •

#2
August 29, 2011 at 05:58:56
Yes DB triggers or stored procedures whatever works.

Report •

#3
September 27, 2011 at 12:53:47
Testing this now... This appears to work... through when I enter this ticket into our database. We are currently using Sales Logix, users can no longer create a ticket.

USE [SalesLogix_Test]
GO
/****** Object: Trigger [sysdba].[SLX_Portal_Ticket] Script Date: 09/27/2011 15:17:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Jake Bingen>
-- Create date: <9/27/11>
-- Description: <will fire an email when a ticket is created by the portal user>
-- =============================================
ALTER TRIGGER [sysdba].[SLX_Portal_Ticket]
ON [SalesLogix_Test].[sysdba].[TICKET]
AFTER INSERT
AS
BEGIN
IF (SELECT TICKET.CREATEUSER()) = 'UBQHXA00300P'
BEGIN
EXEC msdb.dbo.sp_send_dbmail @profile_name='slxportal',
@recipients='email@domain.com',
@subject = 'SLX Ticket Has Been Created'
END
END


Report •

Related Solutions

#4
September 27, 2011 at 14:16:31
If "CREATEUSER" is a field in this "TICKET" table, you should be able to use ":new.CREATEUSER" instead of "sysdba.TICKET.CREATEUSER"

I don't have a Oracle instance to test this with, and I don't do much SQL programming, so take the above with a grain of salt, and all that.

How To Ask Questions The Smart Way


Report •

Ask Question