CREATE TABLE FLIGHT ( CRAFT_CODE VARCHAR(30), SOURCE VARCHAR(30), DESTINATION VARCHAR(30) )

CREATE TABLE RESERVATIONS ( CRAFT_CODE VARCHAR(30), NO_OF_SEATS INT, CLASS VARCHAR(30) )

-- REQUIREMENT: HOW TO INSERT DATA INTO BOTH FLIGHT TABLE AND RESERVATION TABLE TOGETHER? SINGLE TRANSACTION. CREATE VIEW VW_FLIGHT_RESERVATIONS AS SELECT FLIGHT.CRAFT_CODE, SOURCE, DESTINATION, NO_OF_SEATS, CLASS FROM FLIGHT INNER JOIN RESERVATIONS ON FLIGHT.CRAFT_CODE = RESERVATIONS.CRAFT_CODE

-- ISSUE: INSERT INTO VW_FLIGHT_RESERVATIONS VALUES ('AI01', 'SRC1', 'DEST1', 11, 'ECO') SELECT * FROM VW_FLIGHT_RESERVATIONS

-- SOLUTION: UPDATABLE VIEWS = DML TRIGGERS ON VIEWS CREATE TRIGGER TRIG ON VW_FLIGHT_RESERVATIONS INSTEAD OF INSERT AS BEGIN BEGIN TRANSACTION BEGIN TRY DECLARE @CRAFT_CODE VARCHAR(30) DECLARE @SOURCE VARCHAR(30) DECLARE @DESTINATION VARCHAR(30) DECLARE @NO_OF_SEATS VARCHAR(30) DECLARE @CLASS VARCHAR(30)

SELECT @CRAFT_CODE = CRAFT_CODE FROM INSERTED SELECT @SOURCE = SOURCE FROM INSERTED SELECT @DESTINATION = DESTINATION FROM INSERTED SELECT @NO_OF_SEATS = NO_OF_SEATS FROM INSERTED SELECT @CLASS = CLASS FROM INSERTED

INSERT INTO FLIGHT VALUES (@CRAFT_CODE, @SOURCE, @DESTINATION) INSERT INTO RESERVATIONS VALUES (@CRAFT_CODE, @NO_OF_SEATS, @CLASS) COMMIT END TRY

BEGIN CATCH PRINT 'ERROR DURING DATA DISTRIBUTION' ROLLBACK END CATCH END

INSERT INTO VW_FLIGHT_RESERVATIONS VALUES ('AI01', 'SRC1', 'DEST1', 11, 'ECO') SELECT * FROM VW_FLIGHT_RESERVATIONS SELECT * FROM FLIGHT SELECT * FROM RESERVATIONS

CREATE PROC USP_INSERT_FLIGHTS @CRAFT_CODE VARCHAR(30), @SOURCE VARCHAR(30), @DESTINATION VARCHAR(30), @NO_OF_SEATS INT, @CLASS VARCHAR(30) AS INSERT INTO VW_FLIGHT_RESERVATIONS VALUES (@CRAFT_CODE, @SOURCE, @DESTINATION, @NO_OF_SEATS, @CLASS)

EXEC USP_INSERT_FLIGHTS 'AI02', 'SRC2', 'DEST2', 22, 'ECO' SELECT * FROM VW_FLIGHT_RESERVATIONS SELECT * FROM FLIGHT SELECT * FROM RESERVATIONS

SP > VIEW > TRIGGER > BASE TABLES

-- TASK 1: WRITE A PROCEDURE TO REPORT LIST OF ALL RESERVATIONS FOR A GIVEN FLIGHT (MEANS: YOU NEED TO SUPPLY FLIGHT DYNAMICALLY @ RUNTIME? -- TASK 2: WRITE A PROCEDURE TO REPORT LIST OF AL SUCH FLIGHTS WITH MORE THAN GIVEN COUNT OF RESERVATIONS? -- TASK 3: WRITE A PROCEDURE TO VALIDATE AND INSERT DATA INTO RESERVATIONS IN SUCH A WAY THE MINIMUM SEATS SHOULD BE 10 -- TASK 4: WRITE A PROCEDURE TO VALIDATE AND INSERT DATA INTO FLIGHT TABLE IN SUCH A WAY SOURCE CITY AND DESTINATION CITY ARE DIFFERENT.