Trigger clause examples

The following are examples of SQL that you can enter in Trigger SQL Clause.

• In the SQL below, #L indicates that notifications are used only if the user is DolanW.
  CmLi_Comm_NotifyTime<#T AND
  cmli_comm_userid=#U AND
  Comm_Status='Pending' AND #L = 'DolanW'
• In the SQL below, #C indicates that the user is notified if team is their current team. It also checks that team is null, as Team may not be a mandatory field.
  CmLi_Comm_NotifyTime<#T
  AND cmli_comm_userid=#U
  AND Comm_Status='Pending'
  AND Comm_ChannelID = #C OR Comm_ChannelID IS NULL)
• The SQL below specifies that the escalation rule runs only between 7am and 6pm, and does not run on weekends.
  (... existing SQL triggering clause) AND datepart(hour, current_timestamp)<'18'
  AND datepart(hour, current_timestamp)>'7'
  AND DATENAME(WEEKDAY, GETDATE()) <> 'Saturday'
  AND DATENAME(WEEKDAY, GETDATE()) <> 'Sunday'
• The SQL below notifies a user if a record has been stuck at a certain stage for 14 days.
  DATEDIFF(day,case_updateddate, getdate())>14
  AND (case_stage=<1> OR case_stage=<2>) AND case_assigneduserid=#U
• The SQL below is taken from the Email Reminder escalation rule. It uses a stop clause (CmLi_SMSMessageSent) to prevent the rule firing repeatedly. The email message is sent only when the field is null. When the email is sent, the field is set to Y which prevents the rule firing again.
  (CmLi_Comm_NotifyTime<#T)
  AND(UPPER(RTRIM(Comm_Status))=UPPER(RTRIM(N'Pending')))
  AND(CmLi_SMSMessageSent IS NULL)
  AND(UPPER(RTRIM(Comm_SMSNotification))=UPPER(RTRIM(N'Y')))