I have a table that has the following columns, and I cannot modify the schema (i.e. I can't modify the table or add an identity field).
What I want is to create a trigger to update one column to treat it as an identity field.
accountno firstname lastname keyField
jku45555 John Doe 123
Now what I want is when I insert the next record, I grab the KeyFieldId of the previous record and update the newly inserted record to be 124 (keep in mind this a Varchar field).
I need the best possible way of doing this, and like I said modifying the table is not an option. Thanks!
You want to do something like this... For a table named "Foo", with two columns, First Name and KeyFieldId (both varchar), this trigger will do what you want:
-------------------------------------------------------------------------
-- These lines will create a test table and test data
--DEBUG: CREATE TABLE Foo (FirstName varchar(20), KeyFieldId varchar(10))
--DEBUG: INSERT INTO Foo VALUES ('MyName', '145')
--
CREATE TRIGGER test_Trigger ON Foo
INSTEAD OF INSERT
AS
BEGIN
DECLARE @maxKeyFieldId int;
SELECT @maxKeyFieldId = MAX(CAST(KeyFieldId AS int)) FROM Foo;
WITH RowsToInsert AS (
SELECT *, ROW_NUMBER() OVER (ORDER BY (CAST(KeyFieldId AS int))) AS RowNum
FROM inserted
) INSERT INTO Foo (FirstName, KeyFieldId)
SELECT FirstName, @maxKeyFieldId + RowNum
FROM RowsToInsert;
END
Things to note here: