functionsAndTriggers.sql

-- FUNCTIONS

-- returns the total funds of a single episode
CREATE FUNCTION EPISODEFUNDS(ENO INT)
RETURNS INT
RETURN SELECT SUM(FUNDS) FROM SPONSORS
WHERE EPISODENO = ENO
GROUP BY EPISODENO;

-- returns table which contains how much points any contestant has in every episode
CREATE FUNCTION ID_CONTESTANT_POINTS()
RETURNS TABLE(
    ID INT,
    EPISODENO INT,
    NAME VARCHAR(255),
    EP_SCORE INT)
RETURN SELECT P.CONTESTANTCODE,P.EPISODENO, C.NAME,SUM(SCORE)
        FROM POINTS P,CONTESTANT C
WHERE P.CONTESTANTCODE = C.CODE
GROUP BY  P.CONTESTANTCODE, P.EPISODENO, C.NAME;


-- Call the above function
SELECT * FROM TABLE(FN45464.ID_CONTESTANT_POINTS());

--VIEWS
CREATE VIEW V_EPISODE_FUNDS(EPISODENO,FUNDED)
AS
SELECT DISTINCT EPISODENO, FN45464.EPISODEFUNDS(EPISODENO)
FROM SPONSORS;

--view that shows total points each contestant has
CREATE VIEW V_CONTESTANT_FINALSCORE(NAME,FINALSCORE)
AS
    SELECT NAME,SUM(EP_SCORE) AS FINALSCORE FROM TABLE(FN45464.ID_CONTESTANT_POINTS())
GROUP BY NAME;

--view that shows song and the contestant who sang it and the points he receives for it
CREATE VIEW V_CONT_SONG_SCORE(NAME,SONG,TOTAL_SCORE)
AS
    SELECT C.NAME, S.NAME, C.EP_SCORE
    FROM SONG S, TABLE(FN45464.ID_CONTESTANT_POINTS()) AS C
WHERE S.CONTESTANTCODE = C.ID
AND C.EPISODENO = S.EPISODENO;

-- Triggers

-- A trigger that is called before updating the funds and checking whether the total funds are greater
-- than 4000$
CREATE TRIGGER UPDATESPONSOREDFUNDS
    BEFORE UPDATE OF FUNDS ON SPONSORS
    REFERENCING NEW AS N OLD AS O
    FOR EACH ROW
     WHEN ( FN45464.EPISODEFUNDS(N.EPISODENO) - O.FUNDS + N.FUNDS < 4000 ) SET N.FUNDS = O.FUNDS;

-- A trigger that is called after inserting a new company in the COMPANY table
-- therefore inserts the new company as a sponsor of the last episode with 4000$ funds
CREATE TRIGGER AUTOFUND
    AFTER INSERT ON COMPANY
    REFERENCING NEW AS N
    FOR EACH ROW
    BEGIN
    DECLARE LAST_ROW INT;
    SET LAST_ROW = (SELECT MAX(EPISODENO) from SPONSORS);
    INSERT INTO SPONSORS(EPISODENO, COMPANYCODE, FUNDS) VALUES (LAST_ROW, N.CODE, 4000);
    END;