Insert Trigger Question 1

[jimmythegeek]

I need an insert trigger that checks a value in one field and if it is a certain string, then change it to another value. I need to change the string "Required Selection" to a corresponding foreign key value. So here is the pseudo-code (the name of the field is "answer").


If inserted.answer = "Required Selection"
set inserted.answer = 61


Hopefully this is not a stupid question. I'm also not sure whether this should be an "After Insert" trigger or not. I don't need any of the create trigger stuff, I just need the proper syntax for the instruction. Thanks in advance for any help.

Jim Lunde
We all agree your theory is crazy, but is it crazy enough?

 

[JamesLean]

UPDATE t
SET answer = '61'
FROM inserted i JOIN mytable t ON i.id = t.id
WHERE i.answer = 'required selection'

You cannot update the inserted table.

--James

 

[SQLSister]

Let me ask. what datatype is the answer field? If it is integer, you should change the way data is input rather than handle this with a trigger. This would be more efficent than a trigger. Also it probably would not run the trigger becasue it couldn't put the value into the existing datatype. So I beleive it would error before getting to the trigger. That might not be true with an instead of trigger, I've never tested that because I always make sure I insert the right dat type of the information.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[jimmythegeek]

The datatype is varchar. The programmer said (for some reason which I wasn't going to get into), that he would have a hard time sending me the correct id, and the app is in production now, so while I would not typically do it this way, I figured it was the easiest solution.

Thank you both.

Jim Lunde
We all agree your theory is crazy, but is it crazy enough?