Create a Trigger

Description

Triggers enable users to define event-based actions. For example, if a table is updated, then run a query to update predictions.

• MongoDB (available for MongoDB Atlas Database),
• Slack,
• Solace,
• PostgreSQL (requires write access). </Info>

Syntax

Here is the syntax for creating a trigger:

CREATE TRIGGER trigger_name
ON integration_name.table_name
[COLUMNS column_name1, column_name2, ...]
(
    sql_code
) 

By creating a trigger on a data source, every time this data source is updated or new data is inserted, the sql_code provided in the statement will be executed.

You can create a trigger either on a table...

CREATE TRIGGER trigger_name
ON integration_name.table_name
(
    sql_code
) 

...or on one or more columns of a table.

CREATE TRIGGER trigger_name
ON integration_name.table_name
COLUMNS column_name1, column_name2
(
    sql_code
) 

Here is how to list all triggers:

SHOW TRIGGERS;

Example

Firstly, connect Slack to MindsDB following this instruction and connect the Slack app to a channel.

CREATE DATABASE mindsdb_slack
WITH
  ENGINE = 'slack',
  PARAMETERS = {
      "token": "xoxb-...",
      "app_token": "xapp-..."
    };

Create a model that will be used to answer chat questions every time new messages arrive. Here we use the OpenAI engine, but you can use any other LLM.

CREATE MODEL chatbot_model
PREDICT answer
USING
    engine = 'openai_engine',
    prompt_template = 'answer the question: {{text}}';

Here is how to generate answers to Slack messages using the model:

SELECT s.text AS question, m.answer
FROM chatbot_model m
JOIN mindsdb_slack.messages s
WHERE s.channel_id = 'slack-bot-channel-id' 
AND s.user != 'U07J30KPAUF'
AND s.created_at > LAST;

Let's analyze this query:

• We select the question from the Slack connection and the answer generated by the model.
• We join the model with the messages table.
• In the WHERE clause:
  • We provide the channel name where the app/bot is integrated.
  • We exclude the messages sent by the app/bot. You can find the user ID of the app/bot by querying the mindsdb_slack.users table.
  • We use the LAST keyword to ensure that the model generates answers only to the newly sent messages.

Finally, create a trigger that will insert an answer generated by the model every time when new messages are sent to the channel.

CREATE TRIGGER slack_trigger
ON mindsdb_slack.messages
(
    INSERT INTO mindsdb_slack.messages (channel_id, text)
        SELECT 'slack-bot-channel-id' AS channel_id, answer AS text
        FROM chatbot_model m
        JOIN TABLE_DELTA s
        WHERE s.user != 'slack-bot-id' # this is to prevent the bot from replying to its own messages
        AND s.channel_id = 'slack-bot-channel-id'
);

Let's analyze this statement:

• We create a trigger named slack_trigger.
• The trigger is created on the mindsdb_slack.messages table. Therefore, every time when data is added or updated, the trigger will execute its code.
• We provide the code to be executed by the trigger every time the triggering event takes place.
  • We insert an answer generated by the model into the messages table.
  • The TABLE_DELTA stands for the table on which the trigger has been created.
  • We exclude the messages sent by the app/bot. You can find the user ID of the app/bot by querying the mindsdb_slack.users table.