asked
Trigger to identify when certain value has changed, then change that value to another value
This may be an odd question, but hopefully someone can help me out. I am currently working with a game that is very database heavy, using MS SQL 2000. In the game, once you achieve a certain quest, a value in the table tblGameID1 is updated in the StoryQuestState to 65535. What I have been trying to create with no success is a trigger that, when that value changes to 65535, it updates again to -1. I've tried a simple UPDATE tblGameID1 SET StoryQuestState = -1 WHERE StoryQuestState = 65535, but that didn't work. I also tried some variations of that with a IF EXISTS statement in there. I'm extremely new to SQL and honestly, I'm not sure if this is even possible. I'll need this trigger to identify when 65535 is present in an ever expanding column and update it to -1. This can happen at any time. Anyway, I appreciate any help you can provide. Thanks!
Hi, what you try might be a case for a *INSTEAD OF trigger*. Although you have an older version of SQL Server, you have luck. In SQL 2000 the Instead-Of-Trigger was a new Feature:
In the case, a inserted Value could be 65535, you need another trigger: INSTEAD OF INSERT. key1 and key2 represent the primary key (I don't know how it is in your table). The set-statement contains just one column to update. You have to add the others, too. HTH Dennis
CREATE TRIGGER tr_test
ON tblGameID1
INSTEAD OF UPDATE
AS
BEGIN
SET NOCOUNT ON;
update
tblGameID1
set
StoryQuestState = case when i.StoryQuestState = 65535 then -1 else i.StoryQuestState end
from
tblGameID1 a
inner join
INSERTED i
on a.key1 = i.key1 and a.key2 = i.key2
END
In the case, a inserted Value could be 65535, you need another trigger: INSTEAD OF INSERT. key1 and key2 represent the primary key (I don't know how it is in your table). The set-statement contains just one column to update. You have to add the others, too. HTH Dennis
13 People are following this question.
