set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[update_ts_pdu] ON [dbo].[tbl_pdu]
AFTER INSERT,UPDATE
AS

DECLARE @pointID int

DECLARE curModifiedData CURSOR FOR
SELECT pointID
FROM INSERTED

OPEN curModifiedData
FETCH NEXT FROM curModifiedData INTO @pointID
WHILE (@@FETCH_STATUS <> -1)
BEGIN
UPDATE tbl_pdu
SET updateTimestamp = getDate()
WHERE pointID = @pointID
FETCH NEXT FROM curModifiedData INTO @pointID
END

CLOSE curModifiedData
DEALLOCATE curModifiedData

ALTER TABLE [dbo].[tbl_pdu] ADD CONSTRAINT DF_tbl_pdu_updateTimestamp DEFAULT GETDATE() FOR updateTimestamp


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[update_ts_pdu] ON [dbo].[tbl_pdu]
AFTER UPDATE
AS

BEGIN

IF NOT UPDATE(updateTimestamp)
BEGIN
UPDATE t
SET t.updateTimestamp = getDate()
FROM tbl_pdu t
JOIN INSERTED i
ON i.pointID = t.pointID
END

END

quote:

---

Originally posted by Kristen

"IF NOT UPDATE(updateTimestamp)"

That's an interesting idea - allow the application to set the updateTimestamp if it wants to?

Although is there some risk that the App only sets it on some of the rows in the update set? ... there I am being pedantic again

We stuff the date in via a trigger on all Inserts / Updates, but actually the SProc probably does it two most of the time. This would save some time, and TLog space.

---