Question about a Trigger

  • 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?

  • 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

  • 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

    http://www.sqlservercentral.com/columnists/awarren/

  • 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