How to realize this in Transact SQL ( SQL Server 2008 )

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 don开发者_运维问答t work of course ;)

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:

CREATE TRIGGER schema.tbl_name_insert ON schema.tbl_name
    AFTER INSERT
    AS
        SET NOCOUNT ON

        UPDATE schema.tbl_name
        SET mtime = getdate()
        FROM schema.tbl_name
        INNER JOIN JOIN INSERTED
            ON schema.tbl_name.PK = INSERTED.PK

or

CREATE TRIGGER schema.tbl_name_insert ON schema.tbl_name
    AFTER INSERT
    AS
        SET NOCOUNT ON

        UPDATE schema.tbl_name
        SET mtime = getdate()
        FROM schema.tbl_name
        WHERE schema.tbl_name.PK IN (SELECT PK FROM INSERTED)

A trigger fires once for an entire INSERT statement, so, in general, the INSERT AND DELETED tables may contain multiple rows.

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):

CREATE TRIGGER [MAP].[TR_iu_MyTable__LastUpdated]
 on [dbo].[MyTable]
 after insert, update

AS

    SET NOCOUNT on

    UPDATE MAP.MyTable
     set LastUpdated = getdate()
     where MyTableId in (select MyTableId from inserted)

GO

(Note that this is an "after" trigger, so I only need to run an UPDATE.)

First, for the insert you can set a default value to be GETDATE().

Then on update, try something like this:

CREATE TRIGGER TimeUpdater ON Auctions FOR UPDATE
AS
UPDATE auctions 
SET mtime = GETDATE() 
FROM UPDATED u
WHERE Id = u.Id