Question #2 from the September MySQL User Group was whether or not a TRIGGER can affect a different table. Apparently the documentation (perhaps for an earlier version??) specified this was not possible. Tom Hanlon, MySQL employee, put up this example (modified from the original, special thanks to Ralph Navarro for copying it down):
Basically, this trigger will insert the current user and timestamp into another table.
mysql> delimiter $$
mysql> CREATE TRIGGER BIcity BEFORE INSERT ON city
-> FOR EACH ROW BEGIN
-> INSERT INTO citytest (name,happened) values (current_user(),now());
-> END;
-> $$
Query OK, 0 rows affected (0.03 sec)
mysql> delimiter ;
mysql> create table citytest (
name varchar(60) not null default '',
happened datetime not null);
Query OK, 0 rows affected (0.19 sec)
mysql> describe city;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| CountryCode | char(3) | NO | | | |
| District | char(20) | NO | | | |
| Population | int(11) | NO | | 0 | |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
mysql> insert into city (Name) values ('tomtown');
Query OK, 1 row affected (0.02 sec)
mysql> select * from citytest\G
*************************** 1. row ***************************
name: root@localhost
happened: 2006-09-07 21:45:14
1 row in set (0.01 sec)
interesting
interesting article.
------------------
http://301url.com/mortgage - all about mortgages
Yes, you can. Note in this
Yes, you can. Note in this example, citytest is the same as the table city (except city's id is auto_increment and a primary key, and doesn't contain the timestamp), so if you don't have that table in your test database you can create it using the same definition as citytest.
mysql> insert into city (ID,name,CountryCode,District,Population) VALUES (1,'one','cc','district',12345);
mysql> delimiter $$
mysql> CREATE TRIGGER BUcity BEFORE UPDATE ON city
FOR EACH ROW BEGIN
INSERT INTO citytest (ID,name,tstamp) VALUES
(OLD.ID,OLD.name,NOW());
INSERT INTO citytest (ID,name,tstamp) VALUES
(NEW.ID,NEW.name,NOW());
END;
$$
Query OK, 0 rows affected (0.03 sec)
mysql> delimiter ;
mysql> create table citytest (
ID int not null,
name char(35) not null default '',
CountryCode char(3) not null default '',
District char(20) not null default '',
Population int not null default 0,
tstamp timestamp);
Query OK, 0 rows affected (0.19 sec)
mysql> describe city;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| CountryCode | char(3) | NO | | | |
| District | char(20) | NO | | | |
| Population | int(11) | NO | | 0 | |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
mysql> UPDATE city SET name='foo' WHERE name='one';
Query OK, 1 row affected (0.02 sec)
mysql> select * from citytest\G
select * from citytest\G
*************************** 1. row ***************************
ID: 1
name: one
CountryCode:
District:
Population: 0
tstamp: 2006-09-11 15:45:46
*************************** 2. row ***************************
ID: 1
name: foo
CountryCode:
District:
Population: 0
tstamp: 2006-09-11 15:45:46
2 rows in set (0.00 sec)
And now you have 2 rows with the old and new data. OLD and NEW are special table names inside the trigger, and they contain each row *modified* (not each row looked at).
Also note that this is technically effective as a "history" table, but it means each update takes at least twice as long (1 insert + 1 update, PLUS the overhead of the trigger itself), and is not a proper audit, as the DBA has control over the database and can change the data.
Yeah, the initial
Yeah, the initial documentation noted that insert into other tables was not possible (iirc). The one thing the above example leaves me wondering is if you can insert the new or old values of the data being modified into the other table?