LogoLogo
DoltHubBlogDiscordGitHubDolt
  • Introduction
    • What Is Dolt?
    • Installation
      • Linux
      • Windows
      • Mac
      • Build from Source
      • Application Server
      • Docker
      • Upgrading
    • Getting Started
      • Version Controlled Database
      • Git For Data
      • Versioned MySQL Replica
    • Use Cases
      • Data Sharing
      • Data and Model Quality Control
      • Manual Data Curation
      • Version Control for your Application
      • Versioned MySQL Replica
      • Audit
      • Configuration Management
      • Offline First
  • Concepts
    • Dolt
      • Git
        • Commits
        • Log
        • Diff
        • Branch
        • Merge
        • Conflicts
        • Remotes
        • Working Set
      • SQL
        • Databases
        • Schema
        • Tables
        • Primary Keys
        • Types
        • Indexes
        • Views
        • Constraints
        • Triggers
        • Procedures
        • Users/Grants
        • Transactions
        • System Variables
      • RDBMS
        • Server
        • Backups
        • Replication
    • DoltHub/DoltLab
      • Permissions
      • Pull Requests
      • Issues
      • Forks
  • SQL Reference
    • Running the Server
      • Configuration
      • Access Management
      • Branch Permissions
      • Backups
      • Garbage Collection
      • Metrics
      • Replication
      • Troubleshooting
    • Version Control Features
      • Using Branches
      • Merges
      • Querying History
      • Using Remotes
      • Procedures
      • Functions
      • System Tables
      • System Variables
      • Saved Queries
    • SQL Language Support
      • Data Description
      • Expressions, Functions, Operators
      • Supported Statements
      • MySQL Information Schema
      • Collations and Character Sets
      • System Variables
      • Miscellaneous
    • Supported Clients
      • Programmatic
      • SQL Editors
    • Benchmarks and Metrics
      • Correctness
      • Latency
      • Import
  • CLI Reference
    • Commands
    • Git Comparison
  • Architecture
    • Overview
    • Storage Engine
      • Commit Graph
      • Prolly Trees
      • Block Store
    • SQL
      • Go MySQL Server
      • Vitess
  • Guides
    • Cheat Sheet
    • Contributing
      • dolt
      • go-mysql-server
    • MySQL to Dolt Replication
    • Importing Data
    • Integrations
  • Other
    • FAQ
    • Roadmap
    • Versioning
  • Products
    • Hosted Dolt
      • Getting Started
      • Notable Features
      • SQL Workbench
      • Cloning a Hosted Database
      • Using DoltHub as a Remote
      • Infrastructure
    • DoltHub
      • Data Sharing
      • API
        • Authentication
        • SQL
        • CSV
        • Database
        • Hooks
      • Continuous Integration
        • Getting Started
        • Workflow Reference
      • Transform File Uploads
      • Workspaces
    • DoltLab
    • Dolt Workbench
    • DoltgreSQL
Powered by GitBook
On this page
  • What is a Trigger?
  • How to use Triggers
  • Difference between MySQL Triggers and Dolt Triggers
  • Interaction with Dolt Version Control
  • Example
  • dolt_schemas table

Was this helpful?

Edit on GitHub
Export as PDF
  1. Concepts
  2. Dolt
  3. SQL

Triggers

PreviousConstraintsNextProcedures

Last updated 1 year ago

Was this helpful?

What is a Trigger?

Triggers are SQL statements you can set to run every time a row is inserted, updated, or deleted from a particular table. Triggers receive the value of the row being inserted, updated, or deleted like a parameter, and can change it in some cases.

Database users create triggers. Triggers are schema. Triggers are stored along with other schema elements in the database.

How to use Triggers

Triggers are a general tool, but they are most commonly used to enforce complex constraints that can't be expressed by foreign keys, nullness, types, or the check syntax.

Difference between MySQL Triggers and Dolt Triggers

Dolt triggers match MySQL triggers exactly.

Interaction with Dolt Version Control

Triggers are versioned in the dolt_schemas table just like . You add and commit that table just like any other changed table after you create or modify a trigger.

Example

mysql> create table a (x int primary key);
mysql> create table b (y int primary key);
mysql> create trigger adds_one before insert on a for each row set new.x = new.x + 1;
mysql> insert into a values (1), (3);
mysql> select * from a;
+---+
| x |
+---+
| 2 |
| 4 |
+---+
mysql> create trigger inserts_into_b after insert on a for each row insert into b values (new.x * 2);
mysql> insert into a values (5);
mysql> select * from a;
+---+
| x |
+---+
| 2 |
| 4 |
| 6 |
+---+
mysql> select * from b;
+----+
| y  |
+----+
| 12 |
+----+

dolt_schemas table

mysql> select * from dolt_status;
+--------------+--------+-----------+
| table_name   | staged | status    |
+--------------+--------+-----------+
| dolt_schemas | 0      | new table |
| a            | 0      | new table |
| b            | 0      | new table |
+--------------+--------+-----------+
mysql> select * from dolt_schemas;
+---------+----------------+-----------------------------------------------------------------------------------------------+----+--------------------------------+
| type    | name           | fragment                                                                                      | id | extra                          |
+---------+----------------+-----------------------------------------------------------------------------------------------+----+--------------------------------+
| trigger | adds_one       | create trigger adds_one before insert on a for each row set new.x = new.x + 1                 | 1  | {"CreatedAt": 1.656093714e+09} |
| trigger | inserts_into_b | create trigger inserts_into_b after insert on a for each row insert into b values (new.x * 2) | 2  | {"CreatedAt": 1.656093749e+09} |
+---------+----------------+-----------------------------------------------------------------------------------------------+----+--------------------------------+
views