Try Opteryx

DROP TRIGGER

The DROP TRIGGER statement removes one refresh trigger from a table. The materialized view the trigger served stays queryable, but commits to that table no longer refresh it — To stop a view refreshing, prefer ALTER MATERIALIZED VIEW ... SUSPEND: a dropped trigger is indistinguishable from one that was never created or that broke, whereas a suspended view records that it was switched off deliberately, when, and by whom.

Syntax

DROP TRIGGER [ IF EXISTS ] <trigger_name> ON <table_name>;

Parameters

  • <trigger_name> — auto-generated as refresh__<collection>__<view_name>__<suffix> when a materialized view is created. Use SHOW TRIGGERS FOR to list the triggers on a table.
  • <table_name> — the table the trigger is attached to, fully qualified as <workspace>.<collection>.<table_name>.
  • IF EXISTS — skip the operation without error if the trigger does not exist, instead of refusing the statement.

Examples

Drop a Refresh Trigger

DROP TRIGGER refresh__analytics__daily_totals ON my_workspace.sales.orders;

Drop Only If It Exists

DROP TRIGGER IF EXISTS refresh__analytics__daily_totals ON my_workspace.sales.orders;

Notes

  • Requires the writer role on the table the trigger is attached to — removing a trigger is an update to that table.
  • After the drop, the materialized view goes stale silently as its source changes. To resume refreshing, re-create the view with CREATE OR REPLACE MATERIALIZED VIEW, which rebuilds its triggers.
  • CASCADE and RESTRICT are not supported and are rejected when the query is planned.
  • There is no CREATE TRIGGER statement — triggers only come into existence through CREATE MATERIALIZED VIEW.
  • Removing a materialized view entirely, triggers and all, is DROP MATERIALIZED VIEW.

See Also