Triggers

This is a translation reference to convert Oracle Triggers to snowflake.

Triggers are not supported by Snowflake, and then they will not be migrated automatically.

Snowflake at this moment does not provide a direct mechanism for triggers, but some Snowflake features can be used to achieve similar results.

We recommend that you perform an analysis of your triggers, and classify them by purpose:

• Audit Triggers: the intention of these triggers is to capture information and record the changes done on some tables into other tables.

• Initialization Triggers: the intention of these triggers is to add some default values to the new records. They are usually before or after insert triggers

• Business Rule Barrier Triggers: these usually apply for BEFORE/AFTER DELETE or UPDATE. These triggers are meant to create a barrier to avoid data entry or deletion that will break some business rules.

• Instead of Triggers: used for example to allow inserts on views are not supported. The recommendation will be to turn that logic into a stored procedure and introduce calls whenever they were used for insert/delete/update operations.

• Database Triggers: cannot be replicated, it is also recommended to encapsulate this logic into a stored procedure. But this logic will need to be manually invoked.

• Generic After Triggers: for some after triggers, streams, and tasks can be leveraged see section below.

Audit Trigger

CREATE OR REPLACE TRIGGER SCHEMA.TRIGGER_NAME
BEFORE UPDATE OR INSERT ON SCHEMA.TRIGGER_NAME FOR EACH ROW  
BEGIN 
:NEW.LAST_UPDATE := SYSDATE; 
END;

Before UPDATE triggers for audit cases like this cannot be handled directly. For the INSERT case you can use the default value case explained for the initialization trigger. However for the update case the only option will be to use a task as it is explained later for AFTER triggers. However the LAST_UPDATE will not be accurate, there will be an offset because the recorded modification will be at the time of task execution (for example if the tasks executes each 5min then the LAST_UPDATE will be recorded 5min later).

For UPDATE cases trying to capture the CURRENT_USER is not possible.

Other cases of AUDIT triggers are when they register changes of a table into an update table. Using the AFTER trigger technique describe later can be used but again USER information cannot be tracked and TIME information will not be accurate.

Initialization Trigger

CREATE OR REPLACE TRIGGER SCHEMA.TRIGGER_NAME
BEFORE INSERT ON SCHEMA.TABLE1 FOR EACH ROW
BEGIN
   SELECT SCHEMA.TABLE.NEXTVAL INTO :NEW.COLUMN_SEQ FROM DUAL;
   SELECT USER INTO :NEW.UPDATED_BY FROM DUAL;
   SELECT SYSTIMESTAMP INTO :NEW.UPDATED_TM FROM DUAL;
END

For these triggers, you might use Snowflake Default column values for example for sequence values.

You can also use CURRENT_USER() and CURRENT_TIMESTAMP instead of USER or SYS_TIMESTAMP

This only applies for BEFORE INSERT or AFTER INSERT cases.

Business Rule Barrier

CREATE OR REPLACE EDITIONABLE TRIGGER SCHEMA.TRIGGER_NAME
BEFORE DELETE ON SCHEMA.TABLE FOR EACH ROW
BEGIN
   IF (:OLD.termination_date is NULL OR 
   :OLD.termination_date >= TRUNC(SYSDATE)+1 ) THEN
     RAISE_APPLICATION_ERROR(-30001,'An employee must be terminated before deleteing the row');
 END IF; 

For these cases you will need to in-line the trigger actions after/before the DELETE or UPDATE is performed.

A tasks is not recommended here because task are run on an schedule, and then the row will already be modified.

This section shows a known workaround for partially implementing AFTER Triggers.

GENERIC AFTER TRIGGER

Example 1: Basic Trigger conversion

Oracle

CREATE TRIGGER example_trigger
AFTER INSERT ON table1
SELECT * FROM DUAL;

Snowflake

SnowConvert helpers Code removed from the example. You can find them here.

-- Use streams to track changes to the table.
CREATE OR REPLACE STREAM example_trigger_STREAM ON TABLE TABLE_ONE;

-- Use procedures to execute the trigger statements
CREATE OR REPLACE PROCEDURE example_trigger_PROCEDURE() 
RETURNS STRING
LANGUAGE JAVASCRIPT
AS $$
  // REGION SnowConvert Helpers Code
  EXEC(`SELECT * FROM DUAL;`);
  // The stream is recreated as a way to reset it, also using a DML on the STREAM will reset it
  EXEC(`CREATE OR REPLACE STREAM example_trigger_STREAM ON TABLE TABLE_ONE`);
  return '';
$$;

-- Use the task to verify table changes, and execute the procedure accordingly.
CREATE OR REPLACE TASK example_trigger_TASK
WAREHOUSE = 'DEMO_WH'
SCHEDULE = '1 minute'
WHEN SYSTEM$STREAM_HAS_DATA('example_trigger_STREAM')
AS CALL example_trigger_PROCEDURE();
ALTER TASK example_trigger_TASK RESUME;

In-depth explanation for the snowflake code

Streams

These take care of storing the changes made to the table. Please note:

• These will store the delta between the current table state, and the last offset stored by the stream itself. Please take this into account for billing purposes.

• Notice that these do not store the information of updates, but rather store them as an insertion.

• In the same manner, they cannot be configured to track only deletions or only updates, and thus they should have to be filtered in the procedure and the task itself (see below).

Procedures

These take care of running the trigger's SQL statement(s). Please note:

• There is a need to flush the stream, hence the new stream creation at the end of the procedure.

• Any actions that need to be filtered (like AFTER-INSERTs-only triggers) will need to be filtered in the stored procedure itself.

Tasks

These take care of regularly verifying for stream changes and accordingly execute the trigger's SQL statement(s). Please note:

• The Tasks work on a schedule, an action does not trigger them. This means that there will be trigger scheduled checks with no data changes performed in the table.

• Tasks cannot be configured to run more than once every sixty (60) seconds, as the minimum time is one (1) minute.

• Once the stream has detected changes there will be, in the worst-case scenario, sixty (60) seconds of delay between the change detection and the trigger execution.

• While adding the WHEN avoids Task execution, snowflake still adds Charge every time it is evaluated; and said Charge will be added to the bill when the trigger actually executes.

• The Task needs a Warehouse to be executed in and will need to be manually set by the client.