CODEWITHSUNDEEP
phpmysqltriggers
ProvocativePanda
ProvocativePanda

Reputation: 111

How to create a MySQL trigger to keep track of changes

I have a table called "products", the following columns are an example of what can be found in this table (although there are many more):

When price/our_cost is updated, I want to insert a row into a products_history table with the following:

Here's my current trigger, and while I expect this to work (except entering the employee_id) I get an error saying Column count doesn't match value at row 1 

DELIMITER |

CREATE TRIGGER after_update_products
    AFTER UPDATE ON products FOR EACH ROW
    BEGIN
        SET @History = '';

        IF NEW.price <> OLD.price THEN
            SET @History = CONCAT(@History, '(', OLD.products_id, ", price, ", OLD.price, ", ", NEW.price, "),");
        END IF;

        IF NEW.our_cost <> OLD.our_cost THEN
            SET @History = CONCAT(@History, '(', OLD.products_id, ", our_cost, ", OLD.our_cost, ", ", NEW.our_cost, "),");
        END IF;

        IF @History <> '' THEN
            SET @History = SUBSTRING(@History, 2, CHAR_LENGTH(@History) - 3);
            INSERT INTO products_history (products_id, field, old_value, new_value) VALUES (@History);
        END IF;
    END;
|

DELIMITER ;

What am I doing wrong?

On top of that, do I need to worry about putting quotes around the things I'm inserting (when I do this the error above still remains), and will escaping be an issue?

Also, how can I get PHP to add the $login_id to each row that was updated?

Upvotes: 1

Views: 5157

Answers (3)

Diogo Paim
Diogo Paim

Reputation: 1

You can simple add a "employee_id" field to your product list, then, the trigger should like something like this:

CREATE TRIGGER after_update_products
AFTER UPDATE ON products FOR EACH ROW
BEGIN
    IF NEW.price <> OLD.price THEN
        INSERT INTO products_history (products_id, field, old_value, new_value, guy, date) VALUES (old.products_id,'price',old.price,new.price,new.employe_id,now());
    END IF;

    IF NEW.our_cost <> OLD.our_cost THEN
        INSERT INTO products_history (products_id, field, old_value, new_value, guy, date) VALUES (old.products_id,'our_cost',old.our_cost,new.our_cost,new.employe_id,now());
    END IF;
END;

Upvotes: 0

user2191863
user2191863

Reputation: 1

Why are you creating an extra variable? You don't really need it. Why don't you try this:

CREATE TRIGGER after_update_products
AFTER UPDATE ON products FOR EACH ROW
BEGIN
    IF NEW.price <> OLD.price THEN
        INSERT INTO products_history (products_id, field, old_value, new_value) VALUES (old.products_id,'price',old.price,new.price);
    END IF;

    IF NEW.our_cost <> OLD.our_cost THEN
        INSERT INTO products_history (products_id, field, old_value, new_value) VALUES (old.products_id,'our_cost',old.our_cost,new.our_cost);
    END IF;
END;

I'm not very clear about your last question "Also, how can I get PHP to add the $login_id to each row that was updated?"

Upvotes: 0

Brian Moore
Brian Moore

Reputation: 276

Try removing the '(' and ')' from around @History.

INSERT INTO products_history (products_id, field, old_value, new_value) VALUES @History;

Also, you wont be able to have the PHP script pass the login_id to the Trigger, as that's handled beyond PHP's influence. You might not be able to use TRIGGER, and might have to build that INSERT statement in your PHP script.

Upvotes: 1

Related Questions