HWA3_P2_1.sql

/* -------------------------------------------------------------------------
   Jade Cheng
   ICS 321 Assignment 3
   December 12, 2008

   PART 2.1
   Create Insert, Update and delete stored procedures, with required
   parameters for the following tables:

     - PERSON
     - FULL_TIME
     - PART_TIME
     - BADGE
     - PERSON_SKILL
   ------------------------------------------------------------------------- */

USE DB9975
GO

-- NOTE: The Insert and Update scripts are implemented in parts 2.2 and 3.1.

CREATE PROCEDURE DeletePersonInstance(@Id INT) AS

	-- Lock the database in here.  Use the most restrictive lock because speed
	-- is not an issue.
	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
	BEGIN TRANSACTION

	-- Verify the person exists.
	DECLARE @TempPersonTable TABLE ( one INT )
	INSERT INTO @TempPersonTable SELECT TOP 1 1 FROM PERSON WHERE Id = @Id;
	IF @@ROWCOUNT <> 1
	BEGIN
		ROLLBACK TRANSACTION
		RAISERROR('Person does not exist.', 16, -1, @Id)
		RETURN 50000
	END

	-- Verify the person has not issued anyone a badge.  If the person has,
	-- then deleting this person would result in an inconsistent database:
	-- BADGE.IssuingPerson would link to a non-existent PERSON.Id.
	SELECT TOP 1 1 FROM BADGE WHERE IssuingPerson = @Id;
	IF @@ROWCOUNT <> 0
	BEGIN
		ROLLBACK TRANSACTION
		RAISERROR('Cannot delete badge issuers.', 16, -1, @Id)
		RETURN 50000
	END

	DELETE FROM FULL_TIME
	WHERE Id = @Id;

	DELETE FROM PART_TIME
	WHERE Id = @Id;

	DELETE FROM PERSON_DESIGNATE
	WHERE PersonId = @Id;

	DELETE FROM PERSON_PROFILE
	WHERE PersonId = @Id;

	DELETE FROM PERSON_SKILL
	WHERE PersonId = @Id;

	DELETE FROM PROJECT_ASSIGNMENT
	WHERE PersonId = @Id;

	DECLARE @BadgeId INT
	SELECT @BadgeId = Badge FROM PERSON WHERE Id = @Id;

	DELETE FROM PERSON
	WHERE Id = @Id;

	DELETE FROM BADGE
	WHERE Id = @BadgeId;

	COMMIT TRANSACTION
GO