Paul McArdle wrote:
> not lifted, 5..0.7-beta gives same error as above.
This is not true. You can now reference tables. You must be explicit, however.
You need to explicitly inform MySQL what you wish to do. Let's assume you have an AFTER UPDATE trigger on a table called timecards, which references a table employees. I created a simple script called create_emps_timecards.sql:
create_emps_timecards.sql
---------------------------------
USE test
DROP TABLE IF EXISTS timecards;
DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
emp_id INT NOT NULL
, name VARCHAR(30) NOT NULL
, total_hours_worked INT NOT NULL
, PRIMARY KEY (emp_id)
) ENGINE=INNODB;
CREATE TABLE timecards (
emp_id INT NOT NULL
, date_in DATE NOT NULL
, num_hours INT NOT NULL
, PRIMARY KEY (emp_id, date_in)
, FOREIGN KEY (emp_id) REFERENCES employees (emp_id)
) ENGINE=INNODB;
INSERT INTO employees VALUES (1, 'Hank Aaron', 20);
INSERT INTO timecards VALUES (1, '2005-06-27', 20);
---------------------------------
I then created a trigger on the timecards table which updated the employees table. I called this ai_timecards.sql:
ai_timecards.sql
---------------------------------
USE test
\g
DELIMITER //
CREATE TRIGGER ai_timecards
AFTER INSERT ON timecards FOR EACH ROW
BEGIN
UPDATE employees SET total_hours_worked = total_hours_worked + NEW.num_hours WHERE emp_id = NEW.emp_id;
END
//
\g
---------------------------------
I then ran a test case against the test database. I saved the test script as test.sql:
test.sql:
---------------------------------
USE test;
SET @@AUTOCOMMIT = 0;
BEGIN;
LOCK TABLES employees WRITE, timecards WRITE, mysql.proc READ;
INSERT INTO timecards VALUES (1, '2005-06-28', 8);
## We should hopefully now have 28 hours in our main employees table
SHOW WARNINGS;
SELECT * FROM employees;
ROLLBACK;
UNLOCK TABLES;
SELECT * FROM employees;
---------------------------------
The important thing to note is that I am EXPLICITLY telling MySQL which tables to lock, which helps it perform the conflict resolution for triggers. Notice also, that I've used InnoDB tables to demonstrate in this test that a ROLLBACK is transaction safe for triggers on transaction-safe tables.
Here is the output of the test case:
output.txt
---------------------------------
[root@localhost ~]# mysql < create_emps_timecards.sql
[root@localhost ~]# mysql < ai_timecards.sql
[root@localhost ~]# mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 5.0.7-beta-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> SOURCE test.sql
Database changed
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.05 sec)
Query OK, 1 row affected (0.00 sec)
Empty set (0.00 sec)
+--------+------------+--------------------+
| emp_id | name | total_hours_worked |
+--------+------------+--------------------+
| 1 | Hank Aaron | 28 |
+--------+------------+--------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
+--------+------------+--------------------+
| emp_id | name | total_hours_worked |
+--------+------------+--------------------+
| 1 | Hank Aaron | 20 |
+--------+------------+--------------------+
1 row in set (0.00 sec)
mysql>
---------------------------------
As you can see, the trigger correctly updated the employees.total_hours_worked table with the necessary change (and there were no warnings issued by MySQL). Also notice that when a ROLLBACK is issued, then the trigger's changes are undone.
Hope this clears things up,
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Got Cluster?
http://www.mysql.com/cluster
Personal:
http://jpipes.com