Предположим, что в базе данных поддерживаются две - Ответ на вопрос по информатике №1163825

Вопрос

Предположим, что в базе данных поддерживаются две отдельные таблицы RDEMP и RMEMP с такой же структурой, что и таблица EMP, но содержащие данные о служащих, имеющих самый большой доход в своем отделе и среди участников своего проекта соответственно (если для некоторого служащего размер премиальных неизвестен, премиальные не учитываются в общей сумме его дохода). Какие из приводимых ниже определений триггеров обеспечат требуемое наполнение этих таблиц?

• CREATE TRIGGER EMP_INSERT AFTER INSERT ON EMP REFERENCING NEW ROW AS NEW_EMP FOR EACH ROW BEGIN ATOMIC WHEN (NEW_EMP.EMP_SAL + COALESCE (NEW.EMP_BONUS, 0)>= (SELECT RDEMP.EMP_SAL + COALESCE (RDEMP.EMP_BONUS, 0) FROM RDEMP WHERE RDEMP.DEPT_NO = NEW_EMP.DEPT_NO) OR NOT EXISTS (SELECT * FROM RDEMP WHERE RDEMP.DEPT_NO = NEW_EMP.DEPT_NO)) BEGIN ATOMIC DELETE FROM RDEMP WHERE RDEMP.EMP_NO = NEW_EMP.EMP_NO AND RDEMP.EMP_SAL + COALESCE (RDEMP.EMP_BONUS, 0) < NEW_EMP.EMP_SAL + COALESCE (NEW.EMP_BONUS, 0); INSERT INTO RDEMP ROW (NEW_EMP.EMP_NO, NEW_EMP.DEPT_NO, NEW_EMP.PRO_NO, NEW_EMP.EMP_BDATE, NEW_EMP.EMP_SAL, NEW_EMP.EMP_BONUS); END; WHEN (NEW_EMP.EMP_SAL + COALESCE (NEW.EMP_BONUS, 0)>= (SELECT RMDEMP.EMP_SAL + COALESCE (RMEMP.EMP_BONUS, 0) FROM RMEMP WHERE RMEMP.DEPT_NO = NEW_EMP.DEPT_NO) OR NOT EXISTS (SELECT * FROM RMEMP WHERE RMEMP.PRO_NO = NEW_EMP.PRO_NO)) BEGIN ATOMIC DELETE FROM RMEMP WHERE RMEMP.EMP_NO = NEW_EMP.EMP_NO AND RMEMP.EMP_SAL + COALESCE (RMEMP.EMP_BONUS, 0) < NEW_EMP.EMP_SAL + COALESCE (NEW.EMP_BONUS, 0); INSERT INTO RMEMP ROW (NEW_EMP.EMP_NO, NEW_EMP.DEPT_NO, NEW_EMP.PRO_NO, NEW_EMP.EMP_BDATE, NEW_EMP.EMP_SAL, NEW_EMP.EMP_BONUS); END; END;CREATE TRIGGER EMP_UPDATE AFTER UPDATE OF EMP_SAL, EMP_BONUS ON EMP REFERENCING NEW ROW AS NEW_EMP FOR EACH ROW BEGIN ATOMIC WHEN (NEW_EMP.EMP_SAL + COALESCE (NEW.EMP_BONUS, 0)>= (SELECT RDEMP.EMP_SAL + COALESCE (RDEMP.EMP_BONUS, 0) FROM RDEMP WHERE RDEMP.DEPT_NO = NEW_EMP.DEPT_NO) OR NOT EXISTS (SELECT * FROM RDEMP WHERE RDEMP.DEPT_NO = NEW_EMP.DEPT_NO)) BEGIN ATOMIC DELETE FROM RDEMP WHERE RDEMP.EMP_NO = NEW_EMP.EMP_NO AND RDEMP.EMP_SAL + COALESCE (RDEMP.EMP_BONUS, 0) < NEW_EMP.EMP_SAL + COALESCE (NEW.EMP_BONUS, 0); INSERT INTO RDEMP ROW (NEW_EMP.EMP_NO, NEW_EMP.DEPT_NO, NEW_EMP.PRO_NO, NEW_EMP.EMP_BDATE, NEW_EMP.EMP_SAL, NEW_EMP.EMP_BONUS); END; WHEN (NEW_EMP.EMP_SAL + COALESCE (NEW.EMP_BONUS, 0)>= (SELECT RMEMP.EMP_SAL + COALESCE (RMEMP.EMP_BONUS, 0) FROM RMEMP WHERE RMEMP.DEPT_NO = NEW_EMP.DEPT_NO) OR NOT EXISTS (SELECT * FROM RMEMP WHERE RMEMP.PRO_NO = NEW_EMP.PRO_NO)) BEGIN ATOMIC DELETE FROM RMEMP WHERE RMEMP.EMP_NO = NEW_EMP.EMP_NO AND RMEMP.EMP_SAL + COALESCE (RMEMP.EMP_BONUS, 0) < NEW_EMP.EMP_SAL + COALESCE (NEW.EMP_BONUS, 0); INSERT INTO RMEMP ROW (NEW_EMP.EMP_NO, NEW_EMP.DEPT_NO, NEW_EMP.PRO_NO, NEW_EMP.EMP_BDATE, NEW_EMP.EMP_SAL, NEW_EMP.EMP_BONUS); END; END;CREATE TRIGGER EMP_DELETE AFTER DELETE ON EMP REFERENCING OLD ROW AS OLD_EMP FOR EACH ROW BEGIN ATOMIC WHEN (NEW_EMP IN (SELECT * FROM RDEMP WHERE RDEMP.DEPT_NO = OLD_EMP.DEPT_NO)) BEGIN ATOMIC DELETE FROM RDEMP WHERE RDEMP.EMP_NO = OLD_EMP.EMP_NO; INSERT INTO RDEMP (SELECT * FROM EMP WHERE NOT EXISTS (SELECT * FROM RDEMP WHERE RDEMP.DEPT_NO = EMP.DEPT_NO) AND EMP.DEPT_NO = OLD_EMP.DEPT_NO AND EMP.EMP_SAL + COALESCE (EMP.EMP_BONUS, 0)= (SELECT MAX (E.EMP_SAL + COALESCE (E.EMP_BONUS, 0)) FROM EMP E WHERE E.DEPT_NO = EMP.DEPT_NO); END; WHEN (NEW_EMP IN (SELECT * FROM RMEMP WHERE RMEMP.DEPT_NO = OLD_EMP.DEPT_NO)) BEGIN ATOMIC DELETE FROM RMEMP WHERE RMEMP.EMP_NO = OLD_EMP.EMP_NO; INSERT INTO RMEMP (SELECT * FROM EMP WHERE NOT EXISTS (SELECT * FROM RMEMP WHERE RMEMP.PRO_NO = EMP.PRO_NO) AND EMP.DEPT_NO = OLD_EMP.DEPT_NO AND EMP.EMP_SAL + COALESCE (EMP.EMP_BONUS, 0)= (SELECT MAX (E.EMP_SAL + COALESCE (E.EMP_BONUS, 0)) FROM EMP E WHERE E.PRO_NO = EMP.PRO_NO); END; END;
• CREATE TRIGGER EMP_INSERT1 AFTER INSERT ON EMP REFERENCING NEW ROW AS NEW_EMP FOR EACH ROW WHEN (NEW_EMP.EMP_SAL + COALESCE (NEW.EMP_BONUS, 0)>= (SELECT RDEMP.EMP_SAL + COALESCE (RDEMP.EMP_BONUS, 0) FROM RDEMP WHERE RDEMP.DEPT_NO = NEW_EMP.DEPT_NO) OR NOT EXISTS (SELECT * FROM RDEMP WHERE RDEMP.DEPT_NO = NEW_EMP.DEPT_NO)) BEGIN ATOMIC DELETE FROM RDEMP WHERE RDEMP.EMP_NO = NEW_EMP.EMP_NO AND RDEMP.EMP_SAL + COALESCE (RDEMP.EMP_BONUS, 0) < NEW_EMP.EMP_SAL + COALESCE (NEW.EMP_BONUS, 0); INSERT INTO RDEMP ROW (NEW_EMP.EMP_NO, NEW_EMP.DEPT_NO, NEW_EMP.PRO_NO, NEW_EMP.EMP_BDATE, NEW_EMP.EMP_SAL, NEW_EMP.EMP_BONUS); END;CREATE TRIGGER EMP_INSERT2 AFTER INSERT ON EMP REFERENCING NEW ROW AS NEW_EMP FOR EACH ROW WHEN (NEW_EMP.EMP_SAL + COALESCE (NEW.EMP_BONUS, 0)>= (SELECT RMDEMP.EMP_SAL + COALESCE (RMEMP.EMP_BONUS, 0) FROM RMEMP WHERE RMEMP.DEPT_NO = NEW_EMP.DEPT_NO) OR NOT EXISTS (SELECT * FROM RMEMP WHERE RMEMP.PRO_NO = NEW_EMP.PRO_NO)) BEGIN ATOMIC DELETE FROM RMEMP WHERE RMEMP.EMP_NO = NEW_EMP.EMP_NO AND RMEMP.EMP_SAL + COALESCE (RMEMP.EMP_BONUS, 0) < NEW_EMP.EMP_SAL + COALESCE (NEW.EMP_BONUS, 0); INSERT INTO RMEMP ROW (NEW_EMP.EMP_NO, NEW_EMP.DEPT_NO, NEW_EMP.PRO_NO, NEW_EMP.EMP_BDATE, NEW_EMP.EMP_SAL, NEW_EMP.EMP_BONUS); END;CREATE TRIGGER EMP_UPDATE1 AFTER UPDATE OF EMP_SAL, EMP_BONUS ON EMP REFERENCING NEW ROW AS NEW_EMP FOR EACH ROW WHEN (NEW_EMP.EMP_SAL + COALESCE (NEW.EMP_BONUS, 0)>= (SELECT RDEMP.EMP_SAL + COALESCE (RDEMP.EMP_BONUS, 0) FROM RDEMP WHERE RDEMP.DEPT_NO = NEW_EMP.DEPT_NO) OR NOT EXISTS (SELECT * FROM RDEMP WHERE RDEMP.DEPT_NO = NEW_EMP.DEPT_NO)) BEGIN ATOMIC DELETE FROM RDEMP WHERE RDEMP.EMP_NO = NEW_EMP.EMP_NO AND RDEMP.EMP_SAL + COALESCE (RDEMP.EMP_BONUS, 0) < NEW_EMP.EMP_SAL + COALESCE (NEW.EMP_BONUS, 0); INSERT INTO RDEMP ROW (NEW_EMP.EMP_NO, NEW_EMP.DEPT_NO, NEW_EMP.PRO_NO, NEW_EMP.EMP_BDATE, NEW_EMP.EMP_SAL, NEW_EMP.EMP_BONUS); END;CREATE TRIGGER EMP_UPDATE2 AFTER UPDATE OF EMP_SAL, EMP_BONUS ON EMP REFERENCING NEW ROW AS NEW_EMP FOR EACH ROW WHEN (NEW_EMP.EMP_SAL + COALESCE (NEW.EMP_BONUS, 0)>= (SELECT RMEMP.EMP_SAL + COALESCE (RMEMP.EMP_BONUS, 0) FROM RMEMP WHERE RMEMP.DEPT_NO = NEW_EMP.DEPT_NO) OR NOT EXISTS (SELECT * FROM RMEMP WHERE RMEMP.PRO_NO = NEW_EMP.PRO_NO)) BEGIN ATOMIC DELETE FROM RMEMP WHERE RMEMP.EMP_NO = NEW_EMP.EMP_NO AND RMEMP.EMP_SAL + COALESCE (RMEMP.EMP_BONUS, 0) < NEW_EMP.EMP_SAL + COALESCE (NEW.EMP_BONUS, 0); INSERT INTO RMEMP ROW (NEW_EMP.EMP_NO, NEW_EMP.DEPT_NO, NEW_EMP.PRO_NO, NEW_EMP.EMP_BDATE, NEW_EMP.EMP_SAL, NEW_EMP.EMP_BONUS); END;CREATE TRIGGER EMP_DELETE1 AFTER DELETE ON EMP REFERENCING OLD ROW AS OLD_EMP FOR EACH ROW WHEN (NEW_EMP IN (SELECT * FROM RDEMP WHERE RDEMP.DEPT_NO = OLD_EMP.DEPT_NO)) BEGIN ATOMIC DELETE FROM RDEMP WHERE RDEMP.EMP_NO = OLD_EMP.EMP_NO; INSERT INTO RDEMP (SELECT * FROM EMP WHERE NOT EXISTS (SELECT * FROM RDEMP WHERE RDEMP.DEPT_NO = EMP.DEPT_NO) AND EMP.DEPT_NO = OLD_EMP.DEPT_NO AND EMP.EMP_SAL + COALESCE (EMP.EMP_BONUS, 0)= (SELECT MAX (E.EMP_SAL + COALESCE (E.EMP_BONUS, 0)) FROM EMP E WHERE E.DEPT_NO = EMP.DEPT_NO); END;CREATE TRIGGER EMP_DELETE2 AFTER DELETE ON EMP REFERENCING OLD ROW AS OLD_EMP FOR EACH ROW WHEN (NEW_EMP IN (SELECT * FROM RMEMP WHERE RMEMP.DEPT_NO = OLD_EMP.DEPT_NO)) BEGIN ATOMIC DELETE FROM RMEMP WHERE RMEMP.EMP_NO = OLD_EMP.EMP_NO; INSERT INTO RMEMP (SELECT * FROM EMP WHERE NOT EXISTS (SELECT * FROM RMEMP WHERE RMEMP.PRO_NO = EMP.PRO_NO) AND EMP.DEPT_NO = OLD_EMP.DEPT_NO AND EMP.EMP_SAL + COALESCE (EMP.EMP_BONUS, 0)= (SELECT MAX (E.EMP_SAL + COALESCE (E.EMP_BONUS, 0)) FROM EMP E WHERE E.PRO_NO = EMP.PRO_NO); END;
• CREATE TRIGGER EMP_INSERT AFTER INSERT ON EMP FOR EACH STATEMENT BEGIN ATOMIC DELETE * FROM RDEMP; INSERT INTO RDEMP (SELECT * FROM EMP WHERE EMP_SAL = (SELECT MAX (E.EMP_SAL + COALESCE (E_BONUS, 0)) FROM EMP E WHERE E.DEPT_NO = EMP.DEPT_NO); DELETE * FROM RMEMP; INSERT INTO RMEMP (SELECT * FROM EMP WHERE EMP_SAL = (SELECT MAX (E.EMP_SAL + COALESCE (E_BONUS, 0)) FROM EMP E WHERE E.PRO_NO = EMP.PRO_NO); END;CREATE TRIGGER EMP_UPDATE AFTER UPDATE ON EMP FOR EACH STATEMENT BEGIN ATOMIC DELETE * FROM RDEMP; INSERT INTO RDEMP (SELECT * FROM EMP WHERE EMP_SAL = (SELECT MAX (E.EMP_SAL + COALESCE (E_BONUS, 0)) FROM EMP E WHERE E.DEPT_NO = EMP.DEPT_NO); DELETE * FROM RMEMP; INSERT INTO RMEMP (SELECT * FROM EMP WHERE EMP_SAL = (SELECT MAX (E.EMP_SAL + COALESCE (E_BONUS, 0)) FROM EMP E WHERE E.PRO_NO = EMP.PRO_NO); END;CREATE TRIGGER EMP_DELETE AFTER DELETE ON EMP FOR EACH STATEMENT BEGIN ATOMIC DELETE * FROM RDEMP; INSERT INTO RDEMP (SELECT * FROM EMP WHERE EMP_SAL = (SELECT MAX (E.EMP_SAL + COALESCE (E_BONUS, 0)) FROM EMP E WHERE E.DEPT_NO = EMP.DEPT_NO); DELETE * FROM RMEMP; INSERT INTO RMEMP (SELECT * FROM EMP WHERE EMP_SAL = (SELECT MAX (E.EMP_SAL + COALESCE (E_BONUS, 0)) FROM EMP E WHERE E.PRO_NO = EMP.PRO_NO); END;

Ответ