IF EXISTS (	SELECT	1
			FROM	sys.triggers
			WHERE	object_id = OBJECT_ID(N'[dbo].[per_bez_gen_values]') )
BEGIN
	DROP TRIGGER [dbo].[per_bez_gen_values]
END;
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER	[dbo].[per_bez_gen_values]
	ON			[dbo].[per_bez]
	FOR INSERT, UPDATE
AS
BEGIN
	SET NOCOUNT ON;

	WITH t0(typ,sortierung) AS (
		--Hilfstabelle für Sortierung Typ
		SELECT 'Anrede',1 UNION ALL
		SELECT 'Rufname',2 UNION ALL
		SELECT 'Vorname',3 UNION ALL
		SELECT 'Spitzname',4 UNION ALL
		SELECT 'Nachname',5 UNION ALL
		SELECT 'Geburtsname',6 UNION ALL
		SELECT 'Handelsregister-Beruf',7 UNION ALL
		SELECT 'Handelsregister-Ort',8 UNION ALL
		SELECT 'Mandat',9
		), t1(fk_per,typ,offset) AS (
		--Anzahl der bereits vorhandenen, konkurierenden Einträge zu betroffenen FKs die jeweils vor den neuen Einträgen stehen sollen
		SELECT	per_bez.fk_per,
				per_bez.typ,
				sum(count(per_bez.pk)) OVER (ORDER BY t0.sortierung) AS anzahl
		FROM	per_bez
		INNER JOIN INSERTED i
		ON		per_bez.fk_per = i.fk_per
		INNER JOIN t0
		ON		per_bez.typ = t0.typ
		GROUP BY per_bez.fk_per,per_bez.typ,t0.sortierung
		), t2(pk,sortierung) AS (
		SELECT	per_bez.pk,
				ROW_NUMBER() OVER (	PARTITION BY per_bez.fk_per ORDER BY t0.sortierung,(	CASE
																							WHEN	d.pk IS NULL													--Datensatz ist neu (LEFT JOIN DELETED WHERE DELETED ist nicht existent)
																							AND		i.pk IS NOT NULL
																							THEN	convert(INT,isnull(per_bez.sortierung,1)) * 10 + t1.offset * 10	--Sortierung der neuen Datensätze + Anzahl vorhandener Datensätze damit sich neue Datensätze hinten anschließen
																							WHEN	d.pk IS NOT NULL												--Datensatz wird geändert (vergleichbar mit INNER JOIN DELETED entspricht allen geänderten Datensätzen)
																							AND		i.pk IS NOT NULL
																							AND		isnull(i.sortierung,1) < isnull(d.sortierung,1)					--Datensatz steigt auf
																							THEN	convert(INT,isnull(per_bez.sortierung,1)) * 10 - 5				--Datensatz muss den bisher auf der Position befindlichen Datensatz verdrängen, also davor eingeordnet werden
																							WHEN	d.pk IS NOT NULL
																							AND		i.pk IS NOT NULL
																							AND		isnull(i.sortierung,1) > isnull(d.sortierung,1)					--Datensatz steigt ab
																							THEN	convert(INT,isnull(per_bez.sortierung,1)) * 10 + 5				--Datensatz muss den bisher auf der Position befindlichen Datensatz verdrängen, also dahinter eingetragen werden
																							WHEN	i.pk IS NULL													--Datensatz wurde nicht geändert, muss aber mit sortiert werden
																							THEN	convert(INT,isnull(per_bez.sortierung,1)) * 10
																							END )) AS sortierung
		FROM	per_bez
		--Alle Datensätze von betroffenen FKs aktuallisieren
		INNER JOIN (	SELECT	fk_per
						FROM	INSERTED
						GROUP BY fk_per ) t3
		ON		per_bez.fk_per = t3.fk_per
		INNER JOIN t0
		ON		per_bez.typ = t0.typ
		LEFT JOIN INSERTED i
		ON		per_bez.pk = i.pk
		LEFT JOIN DELETED d
		ON		per_bez.pk = d.pk
		LEFT JOIN t1
		ON		per_bez.fk_per = t1.fk_per
		AND		per_bez.typ = t1.typ
		)
	UPDATE	per_bez
	SET		per_bez.sortierung = t2.sortierung,
			per_bez.lasttrigger = 'per_bez_gen_values*a'
	FROM	per_bez
	INNER JOIN t2
	ON		per_bez.pk = t2.pk
	WHERE	isnull(per_bez.sortierung,0) != t2.sortierung;
END;
GO