April 3, 2008 at 8:08 am
Could somebody please assist with this query, I am trying to update a timestamp for a record, if the record is edited(Updated), as the trigger stands below it updated the entire table.
ALTER TRIGGER [dbo].[Trigger_tbl_Process_Constants] ON dbo.tbl_Process_Constants
FOR UPDATE
AS
begin
UPDATE dbo.tbl_Process_Constants
SET Date_Modified = GETDATE()
end
Thanks you for the assistance, in advance...
April 3, 2008 at 8:22 am
Well, you have forgotten the where clause in your update statement 🙂
You need to check that the updated record is in your "inserted" or "deleted" table (these contain all the rows that have been modified in this trigger invocation.
Regards,
Andras
April 3, 2008 at 8:30 am
Steven Coleman (4/3/2008)
Could somebody please assist with this query, I am trying to update a timestamp for a record, if the record is edited(Updated), as the trigger stands below it updated the entire table.ALTER TRIGGER [dbo].[Trigger_tbl_Process_Constants] ON dbo.tbl_Process_Constants
FOR UPDATE
AS
begin
UPDATE dbo.tbl_Process_Constants
SET Date_Modified = GETDATE()
end
Thanks you for the assistance, in advance...
So basically something like:
CREATE TRIGGER [dbo].[Trigger_tbl_Process_Constants] ON dbo.tbl_Process_Constants
FOR UPDATE
AS
BEGIN
UPDATE dbo.tbl_Process_Constants
SET Date_Modified = GETDATE()
WHERE keycolumn IN ( SELECT keycolumn
FROM inserted )
END
Regards,
Andras
April 3, 2008 at 9:02 am
Also - just to avoid recursive trigger calls - (stealing Andras' code)
CREATE TRIGGER [dbo].[Trigger_tbl_Process_Constants] ON dbo.tbl_Process_Constants
FOR UPDATE
AS
BEGIN
IF not(update(Date_Modified))
BEGIN
UPDATE dbo.tbl_Process_Constants
SET Date_Modified = GETDATE()
WHERE keycolumn IN ( SELECT keycolumn
FROM inserted )
END
END
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 15, 2014 at 4:17 pm
Thank you! This was just what I needed.
October 15, 2014 at 5:45 pm
sorry...this was goofy and I figured it out but cannot figure out how to delete this post.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply