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.
Copy
```
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.

Copy

(... 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.

Copy

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.
Copy
```
(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')))
```