April 3, 2012 at 3:16 pm
I'm trying to prevent a user from updating a value in a table from b to a. Once the value becomes b, it should never revert back to a.
To accomplish this, I decided to add a trigger on the table and if they try to update from value b back to value a I don't want to allow this.
In my trigger, I used Rollback to prevent the update from happening. This trigger works well in the sense that it does not allow value a into the field however, when I do this, I get a message from SQL saying:
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
Is this message simply informative and not hurting anything or is it causing harm. Should I be doing this differently?
April 3, 2012 at 8:36 pm
Error Level 16 indicates that the error can be corrected by the user.
From what you have stated, I am led to believe that there is more to your problem than what you have posted. There is no need to use a trigger to enforce your requirement.
For example your UPDATE T-SQL code could be as simple as:
DECLARE @U VARCHAR(1)
SET @U = 'A'
UPDATE #RB SET X = @U WHERE X <> 'b'
The fallacy of the simple code is that if the value is NOT 'B', but lets say it is 'Z'. then the above T-SQL would update the 'Z' to an 'A'.
To get tested help please post table definition, sample data and required results when using the sample data. To do so quickly and easily please read the article in the first link of my signature block.
April 3, 2012 at 10:52 pm
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
April 4, 2012 at 9:52 am
Yes you could and should re-write the trigger, but not just because of the error message, which you get whenever you have a rollback in a trigger, there's no way to avoid the message you just need to expect it.
The reason you should re-write the trigger is because it cannot handle a set-based update. It assumes that there will NEVER be a case where multiple rows will be updated by a statement. I suggest you read this article, http://www.sqlservercentral.com/articles/Triggers/64214/, which explains more about what I'm saying here.
In this case I think your best option is to use an INSTEAD OF trigger instead of the default AFTER trigger. So you're trigger would look something like this:
CREATE TRIGGER [dbo].[TRG_PreserveActiveStatus] ON [dbo].[FD__MEDICATIONORDERS]
INSTEAD OF UPDATE
AS
BEGIN;
SET NOCOUNT ON;
/* This trigger only UPDATES dbo.FD__MEDICATIONORDERS when the status isn't back to pending and noted is not 'T' */
UPDATE dbo.FD__MEDICATIONORDERS
SET STATUS = I.STATUS, column1 = I.column1, ...
FROM
INSERTED AS I
WHERE
dbo.FD__MEDICATIONORDERS.PrimaryKey = I.PrimaryKey AND
I.STATUS <> 'Pending' AND I.NOTED <> 'T' ;
END;
I probably didn't get the logic right, but basically what happens is that when an update is issued the code in the trigger replaces what actually happens in the update, so you just need the where clause to EXCLUDE those rows that are being updated that would violate the business rules.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 4, 2012 at 2:47 pm
I believe this worked. My update did not occur and I did not get the message I was getting before.
Thanks!!
April 4, 2012 at 3:00 pm
I assume you mean the INSTEAD OF trigger worked?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 5, 2012 at 12:51 pm
If you can implement this requirement as a table check constraint, then that would perhaps be the best solution, because it would involve no programming work arounds. For example, let's assume that the column in question is an enrollment status code that is logically tied to enrolled_date and disenrolled_date columns.
check
(
(disenrolled_date is null and enrolled_status = 'E')
or (disenrolled_date is not null and enrolled_status = 'D')
)
If the application should NEVER update this specific column, only insert rows and perhaps update other columns, then you can DENY UPDATE on the column to the application account.
deny update on
( [column] ) to ;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply