ALTER ALERT

Snowflake logo in black (no text) Preview Feature — Open

Available to all accounts.

Modifies the properties of an existing alert and suspends or resumes an existing alert.

See also:

CREATE ALERT , DESCRIBE ALERT, DROP ALERT , SHOW ALERTS

Syntax

ALTER ALERT [ IF EXISTS ] <name> { RESUME | SUSPEND };

ALTER ALERT [ IF EXISTS ] <name> SET
  [ WAREHOUSE = <string> ]
  [ SCHEDULE = '{ <number> MINUTE | USING CRON <expr> <time_zone> }' ]
  [ COMMENT = <string> ]

ALTER ALERT [ IF EXISTS ] <name> UNSET
  [ WAREHOUSE ]
  [ SCHEDULE ]
  [ COMMENT ]
  [ , ... ]

ALTER ALERT [ IF EXISTS ] <name> MODIFY CONDITION EXISTS (<condition>)

ALTER ALERT [ IF EXISTS ] <name> MODIFY ACTION <action>
Copy

Parameters

name

Identifier for the alert to alter. If the identifier contains spaces or special characters, the entire string must be enclosed in double quotes. Identifiers enclosed in double quotes are also case-sensitive.

RESUME | SUSPEND

Specifies the action to perform on the alert:

  • RESUME makes a suspended alert active.

  • SUSPEND puts the alert into a “Suspended” state.

If the alert schedule is set to an interval (i.e. num MINUTE), then to avoid ambiguity, the base interval time for the schedule is reset to the current time when the alert is resumed.

The base interval time starts the interval counter from the current clock time. For example, if an alert is created with 10 MINUTE and the alert is resumed at 9:03 AM, then the alert runs at 9:13 AM, 9:23 AM, and so on. Note that we make a best effort to ensure absolute precision, but only guarantee that alerts do not execute before their set interval occurs (e.g., in the current example, the alert could first run at 9:14 AM, but will definitely not run at 9:12 AM).

SET ...

Specifies one (or more) properties to set for the alert (separated by blank spaces, commas, or new lines). For more details about the properties you can set, see CREATE ALERT.

UNSET ...

Specifies one (or more) properties and/or session parameters to unset for the alert, which resets them to the defaults.

You can reset multiple properties/parameters with a single ALTER statement; however, each property/parameter must be separated by a comma. When resetting a property/parameter, specify only the name; specifying a value for the property/parameter will return an error.

MODIFY CONDITION EXISTS (condition)

Specifies the SQL statement that should represent the condition for the alert. You can use the following commands:

If the statement returns one or more rows, the action for the alert is executed.

MODIFY ACTION action

Specifies the SQL statement that should be executed if the condition returns one or more rows.

To send an email notification, you can call the SYSTEM$SEND_EMAIL() stored procedure.

Access Control Requirements

Executing this SQL command requires roles with the following privileges at a minimum:

  • To resume an alert:

    • The role with the OWNERSHIP privilege on the alert must also have the global EXECUTE ALERT privilege.

    • The role executing ALTER ALERT must have either the OPERATE or OWNERSHIP privilege on the alert.

  • To suspend an alert, the role executing ALTER ALERT must have either the OPERATOR or OWNERSHIP privilege on the alert.

  • To modify the properties of the alert, the role executing ALTER ALERT must have the OWNERSHIP privilege on the alert.

Note that operating on any object in a schema also requires the USAGE privilege on the parent database and schema.

For instructions on creating a custom role with a specified set of privileges, see Creating Custom Roles.

For general information about roles and privilege grants for performing SQL actions on securable objects, see Overview of Access Control.

Usage Notes

  • When an alert is resumed, Snowflake verifies that the role with the OWNERSHIP privilege on the alert also has the USAGE privilege on the warehouse assigned to the alert, as well as the global EXECUTE ALERT privilege; if not, an error is produced.

  • Only account administrators (users with the ACCOUNTADMIN role) can grant the EXECUTE ALERT privilege to a role. For ease of use, we recommend creating a custom role (e.g. alert_admin) and assigning the EXECUTE ALERT privilege to this role. Any role that can grant privileges (e.g. SECURITYADMIN or any role with the MANAGE GRANTS privilege) can then grant this custom role to any alert owner role to allow altering their own alerts. For instructions for creating custom roles and role hierarchies, see Configuring Access Control.

Language: English