Thanks. I can't control the update statement that runs, as this is done by the application.

(When the user clicks save, the application updates the entire row.)

Because I can't control this, my trigger is simply a workaround. Perhaps my trigger can be rewritten a bit?

CREATE TRIGGER [dbo].[TRG_PreserveActiveStatus] ON [dbo].[FD__MEDICATIONORDERS]

FOR UPDATE

AS

SET NOCOUNT ON

DECLARE @MEDORDERKEY INT

DECLARE @NOTED VARCHAR(25)

DECLARE @UPDATEDSTATUS VARCHAR(25)

-- STATUS OF ORDER IS PENDING WHEN ORIGINALLY SAVED, ONCE ORDER IS SUBMITTED, IT CHANGES TO ACTIVE

-- IF USER SAVES FORM AGAIN, STATUS CHANGES FROM ACTIVE TO PENDING WHICH IS WRONG SINCE DOSES EXIST

-- THIS ROLLS THE PENDING STATUS CHANGE BACK

SELECT @MEDORDERKEY = MEDORDERKEY

,@UPDATEDSTATUS = STATUS

FROM inserted

IF @UPDATEDSTATUS = 'Pending'

BEGIN

SELECT @NOTED = NOTED

FROM FD__MEDICATIONORDERS

WHERE OP__DOCID = @MEDORDERKEY

IF @NOTED = 'T'

ROLLBACK

END