USE Кладбище
GO

CREATE TRIGGER InsertGrave
ON Grave
AFTER INSERT
AS
DECLARE @POSITION  int; -- id могилы
SET @POSITION = @@IDENTITY;
DECLARE @CoffinID int; 
SET @CoffinID = (SELECT Grave.coffin_id FROM grave where grave.id = @POSITION)
DECLARE @AREA1 int; --пдлощадь гроба
SET @AREA1 = (SELECT coffin.area FROM coffin WHERE coffin.id = @CoffinID)
DECLARE @POSITION1  int; --id кладбища
SET @POSITION1 = (SELECT grave.grave_id FROM grave WHERE grave_id = @POSITION)
DECLARE @AREA int; --площадь кладбища
SET @AREA = (SELECT Graveyard.area FROM Graveyard WHERE Graveyard.id = @POSITION1)
IF @AREA > @AREA1
	BEGIN
	UPDATE Graveyard
	SET area = area - @AREA1
	Select * FROM Graveyard WHERE Graveyard.id = @POSITION1
END
ELSE
	DECLARE @i INT;
	SET @i = 1;
	DECLARE @i1 INT;
	SET @i1 = 0;
	WHILE @i1 = 0
		BEGIN
		DECLARE @AREA2 INT;
		SET @AREA2 = (SELECT Graveyard.area FROM Graveyard WHERE Graveyard.id = @i)
		IF @AREA2 > @AREA1
			BEGIN
			UPDATE Graveyard
			SET area = area - @AREA1
			Select * FROM Graveyard WHERE Graveyard.id = @i


			UPDATE Grave
			SET grave_id = @i;
			Select * FROM Grave WHERE Grave.id = @POSITION

			set @i1 = 1; 
		END
		SET @i = @i + 1;
	END
go