I have an instead of trigger. I have a value in a variable.
I would like to insert the value into one of the columns and use the fields from inserted. This is what I am trying to accomplish:
declare @someLocalVariable varchar(9)
set @someLocalVariable = dbo.someLocalUDF()
INSERT myTable (field1, field2, field3)
VALUES (@someLocalVariable, (select field2, field3 from inserted))
INSERT INTO myTable (
field1,
field2,
field3
) SELECT
@someLocalVariable,
field2,
field3
FROM
INSERTED
Just remember that if this was triggered by a batch insert (i.e. INSERTED has more than one record), all records will also be inserted into the target table.
(Which is exactly what it should do IMHO, but for some reason people usually forget about that.)
Edit
The OP has forgotten to say what his question was really about: generating unique primary key values. So, in that case, one way of doing that would be something like this:
CREATE TRIGGER
dbo.InsteadOfInsertMyTable
ON MyTable
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO myTable (
primaryKeyField,
field2,
field3
) SELECT
dbo.someUniqueValueGeneratorUDF(),
field2,
field3
FROM
INSERTED
END
GO
