Time Stamp field when row is updated
-
I have an ASP.NET page modifying a table and want to be able to place a Time/Date Stamp in the last field of a row every time that row is updated or inserted. Here is what I have but it only adds a time/Date stamp if I am on the first row, if I move to any other row the time/date stamp does not change. Does anyone have a sugestion?
set
ANSI_NULLS ON
set
QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER
TRIGGER [tr_LastUpd.sql]
ON [dbo].[Assets]
FOR
INSERT, UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
DECLARE
asset_cursor
CURSOR
FOR
SELECT
*
FROM
Assets
OPEN
asset_cursor
FETCH
asset_cursor
update
Assets
set
LastUpdateDateTime=getdate()
WHERE
CURRENT OF asset_cursor
CLOSE
asset_cursor
DEALLOCATE
asset_cursor
END
-
ALTER TRIGGER [tr_LastUpd.sql]
ON [dbo].[Assets]
FOR
INSERT, UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
DECLARE
asset_cursor
CURSOR
FOR SELECT * FROM Assets
OPEN
asset_cursor
FETCH NEXT FROM
asset_cursor
WHILE
@@FETCH_STATUS = 0
BEGIN
update Assets
set
LastUpdateDateTime=getdate()
WHERE
CURRENT OF asset_cursor
FETCH NEXT FROM asset_cursor
END
CLOSE
asset_cursor
DEALLOCATE
asset_cursor
END
--- why do you use cursor?
Kindest Regards,Vasc
-
If I don't use a cursor, so say:
-- Insert statements for trigger here
update Assets
set LastUpdateDateTime=getdate()
It updates every row if one field is changed. If I make the chagnes you suggest though I get the same result. One field changes every row gets a time/day stamp
-
Anyway that it works I am willing to try, this is all I know however.
-
the cursor will do the same : update all all rows
what you need is
for insert put default for your field getdate() in table
for updates pass a default param with getdate()
For triggers look at deleted and inserted tables
Kindest Regards,Vasc
-
create trigger dbo.Assets_tiu
on dbo.Assets
after insert, update
as
set nocount on
-- Assuming table has PK of AssetID
update dbo.Assets
set LastUpdated = getdate()
where dbo.Assets.AssetID in (select I.AssetID from inserted I)
go
-
YOU ARE MY HERO!!!
That works perfect, Thanks so
much.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply