home / study / engineering / computer science / computer science questions and answers / please do not spam answer me like other chegg people ...

Question: Please do not spam answer me like other chegg peop...

Please do not spam answer me like other chegg people have. the question is not complicated but does require you to read the SQL table which I have created and supplied to answer the question. (provided below the question) IF you are NOT capable of answering please do not answer it, leave it for someone who can actually help me and not use up 1 of my paid questions.. I really appreciate the last question help from CHEGG EXPERT "Anonymous who has answered 2,421 answers..

********************Question to be answered below using the Table cod which i have provided beneath the question... THANK YOU SO MUCH!! This will help me so much to understand !

4A. Assume today is (July 27, 2017 at 2PM)*. Anita is due to check out today (from Part 1 above). Write an Update Statement to change the status of her Folio to 'CheckedOut. (Be careful to include a WHERE clause so ONLY here folio is updated). Note: This should automatically invoke tr_UpdateFolio above (factoring in the late charge), which automatically invokestr_GenerateBill above, and calls dbo.ProduceBill , and produces a bill.

*To simulate the current time as July 27, 2017 at 2PM, you will need to hard code the date value '7/27/2017 2:00:00 PM'.

4B. Assume today is (July 27, 2017 at 10AM)*. Write an Update Statement to change the status of her Folio to 'CheckedOut. Note: This should automatically invoke tr_UpdateFolio above (NOT factoring in the late charge), which automatically invokes tr_GenerateBill above, and callsdbo.ProduceBill , and produces a bill.

*To simulate the current time as July 27, 2017 at 10AM, you will need to hard code the date value '7/27/2017 10:00:00 AM'.

4C. Assume today is (July 27, 2017 at 6PM)*. Write an Update Statement to change the status of her Folio to 'C'heckedOut.

Note: This should automatically invoke tr_UpdateFolio above (NOT factoring in the late charge), which automatically invokes tr_GenerateBill above, and calls dbo.ProduceBill , and produces a bill.

*To simulate the current time as July 27, 2017 at 6PM, you will need to hard code the date value '7/27/2017 6:00:00 PM'.

4D. Run a SELECT statement on the Billing Table showing your Triggers worked.

**************************************************************************************** USE DATABASE WRITTEN BELOW TO HELP ANSWER THE QUESTION THANK YOU SO MUCH !!!

USE FARMS

CREATE TABLE RESERVATION

(

ReservationID smallint NOT NULL IDENTITY(5000,1),

ReservationDate date NOT NULL,

ReservationStatus char(1) NOT NULL,

ReservationComments varchar(200) NULL,

CreditCardID smallint NOT NULL

)

CREATE TABLE GUEST

(

GuestID smallint NOT NULL IDENTITY(1500,1),

GuestFirst varchar(20) NOT NULL,

GuestLast varchar(20) NOT NULL,

GuestAddress varchar(30) NOT NULL,

guestAddress2 varchar(10) null,

GuestCity varchar(20) NOT NULL,

GuestState char(2) NULL,

GuestPostalCode char(10) NOT NULL,

GuestCountry varchar(20) NOT NULL,

GuestPhone varchar(20) NULL,

GuestEmail varchar(20) NULL,

GuestComments varchar(200) NULL

)

CREATE TABLE ROOMTYPE

(

RoomTypeID smallint NOT NULL IDENTITY(1,1),

RTDescription varchar(200) NOT NULL

)

CREATE TABLE CREDITCARD

(

CreditCardID smallint NOT NULL IDENTITY(1,1),

GuestID smallint NOT NULL,

CCType varchar(5) NOT NULL,

CCNumber varchar(16) NOT NULL,

CCCompany varchar(40) NULL,

CCCardHolder varchar(40) NOT NULL,

CCExpiration smalldatetime NOT NULL

)

CREATE TABLE DISCOUNT

(

DiscountID smallint NOT NULL IDENTITY(1,1),

DiscountDescription varchar(50) NOT NULL,

DiscountExperiation date NOT NULL,

DiscountRules varchar(100) NULL,

DiscountPercent decimal(4,2) NULL,

DiscountAmount smallmoney NULL

)

