how to write a mysql trigger to log history

Question

I have never written a trigger before and I am following a tutorial http://net.tutsplus.com/tutorials/databases/introduction-to-mysql-triggers/ but I am confused about something.

I am trying to write a trigger which pulls data from a table AFTER INSERT and logs it in a history table. Here's my code so far:

DELIMITER $$

CREATE TRIGGER trackHistory
AFTER INSERT ON test.inventory
FOR EACH ROW BEGIN
***
END;

DELIMITER ;

I need to add a INSERT INTO history... query, but I don't understand how I reference the fields on the correct row of the 'inventory' table. Will the answer be the same for and UPDATE or DELETE?

EDIT: I have tried to follow the advice of the answer below, but it didn't work I got this error

1363 - There is no OLD row in on INSERT trigger. Whats wrong?

DELIMITER $$

CREATE TRIGGER trackInsertHistory
AFTER INSERT ON inventory
FOR EACH ROW BEGIN
    INSERT INTO history
    VALUES (NEW.ID, OLD.Quantity, NEW.Quantity, TIMESTAMP);
END$$

CREATE TRIGGER trackStockHistory
AFTER UPDATE ON inventory
FOR EACH ROW BEGIN
    INSERT INTO history
    VALUES (NEW.ID, OLD.Quantity, NEW.Quantity, TIMESTAMP);
END$$

DELIMITER ;

only the NEW record is there on an INSERT. For an UPDATE, the NEW record holds the 'new' values, and the OLD record holds the 'old' values. — BWS
– BWS, Commented Aug 12, 2013 at 19:15
Oh, of course. So, what I really want to do on the after insert update is set both 'old_quantity' and 'new_quantiy' on the table to be NEW.Quantity. Thanks again! — user1015214
– user1015214, Commented Aug 12, 2013 at 19:20
Interesting... I got this error now #1235 - This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'... I guess I just need to do this in two separate queries? — user1015214
– user1015214, Commented Aug 12, 2013 at 19:22
Maybe you should add to the HISTORY what type of update it was (Insert, Update) ... or, could just be inferred when old=new — BWS
– BWS, Commented Aug 12, 2013 at 19:31

BWS · Accepted Answer · 2013-08-12 18:50:45Z

2

they are referenced as NEW and OLD.

NEW is the new record to be inserted or the updated data.

OLD is the deleted record, or the old data before an update.

answered Aug 12, 2013 at 18:50

BWS

3,85620 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1015214 Over a year ago

I just made an edit, I can't figure out how to follow your advice. Please advise.

Tia · Accepted Answer · 2015-07-21 12:06:35Z

2

In an INSERT trigger, only NEW.col_name can be used; there is no old row. In a DELETE trigger, only OLD.col_name can be used; there is no new row. In an UPDATE trigger, you can use OLD.col_name to refer to the columns of a row before it is updated and NEW.col_name to refer to the columns of the row after it is updated.

answered Jul 21, 2015 at 12:06

Tia

1191 gold badge1 silver badge11 bronze badges

Collectives™ on Stack Overflow

how to write a mysql trigger to log history

1363 - There is no OLD row in on INSERT trigger. Whats wrong?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1363 - There is no OLD row in on INSERT trigger. Whats wrong?

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related