0

I want to update inserted row field name is "hash_code" with MD5().

DELIMITER
    //
CREATE TRIGGER `make_hash2` AFTER  INSERT ON
    `newtable` FOR EACH ROW
BEGIN
    DECLARE
        _id INTEGER ;
    SELECT
        id
    INTO _id
FROM
    `newtable`
ORDER BY `id`
DESC
LIMIT 1;

 

UPDATE
    `newtable`
SET
    `hash_code` = MD5(_id)
WHERE
    id = _id ;
END

I tried using the following code but throwing the following error on insert.

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

Serg
  • 22,285
  • 5
  • 21
  • 48
Ankit Kalal
  • 1
  • I don't think you can do everything with the trigger. maybe try with Stored Procedure. Have a look [here](https://stackoverflow.com/questions/12877732/mysql-trigger-for-updating-same-table-after-insert) – Tony May 05 '21 at 10:27

1 Answers1

0

If you want hash_code to be the md5() hash of the id, you can use a generated column:

alter table newtable add column hash_code varchar(32) as
     (md5(id))

Here is a db<>fiddle.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786