Writing Triggers in MySQL in regards to BEFORE UPDATE

Question

I am very new to Triggers and so this is a very beginner question. I am trying simply to, before a row updates, store its id, old and new values, and the current date into a new table. I have tried the following:

DELIMITER //
    DROP TRIGGER IF EXISTS stockTrig//
    CREATE TRIGGER stockTrig BEFORE UPDATE ON products
        FOR EACH ROW
        BEGIN
            IF NEW.quantity != OLD.quantity THEN
                INSERT INTO stock_log SET p_id = OLD.id, old_stock = OLD.quantity, new_stock = NEW.quantity, date = CURDATE();
            END IF;
        END//
DELIMITER;

I'm getting the following:

1235 - This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'

...which I don't quite understand. I am using MySQL 5.1.53, which shouldn't be outdated. What exactly is going wrong here?

Do you already have a BEFORE UPDATE trigger on `products`? You have other problems in your trigger as well but first things first. — mu is too short, Feb 23 '12 at 04:55
Yes I did, but I got rid of it with `SHOW TRIGGERS;`, so that fixed that error, but now I'm getting some syntax errors near 'DELIMITER' at line 1. — id2341677, Feb 23 '12 at 05:00
Updated it even more. When I paste it into phpmyadmin, it says I have some syntax errors, but the trigger still goes through and functions as intended. Any ideas? — id2341677, Feb 23 '12 at 05:06
As I know, phpmyadmin instances (old versions) do not process DELIMITER commands. Try to execute CREATE TRIGGER statement without delimiters. — Devart, Feb 23 '12 at 07:50

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

2

There's nothing wrong I can see with your code.

Can you just use mysql command line? (like every other dev/DBA I know uses for schema changes?)

Edit:

Your INSERT syntax is unorthodox, but valid for mysql. Normally it would be:

...
INSERT INTO stock_log (p_id, old_stock, new_stock, date)
    VALUES (OLD.id, OLD.quantity, NEW.quantity, CURDATE());
...

edited Jun 20 '20 at 09:12

Community

1
1

answered Feb 23 '12 at 05:12

Bohemian

412,405
93
575
722

1

@Devart OK. "You learn something every day". I didn't know about that syntax for insert with mysql. Cool. – Bohemian Feb 23 '12 at 12:18
1

Yes, it is a different way to perform this action. Agree about "You learn something every day" ;-) – Devart Feb 23 '12 at 13:03

Writing Triggers in MySQL in regards to BEFORE UPDATE

1 Answers1

Edit: