July 23, 2002 at 11:47 am
This may be a dumb question, but I need to create a trigger so that when an OrderStatus field is changed from open to closed, it inserts the current date and time into the DateClosed field on the record that is being closed. What is the best way to do this?
July 23, 2002 at 12:09 pm
Okay, this is what I came up with, it is pretty close to what I want:
CREATE TRIGGER orderclose ON dbo.WorkOrders
FOR INSERT, UPDATE
AS
IF UPDATE(OrderStatus)
BEGIN
UPDATE c SET DateCompleted = getdate()
FROM inserted i
INNER JOIN dbo.WorkOrders c ON i.OrderID = c.OrderID
END
However, ideally it would only work if the value of the OrderStatus column was 'Closed' this trigger runs on any update of the field.
Mark
July 23, 2002 at 3:28 pm
Thats pretty good. Only point I'd like to make is that the Update() will fire even if the column was updated to equal the original value. If you truly only want to do it on changes you have to compare the values from the logical inserted and deleted tables.
Andy
July 24, 2002 at 10:38 pm
You will need to do something like this:
declare @iVar1 varchar
declare @oVar1 varchar
--Populate the vars (aliasing is necessary)
select @iVar1 = i.Var1 from inserted i
select @oVar1 = d.Var1 from deleted d
--Check if values have changed
if @iVar1 <> @oVar1
begin
--do your processing here
end
Hope this is what you want.
quote:
Okay, this is what I came up with, it is pretty close to what I want:CREATE TRIGGER orderclose ON dbo.WorkOrders
FOR INSERT, UPDATE
AS
IF UPDATE(OrderStatus)
BEGIN
UPDATE c SET DateCompleted = getdate()
FROM inserted i
INNER JOIN dbo.WorkOrders c ON i.OrderID = c.OrderID
END
However, ideally it would only work if the value of the OrderStatus column was 'Closed' this trigger runs on any update of the field.
Mark
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply