Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Utilize the Event Scheduler in MariaDB Server to automate tasks. Learn how to create, manage, and schedule events to execute SQL statements at specified intervals or times.
Automate database actions with triggers and events in MariaDB Server. Learn to define automatic responses to data modifications and schedule tasks for efficient database management.
Learn how to modify the characteristics of an existing event, such as its schedule, body, or enabled status, without dropping and recreating it.
Modifies one or more characteristics of an existing event.
The ALTER EVENT statement is used to change one or more of the characteristics of an existing without the need to drop and recreate it. The syntax for each of the DEFINER, ON SCHEDULE, ON COMPLETION,COMMENT, ENABLE / DISABLE, and DO clauses is exactly the same as when used with .
This statement requires the privilege. When a user executes a successful ALTER EVENT statement, that user becomes the definer for the affected event.
(In MySQL 5.1.11 and earlier, an event could be altered only by its definer, or by a user having the privilege.)
ALTER EVENT works only with an existing event:
This page is licensed: GPLv2, originally from
ALTER
[DEFINER = { user | CURRENT_USER }]
EVENT event_name
[ON SCHEDULE schedule]
[ON COMPLETION [NOT] PRESERVE]
[RENAME TO new_event_name]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'comment']
[DO sql_statement]ALTER EVENT no_such_event ON SCHEDULE EVERY '2:3' DAY_HOUR;
ERROR 1539 (HY000): Unknown event 'no_such_event'ALTER EVENT myevent
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 2 HOUR
DO
UPDATE myschema.mytable SET mycol = mycol + 1;Automate actions in MariaDB Server with triggers. Learn how to create and manage triggers that execute automatically before or after data modifications, ensuring data integrity and business logic enfo
Explains how triggers can cause implicit locks on referenced tables during the execution of a statement, potentially affecting concurrency.
A trigger may reference multiple tables, and if a LOCK TABLES statement is used on one of the tables, other tables may at the same time also implicitly be locked due to the trigger.
If the trigger only reads from the other table, that table are read locked. If the trigger writes to the other table, it is write-locked. If a table is read-locked for reading via LOCK TABLES, but needs to be write-locked because it could be modified by a trigger, a write lock is taken.
All locks are acquired together when the LOCK TABLES statement is issued, and released together on UNLOCK TABLES.
Assume table1 contains the following trigger:
Not only is table1 write locked, table2 and table3 are also write locked, due to the possible and , while table4 is read locked due to the .
This page is licensed: CC BY-SA / Gnu FDL
LOCK TABLE table1 WRITECREATE TRIGGER trigger1 AFTER INSERT ON table1 FOR EACH ROW
BEGIN
INSERT INTO table2 VALUES (1);
UPDATE table3 SET writes = writes+1
WHERE id = NEW.id AND EXISTS (SELECT id FROM table4);
END;An introduction to creating and managing scheduled events, which are named database objects containing SQL statements to be executed by the Event Scheduler.
Events are named database objects containing SQL statements that are to be executed at a later stage, either once off, or at regular intervals.
They function very similarly to the Windows Task Scheduler or Unix cron jobs.
Creating, modifying or deleting events requires the EVENT privilege.
Events are created with the CREATE EVENT statement.
Events are only executed if the event scheduler is running. This is determined by the value of the system variable, which needs to be set to On for the event scheduler to be running.
You can check if the Event scheduler is running with:
If the event scheduler is not running and event_scheduler has been set to OFF, use:
to activate it. If event_scheduler has been set to Disabled, you cannot change the value at runtime. Changing the value of the event_scheduler variable requires the SUPER privilege.
Since , setting the system variable will also try to reload the if it was not properly loaded at startup.
A list of current events can be obtained with the statement. This only shows the event name and interval - the full event details, including the SQL, can be seen by querying the , or with .
If an event is currently being executed, it can be seen by querying the , or with the statement.
An event can be changed with the statement.
Events are dropped with the statement. Events are also automatically dropped once they have run for the final time according to their schedule, unless the ON COMPLETION PRESERVE clause has been specified.
This page is licensed: CC BY-SA / Gnu FDL
Define a trigger on a table. This statement creates a routine that automatically executes in response to INSERT, UPDATE, or DELETE events.
This statement creates a new trigger. A trigger is a named database object that is associated with a table, and that activates when a particular event occurs for the table. The trigger becomes associated
with the table named tbl_name, which must refer to a permanent table. You cannot associate a trigger with a TEMPORARY table or a view.
CREATE TRIGGER requires the privilege for the table associated with the trigger.
You can have multiple triggers for the same trigger_time and trigger_event.
For valid identifiers to use as trigger names, see .
If used and the trigger already exists, instead of an error being returned, the existing trigger is dropped and replaced by the newly defined trigger.
The DEFINER clause determines the security context to be used when checking access privileges at trigger activation time. Usage requires the privilege.
If the IF NOT EXISTS clause is used, the trigger is created only if a trigger of the same name does not exist. If the trigger already exists, by default a warning is returned.
trigger_time is the trigger action time. It can be BEFORE or AFTER to indicate that the trigger activates before or after each row to be modified.
Multiple trigger_event events can be specified.
Only one trigger_event can be specified.
trigger_event indicates the kind of statement that activates the trigger. A trigger_event can be one of the following:
INSERT: The trigger is activated whenever a new row is inserted into the table; for example, through , , and statements.
UPDATE: The trigger is activated whenever a row is modified; for example, through statements.
DELETE: The trigger is activated whenever a row is deleted from the table; for example, through and statements. However, DROP TABLE
The FOLLOWS`` other_trigger_name and PRECEDES`` other_trigger_name options support multiple triggers per action time.
FOLLOWS adds the new trigger after another trigger, while PRECEDES adds the new trigger before another trigger. If neither option is used, the new trigger is added last for the given action and time.
FOLLOWS and PRECEDES are not stored in the trigger definition. However, the trigger order is guaranteed to not change over time. and other backup methods do not change trigger order.
You can verify the trigger order from the ACTION_ORDER column in table.
MariaDB supports , and CREATE TRIGGER is atomic.
MariaDB does not support .
OR REPLACE and IF NOT EXISTS:
This page is licensed: GPLv2, originally from
CREATE EVENT test_event
ON SCHEDULE EVERY 1 MINUTE DO
UPDATE test.t1 SET a = a + 1;SHOW PROCESSLIST;
+----+-----------------+-----------+------+---------+------+-----------------------------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+-----------------+-----------+------+---------+------+-----------------------------+------------------+----------+
| 40 | root | localhost | test | Sleep | 4687 | | NULL | 0.000 |
| 41 | root | localhost | test | Query | 0 | init | SHOW PROCESSLIST | 0.000 |
| 42 | event_scheduler | localhost | NULL | Daemon | 30 | Waiting for next activation | NULL | 0.000 |
+----+-----------------+-----------+------+---------+------+-----------------------------+------------------+----------+SET GLOBAL event_scheduler = ON;SHOW EVENTS\G;
*************************** 1. row ***************************
Db: test
Name: test_event
Definer: root@localhost
Time zone: SYSTEM
Type: RECURRING
Execute at: NULL
Interval value: 1
Interval field: MINUTE
Starts: 2013-05-20 13:46:56
Ends: NULL
Status: ENABLED
Originator: 1
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ciSHOW CREATE EVENT test_event\G
*************************** 1. row ***************************
Event: test_event
sql_mode:
time_zone: SYSTEM
Create Event: CREATE DEFINER=`root`@`localhost` EVENT `test_event` ON SCHEDULE EVERY 1 MINUTE STARTS '2013-05-20 13:46:56' ON COMPLETION NOT PRESERVE ENABLE DO UPDATE test.t1 SET a = a + 1
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ciALTER EVENT test_event ON SCHEDULE EVERY '2:3' DAY_HOUR;DROP EVENT test_event;
Query OK, 0 rows affected (0.00 sec)CREATE [OR REPLACE]
[DEFINER = { user | CURRENT_USER | role | CURRENT_ROLE }]
TRIGGER [IF NOT EXISTS]
trigger_name trigger_time {trigger_event [ OR trigger_event] [...]}
ON tbl_name FOR EACH ROW
[{ FOLLOWS | PRECEDES } other_trigger_name ]
trigger_stmt;
trigger time:
BEFORE
| AFTER
trigger_event:
INSERT
| UPDATE [ OF column_name [, colunm_name [, ...]]
| DELETETRUNCATEDELETEDELETESELECT trigger_name, action_order FROM information_schema.triggers
WHERE event_object_table='t1';CREATE DEFINER=`root`@`localhost` TRIGGER increment_animal
AFTER INSERT ON animals FOR EACH ROW
UPDATE animal_count SET animal_count.animals = animal_count.animals+1;CREATE DEFINER=`root`@`localhost` TRIGGER increment_animal
AFTER INSERT ON animals FOR EACH ROW
UPDATE animal_count SET animal_count.animals = animal_count.animals+1;
ERROR 1359 (HY000): Trigger already exists
CREATE OR REPLACE DEFINER=`root`@`localhost` TRIGGER increment_animal
AFTER INSERT ON animals FOR EACH ROW
UPDATE animal_count SET animal_count.animals = animal_count.animals+1;
Query OK, 0 rows affected (0.12 sec)
CREATE DEFINER=`root`@`localhost` TRIGGER IF NOT EXISTS increment_animal
AFTER INSERT ON animals FOR EACH ROW
UPDATE animal_count SET animal_count.animals = animal_count.animals+1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
SHOW WARNINGS;
+-------+------+------------------------+
| Level | Code | Message |
+-------+------+------------------------+
| Note | 1359 | Trigger already exists |
+-------+------+------------------------+
1 row in set (0.00 sec)A comprehensive guide to triggers, explaining their execution timing (BEFORE/AFTER), supported events, and how they interact with storage engines.
A trigger is a set of statements that run when, or are triggered by, an event that occurs on a table.
The event can be an INSERT, an UPDATE, or a DELETE. The trigger can be executed BEFORE or AFTER the event. A table can have multiple triggers defined for the same event and timing combination.
The and statements invoke INSERT triggers for each row that is being inserted.
The statement is executed with the following workflow:
BEFORE INSERT;
BEFORE DELETE (only if a row is being deleted);
AFTER DELETE (only if a row is being deleted);
The statement, when a row already exists, follows the following workflow:
BEFORE INSERT;
BEFORE UPDATE;
AFTER UPDATE.
Otherwise, it works like a normal INSERT statement.
Note that does not activate any triggers.
With non-transactional storage engines, if a BEFORE statement produces an error, the statement isn't executed. Statements that affect multiple rows fail before inserting the current row.
With transactional engines, triggers are executed in the same transaction as the statement that invoked them.
If a warning is issued with the SIGNAL or RESIGNAL statement (that is, an error with an SQLSTATE starting with '01'), it is treated like an error.
Here's a simple example to demonstrate a trigger in action. Using these two tables as an example:
We want to increment a counter each time a new animal is added. Here's what the trigger looks like:
The trigger has:
a name (in this case, increment_animal),
a trigger time (in this case, after the specified trigger event),
a trigger event (an INSERT),
a table with which it is associated (animals
AFTER INSERT specifies that the trigger runs after an INSERT. The trigger could also be set to run before, and the statement causing the trigger could be a DELETE or an UPDATE as well. You can also have multiple triggers for an action. In this case, you can use FOLLOWS | PRECEDES`` other_trigger_name to specify the order of the triggers.
The set of statements to run are the statements on the table of the trigger; therefore, columns/values that change are always just a column name or an expression like NEW.column_name. Table references of other tables must come from explicit table references.
Now, if we insert a record into the animals table, the trigger runs, incrementing the animal_count table.
For more details on the syntax, see .
To drop a trigger, use the statement. Triggers are also dropped if the table with which they are associated is also dropped.
The stores information about triggers.
The statement returns similar information.
The statement returns a CREATE TRIGGER statement that creates the given trigger.
Triggers can consist of multiple statements enclosed by a . If you're entering multiple statements on the command line, temporarily set a new delimiter so that you can use a semicolon to delimit the statements inside your trigger. See for more.
If a trigger contains an error and the engine is transactional, or it is a BEFORE trigger, the trigger will not run, and will prevent the original statement from running as well. If the engine is non-transactional, and it is an AFTER trigger, the trigger will not run, but the original statement will.
Here, we'll drop the above examples and then recreate the trigger with an error, a field that doesn't exist, first using the default , a transactional engine, and then again using , a non-transactional engine.
And now the identical procedure, but with a MyISAM table.
The following example shows how to use a trigger to validate data. The statement is used to intentionally produce an error if the email field is not a valid email. As the example shows, in that case, the new row is not inserted (because it is a BEFORE trigger).
This page is licensed: CC BY-SA / Gnu FDL
A list of restrictions associated with the Event Scheduler, including the inability to return result sets and specific date range limitations.
The following restrictions apply to Events:
All of the restrictions listed in Stored Routine Limitations.
Events cannot return a result set.
Event names are case insensitive, so it's not possible to define two events in the same database if their case insensitive names will match. This restriction has applied since MariaDB/MySQL 5.1.8. If you are upgrading from an older version of MySQL, and have events that could clash, these events need to be renamed before the upgrade.
Events do not support dates beyond the maximum that can be represented in the Unix epoch (2038-01-19).
Events cannot be created, dropped or altered by another stored program, trigger or event.
Events cannot create, drop or alter stored programs or triggers
Event timings cannot be strictly predicted. The intervals MONTH, YEAR_MONTH, QUARTER, and YEAR are all resolved in months. All others are resolved in seconds. A delay of up to two seconds is possible in extreme cases, and events scheduled to run at the same second cannot be executed in a given order. The LAST_EXECUTED column in the table will however always be accurate to within a second.
A new connection is used for each execution of statements within the body of an event, so the session counts for such as Com_delete and Com_select will not reflect these.
Because the Event Scheduler depends on grant tables for its functionality, it is automatically disabled when the server is running with .
This page is licensed: CC BY-SA / Gnu FDL
Understand the constraints of triggers, such as the prohibition of statements that return result sets or explicitly start/commit transactions.
The following restrictions apply to :
All of the restrictions listed in .
All of the restrictions listed in .
Triggers are always executed for each row. MariaDB does not support the standard FOR EACH STATEMENT
AFTER INSERT.a set of statements to run (here, just the one UPDATE statement).
CREATE TABLE animals (id MEDIUMINT(9)
NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (`id`));
CREATE TABLE animal_count (animals INT);
INSERT INTO animal_count (animals) VALUES(0);CREATE TRIGGER increment_animal
AFTER INSERT ON animals
FOR EACH ROW
UPDATE animal_count SET animal_count.animals = animal_count.animals+1;SELECT * FROM animal_count;
+---------+
| animals |
+---------+
| 0 |
+---------+
INSERT INTO animals (name) VALUES('aardvark');
INSERT INTO animals (name) VALUES('baboon');
SELECT * FROM animal_count;
+---------+
| animals |
+---------+
| 2 |
+---------+DROP TRIGGER increment_animal;DROP TABLE animals;
UPDATE animal_count SET animals=0;
CREATE TABLE animals (id MEDIUMINT(9) NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (`id`))
ENGINE=InnoDB;
DELIMITER //
CREATE TRIGGER the_mooses_are_loose
AFTER INSERT ON animals
FOR EACH ROW
BEGIN
IF NEW.name = 'Moose' THEN
UPDATE animal_count SET animal_count.animals = animal_count.animals+100;
ELSE
UPDATE animal_count SET animal_count.animals = animal_count.animals+1;
END IF;
END; //
DELIMITER ;
INSERT INTO animals (name) VALUES('Aardvark');
SELECT * FROM animal_count;
+---------+
| animals |
+---------+
| 1 |
+---------+
INSERT INTO animals (name) VALUES('Moose');
SELECT * FROM animal_count;
+---------+
| animals |
+---------+
| 101 |
+---------+DROP TABLE animals;
CREATE TABLE animals (id MEDIUMINT(9) NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (`id`))
ENGINE=InnoDB;
CREATE TRIGGER increment_animal
AFTER INSERT ON animals
FOR EACH ROW
UPDATE animal_count SET animal_count.id = animal_count_id+1;
INSERT INTO animals (name) VALUES('aardvark');
ERROR 1054 (42S22): Unknown column 'animal_count.id' in 'field list'
SELECT * FROM animals;
Empty set (0.00 sec)DROP TABLE animals;
CREATE TABLE animals (id MEDIUMINT(9) NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (`id`))
ENGINE=MyISAM;
CREATE TRIGGER increment_animal
AFTER INSERT ON animals
FOR EACH ROW
UPDATE animal_count SET animal_count.id = animal_count_id+1;
INSERT INTO animals (name) VALUES('aardvark');
ERROR 1054 (42S22): Unknown column 'animal_count.id' in 'field list'
SELECT * FROM animals;
+----+----------+
| id | name |
+----+----------+
| 1 | aardvark |
+----+----------+CREATE TABLE user (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
first_name CHAR(20),
last_name CHAR(20),
email CHAR(100)
)
ENGINE = MyISAM;
DELIMITER //
CREATE TRIGGER bi_user
BEFORE INSERT ON user
FOR EACH ROW
BEGIN
IF NEW.email NOT LIKE '_%@_%.__%' THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Email field IS NOT valid';
END IF;
END; //
DELIMITER ;
INSERT INTO user (first_name, last_name, email) VALUES ('John', 'Doe', 'john_doe.example.net');
ERROR 1644 (45000): Email field is not valid
SELECT * FROM user;
Empty set (0.00 sec)Triggers cannot operate on any tables in the mysql, information_schema or performance_schema database.
Cannot return a result set.
Triggers are not activated by foreign key actions.
If a trigger is loaded into cache, it is not automatically reloaded when the table metadata changes. In this case a trigger can operate using the outdated metadata.
By default, with row-based replication, triggers run on the master, and the effects of their executions are replicated to the slaves. It is possible to run triggers on slaves. See Running triggers on the slave for Row-based events.
This page is licensed: CC BY-SA / Gnu FDL