Edit inserted table sql-CodePudding

When insert I need edit a value if it is null. I create a trigger but I don't know how to edit inserted table.

Error

ALTER TRIGGER [trigger1] on [dbo].[table]
instead of insert
as
    declare @secuencia bigint, @ID_PERSONA VARCHAR;

    select @secuencia = SECUENCIA from inserted 
    select @ID_PERSONA = ID_PERSONA from inserted 

    if @secuencia is null begin         
        set inserted.SECUENCIA = NEXT VALUE FOR SEQ_BIOINTEG --(Sequence)          
    end

CodePudding user response：

i dont know how to edit inserted table.

You do not. That table is read only.

Note how your trigger also says:

instead of insert

There is no way to edit the inserted table.

What you do instead, is setting up an INSERT command for the original table, using the data from the inserted table to filter to the ROWS of inserted - mostly by a join.

Changing inserted makes no sense, logically - because triggers in SQL are one of two things:

INSTEAD OF - then there is no actual insert happening for inserted to start with. Instead of doing the insert, the trigger is called. As such, changing inserted - makes no sense.
AFTER - then the insert already happened (and you UPDATE the rows). As the trigger runs after the update, changing inserting makes no sense.

Note that I say ROWS - your trigger has one very basic error: it assumes inerted contains ONE row. It is a table - it is possible the changes come from an insert statement that inserts multiple rows (which is trivial, i.e. select into, or simply an insert with values for multiple rows). Handle those.

select @ID_PERSONA = ID_PERSONA from inserted

Makes NO sense - inserted is a table, so ID_PERSONA from inserted contains what value, if 2 rows are inserted? You must treat inserted like any other table.

CodePudding user response：

Apart from all the varied issues with your trigger code, as mentioned by others, the easiest way to use a SEQUENCE value in a table is to just put it in a DEFAULT constraint:

ALTER TABLE dbo.[table]
ADD CONSTRAINT DF_table_seq
  DEFAULT (NEXT VALUE FOR dbo.SEQ_BIOINTEG)
  FOR SECUENCIA;