SQL Server insert triggers

[SarahEmm]

I want to use an insert trigger to take data from one field, modify it, then insert it into another field in the same row.

I can do this with interbase, but I cannot figure out if it even possible in SQL Server. Can anyone help?

FYI, this is what I have tried so far:

CREATE TRIGGER SetMyNewCode
ON MyTable
AFTER INSERT AS
BEGIN
DECLARE @vNewCode VARCHAR(7)
, @vOldCode VARCHAR(5)
-- only act if code has been passed
IF UPDATE (MyOldCode)
BEGIN
SET @vOldCode = (SELECT MyOldCode FROM Inserted)
-- work out the new code
EXECUTE MakeANewCode
@OldCode = @vOldCode
, @NewCode = @vNewCode OUTPUT
INSERT INTO Inserted
( NewCode
)
VALUES ( @vNewCode
)
END
END

[skhanal]

You can't insert a row into INSERTED table, you have to UPDATE the base table with newcode value.

[SarahEmm]

thx a lot - I've got it now!

I had assumed that it would be very similar to Interbase - not in this case!