How to correctly create a trigger in MS SQL?

Question

SQL Server

Hello!
There are two tables:
table1(Id, BIN) and table2(Id, t1_Id, BIN)
I need to check BIN at insert in table2. If there is one in the first table (table1),
then I write its Id to table2 (table2) in the t1_Id field.
Here is my code:

go
alter trigger hello on dbo.Employees
after insert
as
declare @newBin bigint;
declare @t1_id bigint;
begin
  select @newBin = BIN from inserted i;
  select @t1_id = (select t.Id from table1 t where t.BIN = @newBin);
        if(@t1_id!=' ')
        begin
  update table2 set [email protected]_id;
        end
end
go

Everything works, there is one but. When updating the table, it updates all t1_id fields.
And it is necessary to update only the added one. How to do?

Answer 1

if updated(field_name)
well, you should keep in mind that in the general case there may be more than one record in the inserted, so the general outline should be something like this:

declare cur_trigger cursor for select ... from inserted 
open cur_trigger
fetch cur_trigger into @переменные 
while @@fetch_status=0 begin
              ... тут сами действия для одной из вставленных строк, включая проверки "измененности" и т.п.
               fetch cur_trigger into @переменные 
end 
close cur_trigger
deallocate cur_trigger

well, or to taste a cycle through records without a cursor

Answer 2

table1(Id, BIN) and table2(Id, t1_Id, BIN)

Create a unique BIN index on table1(Id, BIN) and add all table2.BIN records.

Answer 3

You need to start with the fact that the trigger is not needed here. If you also do the first option, which you were advised, then this is generally a nightmare, because there is also a cursor in the trigger. Never make a cursor in a trigger.
The second option is smaller, but also a nightmare.
"Criticizing - offer."
I propose. To check if there is such an entry in the second table - you need to use a simple foreign key. Those. You just need to link the tables, the field should not be NULL and NOTHING more, no triggers should be fenced.