CREATE TABLE ROOM

(

RoomID smallint NOT NULL IDENTITY(1,1),

RoomNumber varchar(5) NOT NULL,

RoomDescription varchar(200) NOT NULL,

RoomSmoking bit NOT NULL,

RoomBedConfiguration char(2) NOT NULL,

HotelID smallint NOT NULL,

RoomTypeID smallint NOT NULL

)

CREATE TABLE RACKRATE

(

RackRateID smallint NOT NULL IDENTITY(1,1),

RoomTypeID smallint NOT NULL,

HotelID smallint NOT NULL,

RackRate smallmoney NOT NULL,

RackRateBegin date NOT NULL,

RackRateEnd date NOT NULL,

RackRateDescription varchar(200) NOT NULL

)

CREATE TABLE FOLIO

(

FolioID smallint NOT NULL IDENTITY(1,1),

ReservationID smallint NOT NULL,

GuestID smallint NOT NULL,

RoomID smallint NOT NULL,

QuotedRate smallmoney NOT NULL,

CheckinDate smalldatetime NOT NULL,

Nights tinyint NOT NULL,

FStatus char(1) NOT NULL,

Comments varchar(200) NULL,

DiscountID smallint NOT NULL

)

CREATE TABLE BILLING

(

FolioBillingID smallint NOT NULL IDENTITY (1,1),

FolioId smallint NOT NULL,

BillingCategoryID smallint NOT NULL,

BillingDescription char(30) NOT NULL,

BillingAmount smallmoney NOT NULL,

BillingItemQty tinyint NOT NULL,

BillingItemDate date NOT NULL,

)

CREATE TABLE HOTEL

(

HotelID smallint NOT NULL IDENTITY(2100,100),

HotelName varchar(30) NOT NULL,

HotelAddress varchar(30) NOT NULL,

HotelCity varchar(20) NOT NULL,

HotelState char(2) NULL,

HotelCountry varchar(20) NOT NULL,

HotelPostalCode char(10) NOT NULL,

HotelStarRating char(1) NULL,

HotelPictureLink varchar(100) NULL,

TaxLocationID smallint NOT NULL

)

CREATE TABLE PAYMENT

(

PaymentID smallint NOT NULL IDENTITY(8000,1),

FolioID smallint NOT NULL,

PaymentDate date NOT NULL,

PaymentAmount smallmoney NOT NULL,

PaymentComments varchar(200) NULL

)

CREATE TABLE BILLINGCATEGORY

(

BillingCategoryID smallint NOT NULL IDENTITY(1,1),

BillingCatDescription varchar(30) NOT NULL,

BillingCatTaxable bit NOT NULL

)

CREATE TABLE TAXRATE

(

TaxLocationID smallint NOT NULL IDENTITY(1,1),

TaxDescription varchar(30) NOT NULL,

RoomTaxRate decimal(6,4) NOT NULL,

SalesTaxRate decimal(6,4)

)

--Now lets ensure that all the tables were created correctly by using the GO command

--before altering the tables and adding in the contraints

GO

ALTER TABLE RESERVATION

ADD CONSTRAINT PK_ReservationID

PRIMARY KEY (ReservationID)

ALTER TABLE GUEST

ADD CONSTRAINT PK_GuestID

PRIMARY KEY (GuestID)

ALTER TABLE ROOMTYPE

ADD CONSTRAINT PK_RoomTypeID

PRIMARY KEY(RoomTypeID)

ALTER TABLE CREDITCARD

ADD CONSTRAINT PK_CreditCardID

PRIMARY KEY(CreditCardID)

ALTER TABLE DISCOUNT

ADD CONSTRAINT PK_DiscountID

PRIMARY KEY(DiscountID)

ALTER TABLE ROOM

ADD CONSTRAINT PK_RoomID

PRIMARY KEY(RoomID)

ALTER TABLE RACKRATE

ADD CONSTRAINT PK_RackRateID

PRIMARY KEY(RackRateID )

ALTER TABLE FOLIO

ADD CONSTRAINT PK_FolioID

PRIMARY KEY(FolioID)

ALTER TABLE BILLING

ADD CONSTRAINT PK_FolioBillingID

PRIMARY KEY(FolioBillingID)

ALTER TABLE HOTEL

ADD CONSTRAINT PK_HotelIDCheck

PRIMARY KEY(HotelID)

ALTER TABLE PAYMENT

ADD CONSTRAINT PK_PaymentID

PRIMARY KEY(PaymentID)

ALTER TABLE BILLINGCATEGORY

ADD CONSTRAINT PK_BillingCategoryID

PRIMARY KEY(BillingCategoryID)

ALTER TABLE TAXRATE

ADD CONSTRAINT PK_TaxLocationID

PRIMARY KEY(TaxLocationID)

-- Alter Foreign keys

GO

ALTER TABLE RESERVATION

ADD CONSTRAINT FK_RESERVATION

FOREIGN KEY (CreditCardID) REFERENCES CreditCard(CreditCardID)

ON UPDATE Cascade

ON DELETE Cascade

ALTER TABLE CREDITCARD

ADD CONSTRAINT FK_CREDITCARD

FOREIGN KEY (GuestID) REFERENCES Guest(GuestID)

ON UPDATE Cascade

ON DELETE Cascade

ALTER TABLE ROOM

ADD CONSTRAINT FK_ROOM

FOREIGN KEY (HotelID) REFERENCES HOTEL(HotelID)

ON UPDATE Cascade

ON DELETE Cascade,

CONSTRAINT FK_ROOMTYPEID

FOREIGN KEY (RoomTypeID) REFERENCES ROOMTYPE(RoomTypeID)

ON UPDATE No Action

ON DELETE No Action

ALTER TABLE RACKRATE

ADD CONSTRAINT FK_RACKRATE

FOREIGN KEY (RoomTypeID) REFERENCES ROOMTYPE(RoomTypeID)

ON UPDATE Cascade

ON DELETE Cascade,

CONSTRAINT FK_HOTELID2

FOREIGN KEY (HotelID) REFERENCES HOTEL(HotelID)

ON UPDATE No Action

ON DELETE No Action

ALTER TABLE HOTEL

ADD CONSTRAINT FK_HOTEL2

FOREIGN KEY (TaxLocationID) REFERENCES TAXRATE(TaxLocationID)

ON UPDATE Cascade

ON DELETE Cascade

ALTER TABLE BILLING

ADD CONSTRAINT FK_BILLING

FOREIGN KEY (FolioID) REFERENCES FOLIO(FolioID)

ON UPDATE Cascade

ON DELETE Cascade,

CONSTRAINT FK_Billingcat

FOREIGN KEY (BillingCategoryID) REFERENCES BILLINGCATEGORY(BillingCategoryID)

ON UPDATE No Action

ON DELETE No Action

-- ......

ALTER TABLE FOLIO

ADD CONSTRAINT FK_BILLINGIt

FOREIGN KEY (ReservationID) REFERENCES RESERVATION(ReservationID)

ON UPDATE Cascade

ON DELETE Cascade,

CONSTRAINT FK_ROOMIEID

FOREIGN KEY (RoomID) REFERENCES ROOM(RoomID)

ON UPDATE No Action

ON DELETE No Action,

CONSTRAINT FK_THEDISCOUNT

FOREIGN KEY (DiscountID) REFERENCES DISCOUNT(DiscountID)

ON UPDATE No Action

ON DELETE No Action

ALTER TABLE PAYMENT

ADD CONSTRAINT FK_PAYFORIT

FOREIGN KEY (FolioID) REFERENCES FOLIO(FolioID)

ON UPDATE Cascade

ON DELETE Cascade

BULK INSERT RESERVATION FROM 'c:\stage\farms1-1\Reservation.txt' WITH (FIELDTERMINATOR = '|', FIRSTROW =1)

