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.