martyna-mindsdb
128 lines
3.9 KiB
Plaintext
128 lines
3.9 KiB
Plaintext
---
|
|
title: Create a Trigger
|
|
sidebarTitle: 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.
|
|
|
|
<Info>
|
|
Currently, you can create triggers on the following data sources:
|
|
|
|
- [MongoDB](/integrations/data-integrations/mongodb) (available for MongoDB Atlas Database),
|
|
- [Slack](/integrations/app-integrations/slack),
|
|
- [Solace](https://github.com/mindsdb/mindsdb/tree/main/mindsdb/integrations/handlers/solace_handler),
|
|
- [PostgreSQL](/integrations/data-integrations/postgresql) (requires write access).
|
|
</Info>
|
|
|
|
## Syntax
|
|
|
|
Here is the syntax for creating a trigger:
|
|
|
|
```sql
|
|
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...
|
|
|
|
```sql
|
|
CREATE TRIGGER trigger_name
|
|
ON integration_name.table_name
|
|
(
|
|
sql_code
|
|
)
|
|
```
|
|
|
|
...or on one or more columns of a table.
|
|
|
|
```sql
|
|
CREATE TRIGGER trigger_name
|
|
ON integration_name.table_name
|
|
COLUMNS column_name1, column_name2
|
|
(
|
|
sql_code
|
|
)
|
|
```
|
|
|
|
Here is how to list all triggers:
|
|
|
|
```sql
|
|
SHOW TRIGGERS;
|
|
```
|
|
|
|
## Example
|
|
|
|
Firstly, connect Slack to MindsDB following [this instruction](/integrations/app-integrations/slack#set-up-a-slack-app-and-generate-tokens) and connect the Slack app to a channel.
|
|
|
|
```sql
|
|
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](/integrations/ai-engines/openai), but you can use any [other LLM](/integrations/ai-overview#large-language-models).
|
|
|
|
```sql
|
|
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:
|
|
|
|
```sql
|
|
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.
|
|
|
|
```sql
|
|
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.
|