BULK INSERT GUEST FROM 'c:\stage\farms1-1\Guest.txt' WITH (FIELDTERMINATOR = '|', FIRSTROW =1)

BULK INSERT CREDITCARD FROM 'c:\stage\farms1-1\CreditCard.txt' WITH (FIELDTERMINATOR = '|', FIRSTROW =1)

BULK INSERT ROOMTYPE FROM 'c:\stage\farms1-1\RoomType.txt' WITH (FIELDTERMINATOR = '|', FIRSTROW =1)

BULK INSERT DISCOUNT FROM 'c:\stage\farms1-1\Discount.txt' WITH (FIELDTERMINATOR = '|', FIRSTROW =1)

BULK INSERT ROOM FROM 'c:\stage\farms1-1\Room.txt' WITH (FIELDTERMINATOR = '|', FIRSTROW =1)

BULK INSERT RACKRATE FROM 'c:\stage\farms1-1\RackRate.txt' WITH (FIELDTERMINATOR = '|', FIRSTROW =1)

BULK INSERT FOLIO FROM 'c:\stage\farms1-1\Folio.txt' WITH (FIELDTERMINATOR = '|', FIRSTROW =1)

BULK INSERT BILLING FROM 'c:\stage\farms1-1\Billing.txt' WITH (FIELDTERMINATOR = '|', FIRSTROW =1)

BULK INSERT HOTEL FROM 'c:\stage\farms1-1\Hotel.txt' WITH (FIELDTERMINATOR = '|', FIRSTROW =1)

BULK INSERT PAYMENT FROM 'c:\stage\farms1-1\Payment.txt' WITH (FIELDTERMINATOR = '|', FIRSTROW =1)

BULK INSERT BILLINGCATEGORY FROM 'c:\stage\farms1-1\BillingCategory.txt' WITH (FIELDTERMINATOR = '|', FIRSTROW =1)

BULK INSERT TAXRATE FROM 'c:\stage\farms1-1\TaxRate.txt' WITH (FIELDTERMINATOR = '|', FIRSTROW =1)

--------------------------------------------------------------------------------------------------------------------------------------------------------

-- Inserting stored procedure sp_InsertRoomType which calls sp_InsertRackRate from Assignment # 6

GO

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE SPECIFIC_NAME = 'sp_InsertRoomType')

DROP PROCEDURE sp_InsertRoomType;

GO

GO

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE SPECIFIC_NAME = 'sp_InsertRackRate')

DROP PROCEDURE sp_InsertRackRate;

GO

CREATE PROCEDURE sp_InsertRackRate

@RoomTypeID smallint

, @HotelID smallint

, @RackRate smallmoney

, @RackRateBegin date

, @RackRateEnd date

, @RackRateDescription varchar(200)

AS

BEGIN

INSERT INTO RACKRATE(RoomTypeID,HotelID,RackRate,RackRateBegin,RackRateEnd,RackRateDescription)

VALUES(@RoomTypeID,@HotelID,@RackRate,@RackRateBegin,@RackRateEnd,@RackRateDescription);

END

GO

CREATE PROCEDURE sp_InsertRoomType

@RTDescription varchar(200)

, @NewHotelID smallint

, @NewRackRate smallmoney

, @NewRackRateBegin date

, @NewRackRateEnd date

, @NewRackRateDescription varchar(200)

AS

BEGIN

INSERT INTO ROOMTYPE(RTDescription)

VALUES(@RTDescription);

EXEC sp_InsertRackRate

@RoomTypeID = @@IDENTITY

, @HotelID = @NewHotelID

, @RackRate = @NewRackRate

, @RackRateBegin = @NewRackRateBegin

, @RackRateEnd = @NewRackRateEnd

, @RackRateDescription = @NewRackRateDescription

END

GO

------------------------------------------------------------------------------------------------------------------------------------------------------

--The function db.GetRackRate from Assignment # 7

--Write a user-defined function named dbo.GetRackRate which accepts a

