I believe I have quickly exceeded my SQL and SLX knowledge with this one...

I would like to create a DB trigger for each time a ticket is created by the Portal user.  (user ID is below)  I would like to use this feature for the SLX Customer Portal.  If 'portal' user creates a ticket send an email. 

This code seems to work but I can not test it.  When I place this trigger on the sysdba.ticket table users in the SLX LAN Client (and everywhere else I assume) cannot create a ticket.  It errors out.  I can post the error code but removing the trigger fixes this issue.

So I am wondering if anyone else uses the SLX Customer Portal and how they notify their users of a new ticket.  I know you can create a 'Group' which looks at 'Portal' tickets but this needs to be refreshed. And no notification of new tickets.

<p>USE [SalesLogix]
 

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Create date: <9/27/11>

-- Description: <will fire an email when a ticket is created by the portal user>

-- =============================================

CREATE TRIGGER [sysdba].[SLX_Portal_Ticket] 

   ON  [SalesLogix].[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

One thing that I have learned about Triggers and SalesLogix is that you typically want to set the NOCOUNT option to ON.

Otherwise, when an Insert is sent to the DB, it reports more than 1 record being affected. Since SalesLogix is expecting the INSERT to only affect 1 row, it triggers a Rollback on the Transaction.

So, add SET NOCOUNT ON somewhere at the beginning of the trigger and then SET NOCOUNT OFF at the end of the Trigger and see if that makes a difference.

What version of SLX is this? If this is the new customer portal web stuff (v7.2 or higher) and not the old web stuff, an easy place to put this is in the entity model on the OnInsert event for tickets. Do you happent to be on v7.2 or higher?

Ryan> we are on version 7.5.3.4233. This is something I did not consider. How do I place this into the entity model on the OnInsert event for tickets? On the ticket form? Or in the Application Architect> Customer Portal> Pages> Entity Group Viewer?
Vaul> set it up like this same error message. SET NOCOUNT ON/OFF in the wrong places?

<p>USE [SalesLogix]
 

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

SET NOCOUNT ON 

GO

-- =============================================

-- Author: <Jake Bingen>

-- Create date: <9/27/11>

-- Description: <will fire an email when a ticket is created by the portal user>

-- =============================================

CREATE TRIGGER [sysdba].[SLX_Portal_Ticket] 

   ON  [SalesLogix].[sysdba].[TICKET]

   AFTER INSERT

AS 

BEGIN

IF (SELECT TICKET.CREATEUSER()) = 'UBQHXA00300P'

 BEGIN  

     EXEC msdb.dbo.sp_send_dbmail @profile_name='slxportal',            

     @recipients='email@domain', 

     @subject = 'SLX Ticket Has Been Created' 

END

SET NOCOUNT OFF

END

Yes, the SET NOCOUNT is in the Wrong Place. You are setting the NoCount ON while creating the Trigger, not while the trigger executes.

You have to added within the BEGIN/END tags of the trigger. The SET NOCOUNT OFF is on the right place, but the SET NOCOUNT ON is on the wrong place. See code below for correct location

CREATE TRIGGER [sysdba].[SLX_Portal_Ticket] 

   ON  [SalesLogix].[sysdba].[TICKET]

   AFTER INSERT AS 

BEGIN

SET NOCOUNT ON

IF (SELECT TICKET.CREATEUSER()) = 'UBQHXA00300P'

BEGIN  

     EXEC msdb.dbo.sp_send_dbmail @profile_name='slxportal',            

     @recipients='jacob.bingen@wonderwaremidwest.com', 

     @subject = 'SLX Ticket Has Been Created' 

END

SET NOCOUNT OFF

END

Updated the SQL trigger with correct placement of SET NOCOUNT ON.  Same error message as before.  Worth a try.

Any ideas on the entity model?

Jacob:

  You never posted your actual error when adding the Trigger.

  I added it to an Eval DB on my end, and realized what you may be doing wrong.

  I debugged the Trigger, and this line was always failing on me:

IF (SELECT TICKET.CREATEUSER()) = 'UBQHXA00300P'

  This syntax is not working for me, so instead I just used the following:

  This is what I did instead (Keep in mind that the INSERTED recordset could have more than one record at a time, so it is typically better to use a CURSOR to loop through it, but this may sufficient if tickets are created one at a time): 

 <p>CREATE TRIGGER SLX_Portal_Ticket
ON  TICKET  
AFTER INSERT
AS
BEGIN

 

SET NOCOUNT ON

  DECLARE @CreateUser AS CHAR(12)
  SELECT @CreateUser = CREATEUSER FROM INSERTED

  IF @CreateUser = 'UBQHXA00300P' 
  BEGIN       
 EXEC msdb.dbo.sp_send_dbmail @profile_name='slxportal',
 @recipients='email@domain',
 @subject = 'SLX Ticket Has Been Created'
  END
  SET NOCOUNT OFF
END
 
 
 
 

This worked.  I was able to create a ticket in the SLX Client after creating the trigger.

However when I logged into the Customer Portal and attempted to submit a ticket this error message was generated:

The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'.

I will look into this message.  Looks like sp_send_dbmail needs permission to run the trigger.

Just had to add sysbda to the msdb databasemailuserRole.

Anyways this all works I am sent an email.  The ticket is created in the SLX Client.

However on the SLX Customer Portal they get a pop up... 1?  Where is this one being generated from?  Can I make it say ticket created?

Okay this gets more interesting...

If I place this line of code after SET NOCOUNT OFF...

RAISERROR ('Your ticket has been submitted', 16, 10);

I get 'Mail Queued' ?  I assume the 1 earlier is the reference to this 'error' message?  Anyway I can override this to say something different.  

I attempted to do: <  RAISEERROR 1 'Your ticket etc'   > but this breaks it.  I tried putting BEGIN and END before and after the statement.  This breaks it as well.  Also removing the 16 and 10 from the orginal statement break it as well.  I tried looking these up but help was limited.

Does this help anyone troubleshoot this further?  Any other ideas?  'Mail Queued' is better than '1' but I would still prefer- 'Ticket Queued' or something similar.  

Thanks.