create a mysql triggers that reorganize the numbering of a field when one of those fields is deleted

Asked

Viewed 37 times

1

I have a table users containing the following column:

inserir a descrição da imagem aqui

note that the position numbering is in order, what I want is when deleting some user to trigger reorganize the numbering, that is to exclude Josefa and Pedro, Jone becomes position 2 and Soares becomes position 3.

php mysql

  • Can you explain why you need this? If you use this field only for sorting there is no need to reevaluate the values.

  • use these fields to perform other operations that need to be in sequence

  • could pick up the key from the array, the problem starts with zero

  • @Robsonguedesalves uses a rank for that? wouldn’t it be better!

1 answer

0


I don’t know how triggers work in Mysql, but in theory, you just need to decrement all the positions below the one you removed, it’s a simple SQL:

update usuarios set posicao = posicao - 1 where posicao < ?

Where "?" is the position of the user that has been removed.

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.