Geek Answers Handbook

INSERTED and DELETED logical tables cannot be updated

When I use the following instruction

update INSERTED set ...

I get the following error:

INSERTED and DELETED logical tables cannot be updated

This is the startup code:

create trigger TCalcul
on dbo.Calcul
after insert 
  as
  begin
   set nocount on;
   declare @Num1 int;
   declare @Num2 int;
   declare @Op varchar(1);
   set @Num1 = (select Num1 from inserted)
   set @Num2 = (select Num2 from inserted)
   set @Op = (select Op from inserted)
   if @Op = '+'
    update inserted set Resultat = @Num1 + @Num2 
    else if @Op = '-'
     update inserted set Resultat = @Num1 - @Num2 ;
         else if @Op = '*'
         update inserted set Resultat = @Num1 * @Num2 ;
              else if @Op = '/'
              update inserted set Resultat = @Num1 / @Num2 ;
  end
go
+5
source share
1 answer

As the error says, you cannot change the inserted. The Calcul table will already contain the rows represented by the insert at the time the trigger is called; therefore, you are directly working with this data. Since it is possible to insert several lines at the same time, you should not work with local variables, but work with a set:

create trigger TCalcul
on dbo.Calcul
after insert 
as
begin

   set nocount on

   update Calcul 
          set Resultat = case Calcul.Op 
                              when '+' then Calcul.Num1 + Calcul.Num2
                              when '-' then Calcul.Num1 - Calcul.Num2
                              when '*' then Calcul.Num1 * Calcul.Num2
                              when '/' then Calcul.Num1 / Calcul.Num2
                              else null end
         from Calcul inner join Inserted on Calcul.ID = Inserted.ID
end
go

If you cannot use the set for any reason, you should use the cursor to jump through the inserted lines.

. , Calcul ID; .

EDIT:

SET NOCOUNT ON Sql Server , , . , , (, , ). , .

+6

More articles:


All Articles