Skip to main content
replaced http://stackoverflow.com/ with https://stackoverflow.com/
Source Link

From here and herehere I wrote the code below which should work in 5.1 - it inserts an error in a different table - but it could be changed - at least the syntax works.

From here and here I wrote the code below which should work in 5.1 - it inserts an error in a different table - but it could be changed - at least the syntax works.

From here and here I wrote the code below which should work in 5.1 - it inserts an error in a different table - but it could be changed - at least the syntax works.

Source Link
Vérace
  • 30.6k
  • 9
  • 71
  • 84

From here and here I wrote the code below which should work in 5.1 - it inserts an error in a different table - but it could be changed - at least the syntax works.

Here is the trigger

DELIMITER $$
CREATE TRIGGER mytabletriggerexample
BEFORE UPDATE ON billy
FOR EACH ROW BEGIN
  IF(NEW.fred = OLD.fred) THEN
    BEGIN
      DECLARE dummy INT; // if you want to use this 
      SET dummy = 1000;  // value, you can, or a string!
      INSERT INTO errortab VALUES(current_timestamp, NEW.fred, OLD.fred, OLD.mary);
    END;
  END IF; 
END $$
DELIMITER ;

I then ran the code below.

mysql> create table billy( fred int, mary int);
Query OK, 0 rows affected (0.44 sec)

mysql> create table errortab(times timestamp, oldfred int, newfred int, oldmary int)
Query OK, 0 rows affected (0.44 sec)

I then loaded the trigger shown above.

mysql> 
mysql> insert into billy values(1, 2);
Query OK, 1 row affected (0.05 sec)

mysql> insert into billy values(2, 3);
Query OK, 1 row affected (0.14 sec)

mysql> insert into billy values(3, 4);
Query OK, 1 row affected (0.06 sec)

mysql> select * from billy;
+------+------+
| fred | mary |
+------+------+
|    1 |    2 |
|    2 |    3 |
|    3 |    4 |
+------+------+
3 rows in set (0.00 sec)

mysql> select * from errortab;
Empty set (0.03 sec)

mysql> 
mysql> update billy set fred = 2 where mary = 3;
Query OK, 0 rows affected (0.09 sec)
Rows matched: 1  Changed: 0  Warnings: 0

*** Note - no rows affected!

mysql> select * from billy
    -> ;
+------+------+
| fred | mary |
+------+------+
|    1 |    2 |
|    2 |    3 |
|    3 |    4 |
+------+------+
3 rows in set (0.00 sec)

mysql> select * from errortab;
+---------------------+---------+---------+---------+
| times               | oldfred | newfred | oldmary |
+---------------------+---------+---------+---------+
| 2014-07-05 00:58:07 |       2 |       2 |       3 |
+---------------------+---------+---------+---------+
1 row in set (0.00 sec)

mysql> select * from billy;
+------+------+
| fred | mary |
+------+------+
|    1 |    2 |
|    2 |    3 |
|    3 |    4 |
+------+------+
3 rows in set (0.00 sec)

mysql> 

That should be enough to get you started.