Login / Register  search  syndication  about

          Jason Buss' Blog

Jason Buss on SalesLogix development & customization, SQL, and more.

Using SQL triggers to enforce data rules in SalesLogix.

In a recent project, I used triggers to enforce some data rules in the Ticket area of SalesLogix.  We did this because our client was using both the LAN as well as the Web clients.  We were having some difficulty seeing changes in the LAN client when values were set in the Web, and vice-versa.

If you're not familiar with them, triggers are code that are executed automatically in response to an event occurring on a SQL table.  SQL supports triggers for insert, update and delete operations.  In this case, I only had to deal with update triggers, since I needed to set certain fields based on a user either checking a checkbox, or setting a particular ticket status.

I have a extended table off of Ticket (Ticket_Ext), which contained a boolean (ProblemClear) and a date field (ProblemClearDate).  I had to do the following, based on if the ProblemClear field was checked:

  • If True and ProblemClearDate is Empty, set ProblemClearDate to the current date.
  • If True and ProblemClearDate is NOT empty, do nothing.
  • If False, clear the ProblemClearDate field.

 In addition, I had a couple rules for when the Ticket Status value was changed:

  • If the value is 'Closed', and ProblemClear was not checked, set ProblemClear to 'T' and set ProblemClearDate to the current date
  • If the value is not 'Closed' and ProblemClearDate is not empty, clear ProblemClearDate

 For the most part, the syntax creating the triggers is pretty simple.  Basically, we're just writing a standard Update query.  The thing you have to keep in mind, however; is that triggers are defined at the table level and not for specific fields.  In order to evaluate specific fields, you have to utilize specialized system tables 'Inserted' and 'Deleted' that are in SQL.  Whenever a field is updated, a temporary record in the 'Inserted' table is created, and 'Deleted' is populated when a field value is deleted.  There is no corresponding 'Updated' table, so if a field value is updated, then an entry is added to 'Deleted' and then to 'Inserted'.  There is an update function which returns a true value if the field passed to the function had indeed been updated.

 Here's the code I wrote to build these triggers based on the rules listed above:

Create TRIGGER uptClearFlag
ON TICKET_EXT
FOR UPDATE AS
If update (ProblemClear)
Begin
    Update ticket_ext set ProblemClearDate = GetDate()
    where ticketid in (select ticketid from inserted)
    and (ProblemClear = 'T') and ((ProblemClearDate = '' or ProblemClearDate is null))

    Update ticket_ext set ProblemClearDate = Null
    where ticketid in (select ticketid from inserted)
    and (ProblemClear = 'F') and (ProblemClearTime is not null)
End
go

Create TRIGGER uptTicketStatus
ON Ticket
FOR UPDATE AS
If update (StatusCode)
Begin
    Update a set a.ProblemClear = 'T'
    from ticket_ext a
        inner join ticket b on a.ticketid = b.ticketid
        inner join picklist c on b.statuscode = c.itemid
    where a.ticketid in (select ticketid from inserted)
    and (c.text = 'Closed')

    Update a set a.ProblemClearDate = null
    from ticket_ext a
        inner join ticket b on a.ticketid = b.ticketid
        inner join picklist c on b.statuscode = c.itemid
    where a.ticketid in (select ticketid from inserted)
    and (c.text <> 'Closed')   

End

go

As you can see, I join my SLX Tables to the 'Inserted' system table so that I can find the correct record that I want to update.   It also bears mentioning that triggers would not Sync to remotes, so they are not really an option in that case unless you created the same triggers on each remote database as well.

-Jason

 
Follow JasonBussCFX on Twitter

What's This?
  
Bookmark and Share

About Jason Buss

   Jason is a senior application developer with Customer FX.



Related Content
   Editing Filters in Saleslogix Web Client 8.0
Is it possible to add or remove Saleslogix filters from the List view in Accounts?
Posted on Apr 08, 2014 by SalesLogix Support to SalesLogix Questions & Answers
 
   Bug in the Saleslogix Web LiveGrid Lookup dialog
I recently ran into an issue with the Opportunity Products grid in the Saleslogix web client.  I cli
Posted on Apr 04, 2014 by Kris Halsrud to Kris Halsrud's Blog
 
   Saleslogix Web Client Controls are Missing
Every so often we hear that a user can no longer add products to an opportunity whether it is a new oppor
Posted on Mar 24, 2014 by Mark Duthoy to SalesLogix Support
 
   Major Problem in Saleslogix v8.1 Cloud Instances
I have been working with a new client who is on the hosted cloud solution for Saleslogix version 8.1.&nbs
Posted on Mar 19, 2014 by Kris Halsrud to Kris Halsrud's Blog
 
   Setting the Defaults on the Insert Account/Contact Duplicate Record View
When creating a new Account/Contact record in Saleslogix web, you have the ability to check for duplicat
Posted on Mar 10, 2014 by Jason Buss to Jason Buss' Blog
 
Comments

 

Twitted by GeekFetch said:

Pingback from  Twitted by GeekFetch

February 5, 2010 9:33 AM
 

Jason Buss' Blog said:

I previously posted about using an update trigger to enforce data rules in SalesLogix. Since I was working

March 1, 2011 1:23 PM

Leave a Comment

(required)  
(optional)
(required)  
Add
All contents Copyright © 2014 Customer FX Corporation
Customer FX Corporation
2324 University Avenue West, Suite 115
Saint Paul, Minnesota 55114
Tel: 800.728.5783

  Follow @CustomerFX on twitter
Follow the best news, tips, and articles
  Subscribe to Customer FX on youtube
Watch SalesLogix tutorial videos from Customer FX
Login / Register