CREATE TABLE [dbo].[SUDFLabels]
(
[SUDFLabelPriKey] [int] NOT NULL IDENTITY(1, 1),
[UserDefinedField] [int] NOT NULL,
[MoveTypePriKey] [int] NOT NULL,
[Label] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

create trigger [dbo].[SUDFLabelsDeleteTrigger] on [dbo].[SUDFLabels]
after delete
as
set nocount on

-- This trigger does work on multi-row deletes.  If a move type row does not exist, we can just ignore.
update SUDFMoveTypeLabels set
    UserDefinedLabel1 = null
from deleted
inner join SUDFMoveTypeLabels on SUDFMoveTypeLabels.MoveTypePriKey = deleted.MoveTypePriKey
where deleted.UserDefinedField = 1

update SUDFMoveTypeLabels set
    UserDefinedLabel2 = null
from deleted
inner join SUDFMoveTypeLabels on SUDFMoveTypeLabels.MoveTypePriKey = deleted.MoveTypePriKey
where deleted.UserDefinedField = 2

update SUDFMoveTypeLabels set
    UserDefinedLabel3 = null
from deleted
inner join SUDFMoveTypeLabels on SUDFMoveTypeLabels.MoveTypePriKey = deleted.MoveTypePriKey
where deleted.UserDefinedField = 3

update SUDFMoveTypeLabels set
    UserDefinedLabel4 = null
from deleted
inner join SUDFMoveTypeLabels on SUDFMoveTypeLabels.MoveTypePriKey = deleted.MoveTypePriKey
where deleted.UserDefinedField = 4

update SUDFMoveTypeLabels set
    UserDefinedLabel5 = null
from deleted
inner join SUDFMoveTypeLabels on SUDFMoveTypeLabels.MoveTypePriKey = deleted.MoveTypePriKey
where deleted.UserDefinedField = 5

update SUDFMoveTypeLabels set
    UserDefinedLabel6 = null
from deleted
inner join SUDFMoveTypeLabels on SUDFMoveTypeLabels.MoveTypePriKey = deleted.MoveTypePriKey
where deleted.UserDefinedField = 6

update SUDFMoveTypeLabels set
    UserDefinedLabel7 = null
from deleted
inner join SUDFMoveTypeLabels on SUDFMoveTypeLabels.MoveTypePriKey = deleted.MoveTypePriKey
where deleted.UserDefinedField = 7

update SUDFMoveTypeLabels set
    UserDefinedLabel8 = null
from deleted
inner join SUDFMoveTypeLabels on SUDFMoveTypeLabels.MoveTypePriKey = deleted.MoveTypePriKey
where deleted.UserDefinedField = 8

delete from SUDFMoveTypeLabels
from deleted
inner join SUDFMoveTypeLabels on SUDFMoveTypeLabels.MoveTypePriKey = deleted.MoveTypePriKey
where SUDFMoveTypeLabels.UserDefinedLabel1 is null and
    SUDFMoveTypeLabels.UserDefinedLabel2 is null and
    SUDFMoveTypeLabels.UserDefinedLabel3 is null and
    SUDFMoveTypeLabels.UserDefinedLabel4 is null and
    SUDFMoveTypeLabels.UserDefinedLabel5 is null and
    SUDFMoveTypeLabels.UserDefinedLabel6 is null and
    SUDFMoveTypeLabels.UserDefinedLabel7 is null and
    SUDFMoveTypeLabels.UserDefinedLabel8 is null
GO

create trigger [dbo].[SUDFLabelsUpsertTrigger] on [dbo].[SUDFLabels]
after update,insert
as
set nocount on

-- This trigger does work on multi-row actions.
-- Add a new SUDFMoveTypeLabels row for the move type, unless it already exists.
insert into SUDFMoveTypeLabels( MoveTypePriKey )
select distinct inserted.MoveTypePriKey
from inserted
except
select MoveTypePriKey
from SUDFMoveTypeLabels

-- Now, since all possible rows now exist, we can update the appropriate columns.
update SUDFMoveTypeLabels set
    UserDefinedLabel1 = inserted.Label
from inserted
inner join SUDFMoveTypeLabels on SUDFMoveTypeLabels.MoveTypePriKey = inserted.MoveTypePriKey and
    inserted.UserDefinedField = 1

update SUDFMoveTypeLabels set
    UserDefinedLabel2 = inserted.Label
from inserted
inner join SUDFMoveTypeLabels on SUDFMoveTypeLabels.MoveTypePriKey = inserted.MoveTypePriKey and
    inserted.UserDefinedField = 2

update SUDFMoveTypeLabels set
    UserDefinedLabel3 = inserted.Label
from inserted
inner join SUDFMoveTypeLabels on SUDFMoveTypeLabels.MoveTypePriKey = inserted.MoveTypePriKey and
    inserted.UserDefinedField = 3

update SUDFMoveTypeLabels set
    UserDefinedLabel4 = inserted.Label
from inserted
inner join SUDFMoveTypeLabels on SUDFMoveTypeLabels.MoveTypePriKey = inserted.MoveTypePriKey and
    inserted.UserDefinedField = 4

update SUDFMoveTypeLabels set
    UserDefinedLabel5 = inserted.Label
from inserted
inner join SUDFMoveTypeLabels on SUDFMoveTypeLabels.MoveTypePriKey = inserted.MoveTypePriKey and
    inserted.UserDefinedField = 5

update SUDFMoveTypeLabels set
    UserDefinedLabel6 = inserted.Label
from inserted
inner join SUDFMoveTypeLabels on SUDFMoveTypeLabels.MoveTypePriKey = inserted.MoveTypePriKey and
    inserted.UserDefinedField = 6

update SUDFMoveTypeLabels set
    UserDefinedLabel7 = inserted.Label
from inserted
inner join SUDFMoveTypeLabels on SUDFMoveTypeLabels.MoveTypePriKey = inserted.MoveTypePriKey and
    inserted.UserDefinedField = 7

update SUDFMoveTypeLabels set
    UserDefinedLabel8 = inserted.Label
from inserted
inner join SUDFMoveTypeLabels on SUDFMoveTypeLabels.MoveTypePriKey = inserted.MoveTypePriKey and
    inserted.UserDefinedField = 8
GO
ALTER TABLE [dbo].[SUDFLabels] ADD CONSTRAINT [PK_SUDFLabels] PRIMARY KEY CLUSTERED  ([SUDFLabelPriKey]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SUDFLabels] ADD CONSTRAINT [IX_SUDFLabels_MoveType_UserDefinedField] UNIQUE NONCLUSTERED  ([MoveTypePriKey], [UserDefinedField]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SUDFLabels] ADD CONSTRAINT [FK_SUDFLabels_MoveType] FOREIGN KEY ([MoveTypePriKey]) REFERENCES [dbo].[MoveType] ([PriKey])
GO
ALTER TABLE [dbo].[SUDFLabels] ADD CONSTRAINT [FK_SUDFLabels_UserDefinedField] FOREIGN KEY ([UserDefinedField]) REFERENCES [dbo].[SUDFNumber] ([SUDFNumberID])
GO
GRANT SELECT ON  [dbo].[SUDFLabels] TO [MssExec]
GRANT INSERT ON  [dbo].[SUDFLabels] TO [MssExec]
GRANT DELETE ON  [dbo].[SUDFLabels] TO [MssExec]
GRANT UPDATE ON  [dbo].[SUDFLabels] TO [MssExec]
GO