CODEWITHSUNDEEP
mysqltriggers
anubis
anubis

Reputation: 1505

how to update a column instead of delete in a trigger?

I want to change the column archive to 1, instead of deleting the row.

For example, I have a row with some attributes, the last one is "archive", which normally is to 0, and a delete sql is done, it must not delete the row, but juste change the attribute archive to 1.

I think that I have to use a trigger. I tried, but it don't work.

I done a trigger after delete, and I insert the line after the deletion.

BEGIN
    INSERT INTO `type`(`tId`,`archive`) VALUES (OLD.tId, 1);
END

Then I have the error:

#1442 - Can't update table 'type' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

regards

Upvotes: 1

Views: 705

Answers (2)

JProgrammer
JProgrammer

Reputation: 38

Why do you first delete and insert afterwards the same data again?

Can you use a update statement like this?

UPDATE `type` SET `archive`=1 WHERE `tId` = OLD.tid;

Upvotes: 1

CapeStar
CapeStar

Reputation: 147

According to your description, it means you already have the row existing so try try to UPDATE the row instead on INSERT

UPDATE table_name SET archive='1' WHERE tid=OLD.Tid;

Upvotes: 0

Related Questions