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- 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
 
   Video: Demystifying Infor CRM (Saleslogix) Updates
Watch it now! If you missed yesterdays demo, "Demystifying Infor CRM (Saleslogix) Updates" ,
Posted on Sep 26, 2014 by Brianna Ojard to The Inbox
 
   Official Infor CRM Acquisition FAQ
What to expect now that Saleslogix is Infor CRM. Like many of you, it will take some time (probably a
Posted on Sep 18, 2014 by Brianna Ojard to The Inbox
 
   Problem with the Clean Build Folders option in Application Architect
On occasion, it becomes necessary to clean out the build folders and deployed website prior to a build/de
Posted on Sep 16, 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
 

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