Wednesday, June 18, 2008

Update Trigger Syntax

CREATE TABLE dbo.foo
(
ID INT IDENTITY (1, 1) PRIMARY KEY NONCLUSTERED
, DomainValue VARCHAR (126) UNIQUE NOT NULL
, DomainType CHAR (1) NOT NULL
, Active BIT DEFAULT 1 NOT NULL
, Created DATETIME DEFAULT GETDATE()
, Modified DATETIME DEFAULT GETDATE() -- TRIGGER
)

GO

CREATE CLUSTERED INDEX cliDomainType ON Foo (DomainType)

GO

CREATE TRIGGER tru_Foo ON dbo.Foo
FOR UPDATE
AS
BEGIN
UPDATE t
SET t.Modified = GETDATE()
FROM Foo AS t
WHERE t.ID =
(
SELECT ID FROM INSERTED
)
END

No comments: