SQL Trigger with loop from another table

Question

I've got 2 tables :

Table A

create table A
(id int identity(1,1)
,KeyWord1 nvarchar(50)
,KeyWord2 nvarchar(50)
,KeyWord3 nvarchar(50)
)

Table B

Create table B
(id int identity(1,1)
,Address nvarchar(150)
,Chk int
)

The table A contains the following values :

insert into A
values
(166, 'elyse', 'vry')
,(243, 'virginia', 'beach')
,(134, 'aris', 'adium')

The Table B contains the following values :

insert into B
values
('35 stadium street, 134 Paris', null)
,('243, Stadiumù road from Paris', null)

My goal is to set the column B.chk with the number of occurence from table A when a new row is created on table B.

For example :

insert into B
values
('166, road to Champs elysee - 14215 Cdx Evry', null)

The trigger on table B should set B.Chk at 1 cause the following query is matching with the table A

select count(*) from inserted
where b.Address like '%166%'
and b.Address like '%elyse%'
and b.Address like '%vry%'

For this i should create a trigger on Table B but don't know how to "loop" on the other table. Should i user for, while or a cursor ?

Answer 1

You was right, i don't need a loop but chk column need to be set immediately after insert.

Here is my trigger :

 Create trigger TR_Update_B
 ON dbo.B
 AFTER insert
 AS
 BEGIN

 SET NOCOUNT ON;

 DECLARE @docNo int
 DECLARE @Result int

 SELECT @DocNo=id FROM inserted;

 SET @Result= (select count(*) from b
 join a
 on b.Address like '%'+a.KeyWord1+'%' 
 and b.Address like '%'+a.KeyWord2+'%' 
 and b.Address like '%'+a.KeyWord3+'%' 
 where b.id=@DocNo)

 Update b set Chk = @Result where id=@DocNo

 END