There is a query that writes data to a table. There are columns: dt_create and dt_update . We execute the request and the data is recorded, and the date of creation in dt_create . Once again we execute the query, respectively, in dt_create date of the recording, and in dt_update the update date. How to do?

    2 answers 2

    The creation date can be entered into the database using the ON INSERT trigger , and the update date using the ON UPDATE trigger.

    For example. We enter the creation date in the ON INSERT trigger:

     UPDATE [dbo].[Table_1] SET dCreate=GETDATE() WHERE id=(SELECT id from inserted);

    Entering the update date in the ON UPDATE trigger is similar, but instead of the dCreate field, for example, dUpdate will be dUpdate .

    Alternatively, you can use stored procedures instead of direct queries. And in them to carry out operations of addition:

     INSERT INTO [dbo].[Table_1] (...,dCreate) VALUES (...,GETDATE());

    And updates:

     UPDATE [dbo].[Table_1] SET dUpdate=GETDATE() WHERE id=...;

    For the field with the date of creation of the record there is another 3rd option. Assign the current date / time as the default value.

    • Can you show an example - propro17
    • @ propro17, an example of a command to be executed in a trigger, and a link to the documentation added to the response. I think now you can figure it out. - Streletz
    • Uh ... well, you can't update the table in the same trigger that handles the event above this table! This, at least, extra disk operations! - Pavel Mayorov
    • I need to do this in C #. string str = string.Format(" *тут код* ") . Is that so? - propro17

    You can try using an INSTEAD OF trigger to override these fields. But such a trigger is incompatible with cascading operations.

    Therefore, the standard approach for applying such rules is to create a stored procedure that will be called instead of INSERT / UPDATE / DELETE. So, all ORMs can call stored procedures to perform operations on objects.

    Another option is to create a view, and hang an INSTEAD OF trigger on it. This approach is compatible with cascading operations.

    PS but in modern projects the correct way is to use the database only as data storage, and all such rules can be put into the data access layer.

    • As for using the database only as a repository. "Modern", alas, is a relative concept. Such an approach was used everywhere before the introduction of programming support in the DBMS. But that's not the point. Even if it is really justified. Immediately, a number of questions arise regarding, as a minimum, business logic and security modifications. Not to mention that in this case, you often have to manually do what the DBMS does automatically. Of course, all this significantly saves server resources. Only, the main thing is that in order to save 1 ruble in the end I did not have to spend 10 (this is me figuratively). - Streletz
    • @Streletz is just the point that the DB does not automatically put these fields in - Pavel Mayorov
    • With reference to this topic, to be honest, it is not quite so. If you wish, you can have the DBMS fully record the date / time of the record creation itself. For example, set the field to the appropriate default value (then even no program logic is needed!). With the update, I agree, somewhat more complicated. - Streletz