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