GetDate On Insert但不更新 [英] GetDate On Insert but not update
问题描述
我有一个c#程序,它定期将更新和插入命令写入db。在我的sql表中,我有一个名为modifiedDate的列,假设在将行插入表中时获取当前日期时间。我用这样的方式来设置这个列。
I have a c# program that periodically writes update and insert commands to a db. In my sql table i have a column called modifiedDate that is suppose to get the current datetime when the row is inserted into the table. I used a stement like this to set up this column that way.
ALTER TABLE MyTable
ADD CONSTRAINT DF_MyTable_MyColumn
DEFAULT GETDATE() FOR MyColumn
当我的应用程序向db发送一个insert语句时,这很有效,但我注意到当我执行更新语句时,modifiedDate字段不会更新当前的日期和时间。
我会想到,因为该字段设置为检索新数据发生的日期我在插入时根本不包括它声明。
And this works great when my app sends an insert statement to the db, but i've noticed that when i do an update statement, the modifiedDate field does not update with the current date and time.
I would have thought that since that field was setup to retrieve the date when new data occurred i was not including it at all in my insert statement.
Update table set name= 'Bob', address = 'Main Street'' where id = '28'
每次插入或更新数据时,如何允许此字段为getdate()?
先谢谢!
How can I allow this field to getdate() everytime data is inserted or updated?
Thanks in Advance!
推荐答案
默认约束仅适用于插入,更新使用触发器。触发器是最好的方法,因为这个逻辑与表紧密相关,而不是与应用程序紧密相关。这只是我能想到的唯一明显的正确使用触发器,除了更细粒度的参照完整性。
请参阅: [ ^ ]
还要查看一个类似的主题:此处 [ ^ ]
- Amit
A default constraint only works on inserts, for an update use a trigger. Triggers are the best way, because this logic is intimately associated with the table, and not the application. This is just about the only obvious proper use of a trigger that I can think of, apart from more granular referential integrity.
Please see : Create a Modified On Column in SQL Server[^]
And also check one similar thread : here[^]
--Amit
这篇关于GetDate On Insert但不更新的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!