--RoomID and a date. The function will return a smallmoney value for the room's rackrate

--where the date provided falls between the start and end dates of the rackrate found.

GO

IF OBJECT_ID (N'dbo.GetRackRate', N'FN') IS NOT NULL

DROP FUNCTION dbo.GetRackRate

GO

CREATE FUNCTION dbo.GetRackRate(@RoomID int, @Date smalldatetime)

returns smallmoney

as

begin

Declare @RoomTypeID varchar(100)

declare @RackRate varchar(100)

DECLARE @Datebetween varchar(100)

SELECT @RoomTypeID = RoomTypeID From ROOM

WHERE RoomID = @RoomID

SELECT @RackRate = RackRate FROM RACKRATE

WHERE RoomTypeID = @RoomTypeID

AND RackRateBegin < @Date and RackRateEnd > @Date

Return @RackRate

END

Go

-------------------------------------------------------------------------------------------------------------------------------------------------------

-- Function dbo.ProduceBill from A7

--3A. Write a user-defined function named dbo.ProduceBill that accepts only the input parameter 'FolioID'

--to generate a printed bill (nothing fancy - using SELECT statements) displaying the guest's

--information (Name, address, etc) and Folio Details (Room # not id, Check-in Date) ONE TIME, followed all

--the folioBilling items for that folioid with a single Total Amount Due at end. Hint: Use a cursor.

--For each folioid, loop through (and sum) each billingitem (qty * amount) while FolioBillingID count > 0.

--Note: Functions do not allow print, so you need to be creative with SELECT.

GO

IF OBJECT_ID (N'dbo.ProduceBill', N'FN') IS NOT NULL

DROP FUNCTION dbo.ProduceBill

GO

CREATE FUNCTION dbo.ProduceBill(@FolioID smallint)

RETURNS @GuestBill TABLE

(

[Guest_Bill_Statment] varchar(max)

)

--SELECT * FROM GUEST

AS

BEGIN

INSERT INTO @GuestBill

SELECT 'Guest Name: ' + GuestFirst + ' ' + GuestLast + char(13) + char(10) +

'Address Line 1: ' + GuestAddress + CHAR(13) + CHAR(10) +

'Address Line 2: ' + GuestAddress2 + CHAR(13) + CHAR(10) +

'City: ' + GuestCity + CHAR(13) + CHAR(10) +

'Country: ' + GuestCountry + CHAR(13) + CHAR(10) +

'Room Number: ' + RoomNumber + CHAR(13) + CHAR(10) +

'Checkin Date: ' + CONVERT(varchar, CheckinDate, 107) AS 'Bill'

FROM GUEST

JOIN CREDITCARD

ON GUEST.GuestID = CREDITCARD.GuestID

JOIN Reservation

ON CREDITCARD.CreditCardID = RESERVATION.CreditCardID

JOIN FOLIO

ON RESERVATION.ReservationID = FOLIO.ReservationID

JOIN ROOM

ON FOLIO.RoomID = Room.RoomID

WHERE FolioID = @FolioID

DECLARE BillingDetailsCursor CURSOR FOR

SELECT FolioBillingID, FolioID, BillingDescription, BillingAmount, BillingItemQty, BillingItemDate, BillingCatDescription

FROM BILLING

JOIN BILLINGCATEGORY ON BILLING.BillingCategoryID = BILLINGCATEGORY.BillingCategoryID

WHERE BILLING.FolioID = @FolioID

DECLARE @FolioBillingID smallint

DECLARE @FolioIDPrint smallint

DECLARE @BillingDescription varchar(30)

DECLARE @BillingAmount smallmoney

DECLARE @BillingItemQty smallint

DECLARE @BillingItemDate date

DECLARE @BillingCatDescription varchar(200)

DECLARE @GuestBillingTotal smallmoney

SET @GuestBillingTotal = 0

OPEN BillingDetailsCursor

-- Fetch First Time

FETCH NEXT FROM BillingDetailsCursor

INTO @FolioBillingID, @FolioIDPrint, @BillingDescription, @BillingAmount, @BillingItemQty, @BillingItemDate, @BillingCatDescription

WHILE @@FETCH_STATUS = 0

BEGIN

INSERT INTO @GuestBill VALUES('Billing Description: ' + @BillingDescription + char(13) + char(10) +

'Billing Amount: $' + CONVERT(varchar, @BillingAmount) + char(13) + char(10) +

'Billing Item Quantity: ' + CONVERT(varchar, @BillingItemQty) + char(13) + char(10) +

'Billing Item Date: ' + CONVERT(varchar, @BillingItemDate, 107) + char(13) + char(10) +

'Billing Category Description: ' + @BillingCatDescription)

SET @GuestBillingTotal = @GuestBillingTotal + (@BillingAmount * @BillingItemQty)

-- Fetch Again

FETCH NEXT FROM BillingDetailsCursor

INTO @FolioBillingID, @FolioIDPrint, @BillingDescription, @BillingAmount, @BillingItemQty, @BillingItemDate, @BillingCatDescription

END

CLOSE BillingDetailsCursor

DEALLOCATE BillingDetailsCursor

INSERT INTO @GuestBill VALUES('Billing Total: $' + CONVERT(varchar, @GuestBillingTotal))

RETURN

END

GO

-----------------------------------------------------------------------------------------------------------------------------------------------------

-- The function dbo.GetRoomTaxRate

--1. Write a user-defined function named dbo.GetRoomTaxRate which accepts a RoomID. The function will return a decimal

-- value for the room's roomtaxrate. (you will use this simple function in #5 below).

--CREATE if function exists

GO

IF OBJECT_ID (N'dbo.GetRoomTaxRate', N'FN') IS NOT NULL

DROP FUNCTION dbo.GetRoomTaxRate

GO

CREATE FUNCTION dbo.GetRoomTaxRate(@RoomID int)

returns varchar(100)

as

begin

Declare @HotelID varchar(100)

declare @TaxLocationID varchar(100)

DECLARE @SalesTax varchar(100)

SELECT @HotelID = HotelID From ROOM

WHERE RoomID = @RoomID

SELECT @taxLocationID = TaxLocationID FROM HOTEL

WHERE HotelID = @HotelID

SELECT @SalesTax = SalesTaxRate FROM TAXRATE

Where TaxLocationID = @taxLocationID

Return @SalesTax

END

GO

----------------------------------------------------------------------------------------------------------------------------------------------

--0A. Just like you did in A6 (part 2B)...demonstrate

--Community Cottages (Hotel 2400) is finally opening tomorrow!! Using sp_InsertRoomType, Insert a new RoomType called 'Dorm Room' with a

-- year round rate for 2017 of $35.50 per night. Have the stored procedure's output pass its @@identity to sp_InsertRackRate, and add the details

-- for the DormRoom with a rackrate description: 'Special Grand Opening Rate'.

EXEC sp_InsertRoomType

@RTDescription ='Dorm Room'

, @NewHotelID = '2400'

, @NewRackRate =35.50

, @NewRackRateBegin = '2016-01-01'

, @NewRackRateEnd = '2016-12-31'

, @NewRackRateDescription ='Special Grand opening Rate'

SELECT * FROM ROOMTYPE

SELECT * FROM RACKRATE -- ROOM TYPE ID FOR LATER IS 11

GO

-------------------------------------------------------------------------------------------------------------------------------------------

--0C. Write three simple Insert statements to Insert 3 new rooms (Room 101, 102, 103). All with a description "standard dorm room",

-- non-smoking, bed configuration "2F", Hotel ID 2400, and the RoomTypeID you just created in 0A/B.

select * from room

Insert into Room(RoomNumber,RoomDescription,RoomSmoking,RoomBedConfiguration,HotelID,RoomTypeID)

values('101','Standard Dorm Room','0','2F','2400','11');

Insert into Room(RoomNumber,RoomDescription,RoomSmoking,RoomBedConfiguration,HotelID,RoomTypeID)

values('102','Standard Dorm Room','0','2F','2400','11');

Insert into Room(RoomNumber,RoomDescription,RoomSmoking,RoomBedConfiguration,HotelID,RoomTypeID)

values('103','Standard Dorm Room','0','2F','2400','11');

-----------------------------------------------------------------------------------------------------------------------------------------------

--0D. Select * From Room to show the new rooms. Note the RoomIDs for later. (ROOM IDS ARE , 20,21,22

Select * From Room

--------------------------------------------------------------------------------------------------------------------------------------------------

-- 1. Write a trigger named tr_GuestDMustExist on the FOLIO table (after INSERT or UPDATE) that checks to make sure the GuestID is a valid

--GuestID (essentially enforcing referential integrity between the Folio and Guest Tables). If an improper GuestiD is inserted/updated,

--then raise an error message (I prefer Raiserror with parameter fill in the message), and rollback the transaction.

GO

Create Trigger tr_GuestDMustExist ON Folio

AFTER INSERT, UPDATE

AS

DECLARE @GuestID smallint

IF Exists ( SELECT 'This text does not matter'

FROM Inserted i

LEFT JOIN GUEST t ON i.GuestID = t.GuestID

WHERE t.GuestID is NULL )

BEGIN

SELECT @GuestID = (SELECT GuestID FROM Inserted) RAISERROR (60005,10,1,@GuestID)

ROLLBACK

END

------------------------------------------------------------------------------------------------------------------------------------------------------

--1A. Write a simple INSERT statement to create a new RESERVATION with today's date, based on credit card ID 1 (linked to Anita Proul),

-- and make the status "R" for reserved.

GO

Insert into RESERVATION(ReservationDate,ReservationStatus,ReservationComments,CreditCardID)

values('2017-07-09','R','null','1');

GO

------------------------------------------------------------------------------------------------------------------------------------------------------

--1B Write a simple SELECT statement to determine the @@Identity of the last reservation inserted.

SELECT @@IDENTITY AS 'Last Reservation ID';

GO

---------------------------------------------------------------------------------------------------------------------------------------------------

--1C. Now, demonstrate the tr_GuestIDMustExist trigger on the Folio table by creating a new Folio entry based on the ReservationID you made

--in #1A/B above). Insert into Folio the reservationid (from 1B - it should be 5020), the roomid for one of then new rooms you determined in 0D,

-- the quoted rate of $35.50, the check-in date of 24 July for 3 nights, status "R", with no discounts (1),BUT instead of using Anita's real guest

--ID of 1500, you will try to use GuestID 500 (intentional typo). An error message should display showing GuestID 500 is invalid, if your trigger

--was written correctly.

BEGIN TRY

BEGIN TRANSACTION

Insert into FOLIO(ReservationID,GuestID,RoomID,QuotedRate,CheckinDate,Nights,FStatus,Comments,DiscountID)

values('5020','500','20','35.50','2017-07-24','3','R','null','1');

COMMIT

END TRY

BEGIN CATCH

IF @@TRANCOUNT > 0

ROLLBACK TRAN

DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE()

DECLARE @ErrorSeverity INT = ERROR_SEVERITY()

DECLARE @ErrorState INT = ERROR_STATE()

-- Use RAISERROR inside the CATCH block to return error

-- information about the original error that caused

-- execution to jump to the CATCH block.

RAISERROR (@ErrorMessage,

@ErrorSeverity,

@ErrorState

);

END CATCH

-------------------------------------------------------------------------------------------------------------------------------------------------------

--1D. Demonstrate the same trigger again with Anita's real guest id of 1500. You should now have an active Folio linked back to her reservation.

Insert into FOLIO(ReservationID,GuestID,RoomID,QuotedRate,CheckinDate,Nights,FStatus,Comments,DiscountID)

values('5020','1500','20','35.50','2017-07-24','3','R','null','1');

--------------------------------------------------------------------------------------------------------------------------------------------------

--1E. Select * from Folio to show the results.

Select * from Folio

-----------