如何在 Transact SQL 中实现这一点(SQL Server 2008)
我只想要一个像postgresql版本这样的更新触发器...在我看来没有新的。和旧的。在 MSSQL 中?
CREATE OR REPLACE FUNCTION "public"."ts_create" () RETURNS trigger AS
DECLARE
BEGIN
NEW.ctime := now();
RETURN NEW;
END;
已经用谷歌搜索了,但不幸的是没有找到任何东西......想法?
更新: ……,这样吗?
CREATE TRIGGER tr01 ON Auctions for insert
As
update auctions set mtime = GETDATE() where Id = inserted.Id;
或者这个:
CREATE TRIGGER tr01 ON Auctions for insert
As
inserted.ctime := GETDATE();
当然这两者都不起作用;)
I just want an update trigger like this postgresql version... It seems to me there is no NEW. and OLD. in MSSQL?
CREATE OR REPLACE FUNCTION "public"."ts_create" () RETURNS trigger AS
DECLARE
BEGIN
NEW.ctime := now();
RETURN NEW;
END;
Googled already, but nothing to be found unfortunately... Ideas?
Update:
Sth, like this?
CREATE TRIGGER tr01 ON Auctions for insert
As
update auctions set mtime = GETDATE() where Id = inserted.Id;
or this:
CREATE TRIGGER tr01 ON Auctions for insert
As
inserted.ctime := GETDATE();
bothg dont work of course ;)
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
在 SQL Server 中,伪表称为 已插入和更新。
但是,对于您的特定示例,我将在 ctime 列上使用默认约束:
或
触发器针对整个 INSERT 语句触发一次,因此,一般来说,INSERT AND DELETED 表可能包含多行。
In SQL Server the pseudo tables are called INSERTED and UPDATED.
I would use a default constraint on the ctime column for your particular example, however:
or
A trigger fires once for an entire INSERT statement, so, in general, the INSERT AND DELETED tables may contain multiple rows.
对于触发器,SQL 提供名为“inserted”和“deleted”的可用虚拟表。这是我已经使用了几个月的示例例程触发器(SQL 2005):(
请注意,这是一个“之后”触发器,因此我只需要运行更新。)
For triggers, SQL makes available virtual tables named "inserted" and "deleted". Here's a sample routine trigger I've had in place for months (SQL 2005):
(Note that this is an "after" trigger, so I only need to run an UPDATE.)
首先,对于插入,您可以将默认值设置为 GETDATE()。
然后在更新时,尝试这样的事情:
First, for the insert you can set a default value to be GETDATE().
Then on update, try something like this: