CREATE TRIGGER	dbo.trgPlaces
ON		dbo.Places
AFTER		DELETE
AS

SET NOCOUNT ON

IF EXISTS	(
			SELECT		*
			FROM		deleted AS d
			INNER JOIN	(
						SELECT		MIN(ID) AS minID
						FROM		dbo.Places
						GROUP BY	Name,
								Town
					) AS w ON w.minID = d.ID
		)
	BEGIN
		ROLLBACK TRAN
		RAISERROR('You are not allowed to delete the first version.', 16, 1)
	END
GO

CREATE VIEW dbo.vwLatestVersions
AS

WITH cteSource(ID, Name, Town, Latitude, Longitude, Etc1, Etc2, [Description], rn)
AS (
	SELECT	ID,
		Name, 
		Town, 
		Latitude, 
		Longitude, 
		Etc1, 
		Etc2, 
		[Description],
		ROW_NUMBER() OVER (PARTITION BY Name, Town ORDER BY ID DESC) AS rn
	FROM	dbo.Places
)
SELECT	ID,
	Name, 
	Town, 
	Latitude, 
	Longitude, 
	Etc1, 
	Etc2, 
	[Description]
FROM	cteSource
WHERE	rn = 1
GO

CREATE TABLE	dbo.Places
		(
			Validated TINYINT NOT NULL,
			ID INT IDENTITY(1, 1) CONSTRAINT PK_Places PRIMARY KEY CLUSTERED,
			Name NVARCHAR(100) NOT NULL,
			Town NVARCHAR(100) NOT NULL,
			Latitude DECIMAL(9, 6),
			Longitude DECIMAL(9, 6)
		);
GO
ALTER TABLE	dbo.Places
ADD		DEFAULT (0)
FOR		Validated
GO
CREATE VIEW	dbo.vwPlaces
AS

-- Only display the latest version of validated rows
WITH cteSource(ID, Name, Town, Latitude, Longitude, rn)
AS (
	SELECT	ID, 
		Name, 
		Town, 
		Latitude, 
		Longitude,
		ROW_NUMBER() OVER (PARTITION BY Name, Town ORDER BY ID DESC) AS rn
	FROM	dbo.Places
	WHERE	Validated = 1
)
SELECT	ID, 
	Name, 
	Town, 
	Latitude, 
	Longitude
FROM	cteSource
WHERE	rn = 1
GO
CREATE TRIGGER	dbo.trgUpdatePlaces
ON		dbo.Places
INSTEAD OF	UPDATE
AS

SET NOCOUNT ON;

-- Prevent UPDATE and make the new version an INSERT instead.
INSERT	dbo.Places
	(
		Name,
		Town,
		Latitude,
		Longitude
	)
SELECT	Name,
	Town,
	Latitude,
	Longitude
FROM	inserted
WHERE	Name IS NOT NULL
	AND Town IS NOT NULL
GO

-- Test!
SELECT * FROM dbo.Places;
INSERT dbo.Places(Name, Town) VALUES ('Petersplatsen', 'Bjuv');
SELECT * FROM dbo.Places;
INSERT dbo.Places(Name, Town, Latitude) VALUES ('Petersplatsen', 'Bjuv', 12);
SELECT * FROM dbo.Places;
UPDATE dbo.Places SET Latitude=  12.56 WHERE ID = 2;
SELECT * FROM dbo.Places;

GO
-- Cleanup
DROP VIEW dbo.vwPlaces
DROP TABLE dbo.Places
GO