CODEWITHSUNDEEP
sqlsql-servert-sqltriggers
tim
tim

Reputation: 1369

sql server instead of trigger insert statement with variables and values from inserted

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))

Upvotes: 2

Views: 1974

Answers (1)

rsenna
rsenna

Reputation: 11964

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

Upvotes: 7

Related Questions