sql after insertion trigger update column within the same table

Question

im trying write a trigger to update a column in the same table when an insertion is done

my table is

create table BlueTooth (
ID int Primary Key NOT NULL ,
version varchar(45) NOT NULL,
score float null,
speed float 
);

my trigger is

create or replace trigger BlueToothsc
ON BlueTooth 
after insert 
as 
begin 
update BlueTooth set score((select speed from inserted)/(select max(speed) from BlueTooth ) * 100)
END 
GO

but i get errors in "incorrect syntax near" "or" keyword in the first line and "incorrect syntax near" "as"

can some one please help me with this

I want to update the BlueTooth tables score column by (inserted speed / max(speed) ) * 100)

when a faster version of bluetooth came in that should the the score value 100 and all the others should get a lower value respectively.

Answer 1

Instead of storing the score, prefer instead to calculate it at the point of query. So, in this case, a view should do the job:

create table BlueTooth (
ID int Primary Key NOT NULL ,
version varchar(45) NOT NULL,
speed float 
);
go
create view BlueToothScores
as
   select
       ID,
       version,
       speed,
       speed * 100 / MAX(speed) OVER () as score
   from
       BlueTooth

And now you don't need to worry about triggers or update routines - the results are always correct because they're calculated from the real data