"There is no OLD row in on INSERT trigger" error in MySQL

Question

I tried to create a trigger with OLD.num and NEW.num as shown below:

CREATE TRIGGER my_trigger
AFTER INSERT ON test FOR EACH ROW
SET @old_num = OLD.num, @new_num = NEW.num;
             -- ↑ Here           -- ↑ Here

But, I got the error below:

ERROR 1363 (HY000): There is no OLD row in on INSERT trigger

So, how can I solve it?

Answer 1

The most likely explanation for getting an error

"There is no OLD row in on INSERT trigger"

is that you are executing a statement that's creating an AFTER INSERT trigger, rather than creating an AFTER UPDATE trigger.

The reason that you can't reference OLD values from the row, as the row existed prior to the INSERT, is that the row did not exist prior to the INSERT.

Answer 2

The doc says below:

OLD.col_name refers to a column of an existing row before it is updated or deleted.

So, OLD.col_name only works for UPDATE or DELETE as shown below:

CREATE TRIGGER my_trigger
AFTER UPDATE ON test FOR EACH ROW
   -- ↑ Here
...

Or:

CREATE TRIGGER my_trigger
AFTER DELETE ON test FOR EACH ROW
   -- ↑ Here
...

Answer 3

In an UPDATE TRIGGER, you can use the OLD keyword to access the row data which is being replaced by the update. The NEW keyword allows accessing the incoming row data which will replace the old row, if successful.

An example of an UPDATE trigger is:

CREATE TRIGGER upd_check AFTER UPDATE ON SomeTable
    FOR EACH ROW
    BEGIN
       IF (OLD.LastChangedBy <> NEW.LastChangedBy) THEN
         INSERT INTO AuditSomeTable(ID, LastChangedBy) 
         VALUES (OLD.ID, OLD.LastChangedBy);
       END IF;
    END;

SQLFiddle here

Depending on the type of trigger created, the OLD and NEW rows may not be available to you:

INSERT TRIGGER

• Access to the NEW pseudo rows only.

UPDATE TRIGGER

• Access to the NEW and OLD pseudo rows

DELETE TRIGGER

• Access only to the OLD pseudo rows

i.e. there is no OLD row on an INSERT trigger, and no NEW row on a DELETE trigger.

OP's Question

OP hasn't provided the actual code, and the error message referred to in the comments:

There is no OLD row in on INSERT trigger

indicates that the OP had inadvertently created an INSERT TRIGGER and not an UPDATE TRIGGER as was indicated in the question. An INSERT trigger has no OLD pseudo table.

Answer 4

here is my complete code of how trigger will work when some column value updated

DELIMITER $$

CREATE TRIGGER `salestatus_after_update` AFTER UPDATE ON `salesdata`
 FOR EACH ROW BEGIN

        IF NEW.sale_status <> OLD.sale_status THEN
            SET @changetype = 'Sale Status Updated';
        ELSE
            SET @changetype = 'Sale Edited';
        END IF;

        INSERT INTO sales_notification (sale_id, changetype, notify_to, old_value, new_value) VALUES (NEW.id, @changetype, NEW.submit_by, OLD.sale_status, NEW.sale_status);

    END$$

DELIMITER ;

Answer 5

If I get your question right..

The answer is no! You can't have OLD.col_name in an AFTER INSERT trigger.

For your reference in mysql ref manual it clearly dictates that :

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.

Trigger Syntax

Answer 6

In trigger body, the OLD and NEW keywords enable you to access columns in the rows affected by a trigger. OLD and NEW are MySQL extensions to triggers; they are not case sensitive.

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.