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

Question

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

Answer 1

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.

Answer 2

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

Answer 3

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