Use ON UPDATE CURRENT_TIMESTAMP when changing specific field

Asked

Viewed 231 times

2

Example scenario

Table: usuarios

Campos: id, nome, sobrenome, senha, timestamp_alteracao

Question

The field timestamp_alteração, should only be updated when the field senha is amended.

Doubt

  • There is a way to use as a value default field, so that it does this update as per condition?
    • Otherwise, you’d have to do update or even by Trigger?
    • What other options?
mysql sql trigger default
  • 1

    from to make the Rigger after update, my recommendation would be to do this in the backend.

  • @Hudsonph There is no way to use default expression then?

  • 1

    It would be a duplicate of that: https://answall.com/a/172551/112052 ?

  • 1

    @hkotsubo No, as I would like by default expression. Trigger would be if there is no option.

  • but DEFAULT GETDATE() would be for Insert and not update

1 answer

2


There is a way to use as a value default field, so that it does this update as per condition?

Not in every database I know, although technically it would be possible, but it would probably be extension to the default SQL. Mysql does not allow.

Otherwise, you’d have to do update or even by Trigger?

For update you can do whatever you want, but it does not seem to be what you want, even because parole could not be used alone, and is not guaranteed.

Triggers were created precisely for this scenario, every time a specific change is made an action must be made, that is, one action fires another under the control of the SGDB itself.

Obviously this scenario requires a conditional and the trigger needs to be powerful enough to support this kind of thing, so it is likely that something more than pure SQL is required, probably needs a programming language, even if based on SQL. Mysql has IF.

What other options?

Do in the application, which has its advantages there, but also disadvantages, especially if you do not know how to do it correctly, is not guaranteed, the application needs to be correct (which for me is not a problem if the person knows what they are doing, it depends on the person having a Dev or DBA bias).

Browser other questions tagged mysql sql trigger default

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