if object_id('dbo.pt__f1__trg', 'tr') is not null drop trigger dbo.pt__f1__trg;
go
create trigger dbo.pt__f1__trg on dbo.pt after insert, update
as begin
   set nocount on;
   if not exists (select 1 from inserted) return;
   if not exists (select 1 from deleted) or update(f1)
   begin
      delete t from dbo.pt__f1 as t join inserted as i on t.parent__id = i.recid;
      insert into dbo.pt__f1 (parent__id, word)
      select i.recid, w.word
      from inserted as i
      cross apply dbo.words_sb(i.f1, true) as w
      where i.f1 is not null and i.f1 <> '';
   end;
end;
go

if object_id('dbo.pt__f2_1__ins', 'tr') is not null drop trigger dbo.pt__f2_1__ins;
go
create trigger dbo.pt__f2_1__ins on dbo.pt after insert
as begin
   set nocount on;
   if not exists (select 1 from inserted) return;
   delete t from dbo.pt__f2_1 as t join inserted as i on t.parent__id = i.recid;
   ;with NewWords (parent__id, list__index, word) as (
      select i.recid as parent__id, 1 as list__index, w.word
         from inserted as i
         cross apply dbo.words_sb(i.f2_1, true) AS w
         where i.f2_1 is not null and i.f2_1 <> ''
      union all
      select i.recid as parent__id, 2 as list__index, w.word
         from inserted as i
         cross apply dbo.words_sb(i.f2_2, true) AS w
         where i.f2_2 is not null and i.f2_2 <> ''
      union all
      select i.recid as parent__id, 3 as list__index, w.word
         from inserted as i
         cross apply dbo.words_sb(i.f2_3, true) AS w
         where i.f2_3 is not null and i.f2_3 <> ''
      union all
      select i.recid as parent__id, 4 as list__index, w.word
         from inserted as i
         cross apply dbo.words_sb(i.f2_4, true) AS w
         where i.f2_4 is not null and i.f2_4 <> ''
      union all
      select i.recid as parent__id, 5 as list__index, w.word
         from inserted as i
         cross apply dbo.words_sb(i.f2_5, true) AS w
         where i.f2_5 is not null and i.f2_5 <> ''
   )
   insert into dbo.pt__f2_1 (parent__id, list__index, word)
   select parent__id, list__index, word from NewWords;
end;
go

if object_id('dbo.pt__f2_1__upd', 'tr') is not null drop trigger dbo.pt__f2_1__upd; go
create trigger dbo.pt__f2_1__upd on dbo.pt after update
as begin
   set nocount on;
   if update(f2_1)
   begin
      delete t from dbo.pt__f2_1 as t
      join inserted as i on t.parent__id = i.recid where t.list__index = 1;
      insert into dbo.pt__f2_1 (parent__id, list__index, word)
      select i.recid, 1, w.word
      from inserted as i
      cross apply dbo.words_sb(i.f2_1, true) as w
      where i.f2_1 is not null and i.f2_1 <> '';
   end;
end;
go

if object_id('dbo.pt__f2_2__upd', 'tr') is not null drop trigger dbo.pt__f2_2__upd; go
create trigger dbo.pt__f2_2__upd on dbo.pt after update
as begin
   set nocount on;
   if update(f2_2)
   begin
      delete t from dbo.pt__f2_1 as t
      join inserted as i on t.parent__id = i.recid where t.list__index = 2;
      insert into dbo.pt__f2_1 (parent__id, list__index, word)
      select i.recid, 2, w.word
      from inserted as i
      cross apply dbo.words_sb(i.f2_2, true) as w
      where i.f2_2 is not null and i.f2_2 <> '';
   end;
end;
go

if object_id('dbo.pt__f2_3__upd', 'tr') is not null drop trigger dbo.pt__f2_3__upd; go
create trigger dbo.pt__f2_3__upd on dbo.pt after update
as begin
   set nocount on;
   if update(f2_3)
   begin
      delete t from dbo.pt__f2_1 as t
      join inserted as i on t.parent__id = i.recid where t.list__index = 3;
      insert into dbo.pt__f2_1 (parent__id, list__index, word)
      select i.recid, 3, w.word
      from inserted as i
      cross apply dbo.words_sb(i.f2_3, true) as w
      where i.f2_3 is not null and i.f2_3 <> '';
   end;
end;
go

if object_id('dbo.pt__f2_4__upd', 'tr') is not null drop trigger dbo.pt__f2_4__upd; go
create trigger dbo.pt__f2_4__upd on dbo.pt after update
as begin
   set nocount on;
   if update(f2_4)
   begin
      delete t from dbo.pt__f2_1 as t
      join inserted as i on t.parent__id = i.recid where t.list__index = 4;
      insert into dbo.pt__f2_1 (parent__id, list__index, word)
      select i.recid, 4, w.word
      from inserted as i
      cross apply dbo.words_sb(i.f2_4, true) as w
      where i.f2_4 is not null and i.f2_4 <> '';
   end;
end;
go

if object_id('dbo.pt__f2_5__upd', 'tr') is not null drop trigger dbo.pt__f2_5__upd; go
create trigger dbo.pt__f2_5__upd on dbo.pt after update
as begin
   set nocount on;
   if update(f2_5)
   begin
      delete t from dbo.pt__f2_1 as t
      join inserted as i on t.parent__id = i.recid where t.list__index = 5;
      insert into dbo.pt__f2_1 (parent__id, list__index, word)
      select i.recid, 5, w.word
      from inserted as i
      cross apply dbo.words_sb(i.f2_5, true) as w
      where i.f2_5 is not null and i.f2_5 <> '';
   end;
end;
go

alter table pt__f1   drop constraint if exists pk__pt__f1;
alter table pt__f1   add constraint pk__pt__f1   primary key (parent__id, word);
if exists (select * from sys.indexes where name = 'idx__pt__f1') drop index idx__pt__f1;
create index idx__pt__f1 on pt__f1 (word);
alter table pt__f1   drop constraint if exists fk__pt__f1;
if exists (select * from sys.indexes where name = 'fkidx__pt__f1') drop index fkidx__pt__f1;
create index fkidx__pt__f1 on pt__f1 (parent__id);
alter table pt__f1
   add constraint fk__pt__f1
   foreign key (parent__id)
   references pt(recid)
   on delete cascade;

alter table pt__f2_1   drop constraint if exists pk__pt__f2_1;
alter table pt__f2_1   add constraint pk__pt__f2_1   primary key (parent__id, list__index, word);
if exists (select * from sys.indexes where name = 'idx__pt__f2_1') drop index idx__pt__f2_1;
create index idx__pt__f2_1 on pt__f2_1 (word);
alter table pt__f2_1   drop constraint if exists fk__pt__f2_1;
if exists (select * from sys.indexes where name = 'fkidx__pt__f2_1') drop index fkidx__pt__f2_1;
create index fkidx__pt__f2_1 on pt__f2_1 (parent__id);
alter table pt__f2_1
   add constraint fk__pt__f2_1
   foreign key (parent__id)
   references pt(recid)
   on delete cascade;

