Triggers in MySQL Source

May 8, 2007 Markdown source

1---
2title: 'Triggers in MySQL'
3date: '2007-05-08'
4published_at: '2007-05-08T12:03:00.000+10:00'
5tags: ['databases', 'mysql', 'programming']
6author: 'Gavin Jackson'
7excerpt: 'Starting from Mysql 5.0.2, mysql now supports triggers - which can be quite handy. I''m using them to automatically create new (derived) table records when a user submits data to my web app. Syntax is...'
8updated_at: '2007-05-08T13:50:33.700+10:00'
9legacy_url: 'http://www.gavinj.net/2007/05/triggers-in-mysql.html'
10---
11
12Starting from Mysql 5.0.2, mysql now supports triggers - which can be quite handy. I'm using them to automatically create new (derived) table records when a user submits data to my web app. Syntax is very clean (as follows) [http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html](http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html):
13
14```
15CREATE TABLE test1(a1 INT);
16CREATE TABLE test2(a2 INT);
17CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
18CREATE TABLE test4(
19a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
20b4 INT DEFAULT 0
21);
22
23DELIMITER |
24
25CREATE TRIGGER testref BEFORE INSERT ON test1
26FOR EACH ROW BEGIN
27INSERT INTO test2 SET a2 = NEW.a1;
28DELETE FROM test3 WHERE a3 = NEW.a1;
29UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
30END;
31|
32
33DELIMITER ;
34```
35
36[http://www.advogato.org/article/858.html](http://www.advogato.org/article/858.html)
37
38On an unrelated note, I just read the other day that Oracle has acquired the company that wrote the InnoDB Mysql engine (which is the real one that enforces foreign keys and transactions etc).
39
40[ http://developers.slashdot.org/article.pl?sid=07/04/01/1448207&from;=rss](http://developers.slashdot.org/article.pl?sid=07/04/01/1448207&from=rss) On another unrelated note, you can now use Postgresql as a MySQL database engine - how cool is that?
41
42
43

---
title: 'Triggers in MySQL'
date: '2007-05-08'
published_at: '2007-05-08T12:03:00.000+10:00'
tags: ['databases', 'mysql', 'programming']
author: 'Gavin Jackson'
excerpt: 'Starting from Mysql 5.0.2, mysql now supports triggers - which can be quite handy. I''m using them to automatically create new (derived) table records when a user submits data to my web app. Syntax is...'
updated_at: '2007-05-08T13:50:33.700+10:00'
legacy_url: 'http://www.gavinj.net/2007/05/triggers-in-mysql.html'
---

Starting from Mysql 5.0.2, mysql now supports triggers - which can be quite handy. I'm using them to automatically create new (derived) table records when a user submits data to my web app. Syntax is very clean (as follows) [http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html](http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html):

```
CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
b4 INT DEFAULT 0
);

DELIMITER |

CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW BEGIN
INSERT INTO test2 SET a2 = NEW.a1;
DELETE FROM test3 WHERE a3 = NEW.a1;
UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
END;
|

DELIMITER ;
```

[http://www.advogato.org/article/858.html](http://www.advogato.org/article/858.html)

On an unrelated note, I just read the other day that Oracle has acquired the company that wrote the InnoDB Mysql engine (which is the real one that enforces foreign keys and transactions etc).

[ http://developers.slashdot.org/article.pl?sid=07/04/01/1448207&from;=rss](http://developers.slashdot.org/article.pl?sid=07/04/01/1448207&from=rss) On another unrelated note, you can now use Postgresql as a MySQL database engine - how cool is that?