Post reply

Need help with an update Trigger to update set an activerecord field

  • August 4, 2004 at 6:50 pm

    #164572

    I have a table with an update trigger that is giving me problems.

    What I want to do is set an activerecord field for the most current record of a group.  This I have working.

    What I need help with is resetting this field for the other records in the group.

    Example.

    OrderID is the primary key, JudicialFileID is indexed but not unique

    OrderID   JudicialFileID    activerecord

    1                   1                 0

    2                   1                 0

    3                   1                 1

    When a new record is added, I need the update trigger to set the active flag and to reset the previous active record.

    OrderID   JudicialFileID    activerecord

    1                       1                       0

    2                       1                       0

    3                       1                       0

    4                       1                      1

    Here is what I,ve tried

    ALTER TRIGGER utUpdateOrderSummary

    ON dbo.OrderSummary

    FOR INSERT, UPDATE

    AS

    UPDATE a

    Set a.ActiveRecord =0

    From Inserted, OrderSummary a

    Where Inserted.JudicialFileID = a.JudicialFileID

    UPDATE a

    Set a.ModifyDate = GetDate(), a.ModifyUser = SUser_SName(),

    a.ActiveRecord = CASE a.OrderID WHEN

    (SELECT MAX(OrderID) AS Expr1

    FROM OrderSummary

    WHERE (JudicialFileID =inserted.JudicialFileID)

    GROUP BY JudicialFileID)

    THEN 1

    ELSE 0

    End

    From Inserted, OrderSummary a

    Where Inserted.OrderID = a.OrderID

    Thanks for any ideas.

     

  • August 4, 2004 at 10:57 pm

    #517748

    Hi ajaac,

    you have same trigger for insert,update. so, during update operation check for the existence by using the IF EXISTS() clause and do the update operation.

    so far your trigger, irrespective of insert,update it will perform the same operation. bcz your are using onyl INSERTED tables values. not deleted.

    so check whether row exists or not. if exists perform the task what u wanted to.

    regards,

    Ganapathy

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply