CODEWITHSUNDEEP
mysql
Ruchi Tengse
Ruchi Tengse

Reputation: 105

Modifying values in existing records using trigger

I have a MySQL table with the following data

id INT
time_stamp timestamp
status INT

When inserting, the time_stamp attribute will always be current_timestamp, once the current timestamp is more than 48 hrs of the value inserted in the table, i need to change the status to 0.

I created the following trigger:

DELIMITER $$
CREATE TRIGGER new_table_AINS 
BEFORE INSERT ON new_table
FOR EACH ROW begin
DECLARE now_value timestamp;
SET @now_value = current_timestamp;
UPDATE new_table SET new.status = 0 where @now_value = new.time_stamp + interval 48 hour;
END$$

But when I try to insert a new row, I get Error Code 1442 in MySQL

Error Code: 1442. Can't update table 'new_table' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. Please help me.

Thanks a lot!

Upvotes: 0

Views: 42

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

In a "before" trigger, you cannot re-modify the table that you are triggering on. However, I don't think you need a trigger. Instead, use a view:

create view v_ new_table_AINS as
    select . . .,
           (case when current_timestamp >= time_stamp + interval 48 hour then 0
                 else status
            end)
    from new_table_AINS;

At your leisure, you can then run a job that updates the status to zero for old timestamps.

Upvotes: 1

Related Questions