Trigger to change value before update

Question

Hello,

I am using a software which updates a datetime column periodically in a SQL Server 2019 Database. This software sometimes updates with an incorrect datetime value (eg '3262196-08-27 00:00:00') and generates a conversion error.

I have created an "INSTEAD OF UPDATE" trigger in order to check of this value before update and change it if needed.

When I issue an update statement with a correct value, I can see that the trigger fires correctly because of a message being printed during execution.

However, when I issue the problematic update statement, I get the conversion error and the message doesn't print, which leads me to believe that the trigger is not fired.

Is the datatype conversion checked before the trigger is fired ?

(i.e. update table set <datetime_field> = '3262196-08-27 00:00:00' where key = key already produces a conversion to datetime)

How could I get the desired result, for instance changing this value to '2022-02-07 00:00:00' ?

Thank you for your help !

Answer 1

There is a possibility to save the show, although it's a little complicated.

You rename the table to something else. Then you create a view where you make the datetime columns into strings with the convert function. Then you add an INSTEAD OF trigger on the view which writes the data to the table.

But this also means that queries against this view will return strings instead of dates, and that certainly could have repercussions.

Answer 2

Is the datatype conversion checked before the trigger is fired ?

Yes, of course. The data is first inserted into a virtual table called "inserted" / "deleted" with the exact the same table design/data types as the target table = date.

Answer 3

Hi @Benoît USUNIER

How could I get the desired result, for instance changing this value to '2022-02-07 00:00:00' ?

The key point is that there is no way to convert this incorrect datetime value '3262196-08-27 00:00:00' to '2022-02-07 00:00:00'. It will always get conversion error. Or you could give a default value, for example, whenever an incorrect date is encountered during the update, the wrong value is replaced by the date of the update operation occurred.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Hi all, Thank you for your answers, these have definitely helped me better understand how this works and come up with a solution. Have a great day!