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
   Confirming Actions With an "Are you sure?" Prompt in Infor CRM (Saleslogix)
I'm not a huge fan of bugging end users. I figure, if an end user clicked something, he or she meant
Posted on Nov 18, 2014 by Ryan Farley to Ryan Farley's Blog
 
   Manipulating the DependencyLookup Control in the Infor CRM Web Client (Formerly Saleslogix)
 The Dependency Lookup control in the Saleslogix web client is what is used for the hierarchical Are
Posted on Nov 14, 2014 by Kris Halsrud to Kris Halsrud's Blog
 
   Infor CRM (formerly Saleslogix) Statement of Direction
Learn more about the future of Infor CRM. Recently I received my login information for the Infor Partn
Posted on Nov 13, 2014 by Brianna Ojard to The Inbox
 
   Adding a report link to the Common Tasks pane in Infor CRM (formerly Saleslogix)
When you are viewing an Entity Detail page, the Common Tasks pane on the right of the page provides a nu
Posted on Nov 12, 2014 by Jason Buss to Jason Buss' Blog
 
   How to Sum a Collection using Hibernate Criteria and Projections
 To sum the results of a criteria collection you can use code such as the following, which gets the
Posted on Nov 07, 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