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
   Saleslogix (now Infor CRM) Web Client- Build Error Based on Quick Form Name
 I recently discovered a problem that will occur if you happen to name a quick form the same name as
Posted on Oct 31, 2014 by Kris Halsrud to Kris Halsrud's Blog
 
   Unicode Character Sets for SalesLogix Web 8.1
If you have the need to use unicode character sets so international users can view the web client in thei
Posted on Oct 29, 2014 by Mark Duthoy to SalesLogix Support
 
   Change the Appearance of a Saleslogix (Now Infor CRM) Read Only TextBox Web Control
In the Salelogix web client, a read only control has a css style applied to it to make the background app
Posted on Oct 24, 2014 by Kris Halsrud to Kris Halsrud's Blog
 
   Saleslogix (now Infor CRM) Web Client- Changing Label Appearances on Quick Forms
 I wrote previously about setting a label's appearance on a quick form using server side code.&n
Posted on Oct 02, 2014 by Kris Halsrud to Kris Halsrud's Blog
 
   Error Attempting to Save an Entity: "E_FAIL(0x80004005)" in the Infor CRM (Saleslogix) Web Client
We recently had a client that was running into an issue when attempting to create one particular entity i
Posted on Sep 30, 2014 by Kris Halsrud to Kris Halsrud's 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
 

apple watches said:

Using SQL triggers to enforce data rules in SalesLogix. - Jason Buss' Blog

October 8, 2014 5:29 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