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

Вопрос

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

• CREATE TRIGGER EMP_INSERT AFTER INSERT ON EMP REFERENCES NEW TABLE AS NEW_EMP INSERT (NEW_EMP.EMP_NO, CURRENT_TIMESRAMP, NEW_EMP.EMP_SAL, NEW_EMP.EMP_BONUS, (SELECT COUNT (*) FROM EMP WHERE EMP.DEPT_NO = NEW_EMP.DEPT_NO AND EMP.EMP_SAL + COALESCE (EMP.EMP_BONUS, 0) < NEW_EMP.EMP_SAL + COALESCE (NEW_EMP.EMP_BONUS, 0)), (SELECT COUNT (*) FROM EMP WHERE EMP.DEPT_NO = NEW_EMP.DEPT_NO AND EMP.EMP_SAL + COALESCE (EMP.EMP_BONUS, 0) > NEW_EMP.EMP_SAL + COALESCE (NEW_EMP.EMP_BONUS, 0));CREATE TRIGGER EMP_UPDATE AFTER UPDATE OF EMP_SAL, EMP_BONUS ON EMP REFERENCING NEW TABLE AS NEW_EMP INSERT INTO RDEMP (SELECT NEW.EMP_NO, CURRENT_TIMESRAMP, NEW_EMP.EMP_SAL, NEW_EMP.EMP_BONUS, SELECT COUNT (*) FROM EMP WHERE EMP.DEPT_NO = NEW_EMP.DEPT_NO AND EMP.EMP_SAL + COALESCE (EMP.EMP_BONUS, 0) < NEW_EMP.EMP_SAL + COALESCE (NEW_EMP.EMP_BONUS, 0)), (SELECT NEW.EMP_NO, CURRENT_TIMESRAMP, NEW_EMP.EMP_SAL, NEW_EMP.EMP_BONUS, SELECT COUNT (*) FROM EMP WHERE EMP.DEPT_NO = NEW_EMP.DEPT_NO AND EMP.EMP_SAL + COALESCE (EMP.EMP_BONUS, 0) < NEW_EMP.EMP_SAL + COALESCE (NEW_EMP.EMP_BONUS, 0)) FROM NEW_EMP WHERE ((SELECT COUNT (*) FROM EMP WHERE EMP.DEPT_NO = NEW_EMP.DEPT_NO AND EMP.EMP_SAL + COALESCE (EMP.EMP_BONUS, 0) < NEW_EMP.EMP_SAL + COALESCE (NEW_EMP.EMP_BONUS, 0)), SELECT COUNT (*) FROM EMP WHERE EMP.DEPT_NO = NEW_EMP.DEPT_NO AND EMP.EMP_SAL + COALESCE (EMP.EMP_BONUS, 0) > NEW_EMP.EMP_SAL + COALESCE (NEW_EMP.EMP_BONUS, 0))) <> (SELECT RDEMP.NUMBER_LESS, RDEMP.NUMBER_MORE FROM RDEMP.EVENT_TS = SELECT MAX (RDE.EVENT_TS) FROM RDEMP RDE WHERE RDE.EMP_NO = RDEMP.EMP_NO));CREATE TRIGGER EMP_DELETE AFTER DELETE ON EMP REFERENCING OLD TABLE AS OLD_EMP DELETE FROM RDEMP WHERE RDEMP.EMP_NO IN (SELECT EMP_NO FROM OLD_EMP);
• CREATE TRIGGER EMP_INSERT AFTER INSERT ON EMP FOR EACH ROW REFERENCING NEW ROW AS NEW_EMP INSERT (NEW_EMP.EMP_NO, CURRENT_TIMESRAMP, NEW_EMP.EMP_SAL, NEW_EMP.EMP_BONUS, (SELECT COUNT (*) FROM EMP WHERE EMP.DEPT_NO = NEW_EMP.DEPT_NO AND EMP.EMP_SAL + COALESCE (EMP.EMP_BONUS, 0) < NEW_EMP.EMP_SAL + COALESCE (NEW_EMP.EMP_BONUS, 0)), (SELECT COUNT (*) FROM EMP WHERE EMP.DEPT_NO = NEW_EMP.DEPT_NO AND EMP.EMP_SAL + COALESCE (EMP.EMP_BONUS, 0) > NEW_EMP.EMP_SAL + COALESCE (NEW_EMP.EMP_BONUS, 0));CREATE TRIGGER EMP_UPDATE AFTER UPDATE OF EMP_SAL, EMP_BONUS ON EMP FOR EACH ROW REFERENCING NEW ROW AS NEW_EMP INSERT INTO RDEMP (SELECT NEW.EMP_NO, CURRENT_TIMESRAMP, NEW_EMP.EMP_SAL, NEW_EMP.EMP_BONUS, SELECT COUNT (*) FROM EMP WHERE EMP.DEPT_NO = NEW_EMP.DEPT_NO AND EMP.EMP_SAL + COALESCE (EMP.EMP_BONUS, 0) < NEW_EMP.EMP_SAL + COALESCE (NEW_EMP.EMP_BONUS, 0)), (SELECT NEW.EMP_NO, CURRENT_TIMESRAMP, NEW_EMP.EMP_SAL, NEW_EMP.EMP_BONUS, SELECT COUNT (*) FROM EMP WHERE EMP.DEPT_NO = NEW_EMP.DEPT_NO AND EMP.EMP_SAL + COALESCE (EMP.EMP_BONUS, 0) < NEW_EMP.EMP_SAL + COALESCE (NEW_EMP.EMP_BONUS, 0)) FROM NEW_EMP WHERE ((SELECT COUNT (*) FROM EMP WHERE EMP.DEPT_NO = NEW_EMP.DEPT_NO AND EMP.EMP_SAL + COALESCE (EMP.EMP_BONUS, 0) < NEW_EMP.EMP_SAL + COALESCE (NEW_EMP.EMP_BONUS, 0)), SELECT COUNT (*) FROM EMP WHERE EMP.DEPT_NO = NEW_EMP.DEPT_NO AND EMP.EMP_SAL + COALESCE (EMP.EMP_BONUS, 0) > NEW_EMP.EMP_SAL + COALESCE (NEW_EMP.EMP_BONUS, 0))) <> (SELECT RDEMP.NUMBER_LESS, RDEMP.NUMBER_MORE FROM RDEMP.EVENT_TS = SELECT MAX (RDE.EVENT_TS) FROM RDEMP RDE WHERE RDE.EMP_NO = RDEMP.EMP_NO));CREATE TRIGGER EMP_DELETE AFTER DELETE ON EMP FOR EACH ROW REFERENCING OLD TABLE AS OLD_EMP DELETE FROM RDEMP WHERE RDEMP.EMP_NO IN (SELECT EMP_NO FROM OLD_EMP);
• CREATE TRIGGER EMP_INSERT AFTER INSERT ON EMP REFERENCING NEW ROW AS NEW_EMP FOR EACH ROW INSERT INTO RDEMP ROW (NEW_EMP.EMP_NO, CURRENT_TIMESRAMP, NEW_EMP.EMP_SAL, NEW_EMP.EMP_BONUS, (SELECT COUNT (*) FROM EMP WHERE EMP.DEPT_NO = NEW_EMP.DEPT_NO AND EMP.EMP_SAL + COALESCE (EMP.EMP_BONUS, 0) < NEW_EMP.EMP_SAL + COALESCE (NEW_EMP.EMP_BONUS, 0)), (SELECT COUNT (*) FROM EMP WHERE EMP.DEPT_NO = NEW_EMP.DEPT_NO AND EMP.EMP_SAL + COALESCE (EMP.EMP_BONUS, 0) > NEW_EMP.EMP_SAL + COALESCE (NEW_EMP.EMP_BONUS, 0)),CREATE TRIGGER EMP_UPDATE AFTER UPDATE OF EMP_SAL, EMP_BONUS ON EMP REFERENCING NEW ROW AS NEW_EMP FOR EACH ROW WHEN (((SELECT COUNT (*) FROM EMP WHERE EMP.DEPT_NO = NEW_EMP.DEPT_NO AND EMP.EMP_SAL + COALESCE (EMP.EMP_BONUS, 0) < NEW_EMP.EMP_SAL + COALESCE (NEW_EMP.EMP_BONUS, 0)), SELECT COUNT (*) FROM EMP WHERE EMP.DEPT_NO = NEW_EMP.DEPT_NO AND EMP.EMP_SAL + COALESCE (EMP.EMP_BONUS, 0) > NEW_EMP.EMP_SAL + COALESCE (NEW_EMP.EMP_BONUS, 0))) <> (SELECT RDEMP.NUMBER_LESS, RDEMP.NUMBER_MORE FROM RDEMP.EVENT_TS = SELECT MAX (RDE.EVENT_TS) FROM RDEMP RDE WHERE RDE.EMP_NO = RDEMP.EMP_NO)) INSERT (NEW_EMP.EMP_NO, CURRENT_TIMESRAMP, NEW_EMP.EMP_SAL, NEW_EMP.EMP_BONUS, (SELECT COUNT (*) FROM EMP WHERE EMP.DEPT_NO = NEW_EMP.DEPT_NO AND EMP.EMP_SAL + COALESCE (EMP.EMP_BONUS, 0) < NEW_EMP.EMP_SAL + COALESCE (NEW_EMP.EMP_BONUS, 0)), (SELECT COUNT (*) FROM EMP WHERE EMP.DEPT_NO = NEW_EMP.DEPT_NO AND EMP.EMP_SAL + COALESCE (EMP.EMP_BONUS, 0) > NEW_EMP.EMP_SAL + COALESCE (NEW_EMP.EMP_BONUS, 0));CREATE TRIGGER EMP_DELETE AFTER DELETE ON EMP REFERENCING OLD ROW AS OLD_EMP DELETE FROM RDEMP WHERE RDEMP.EMP_NO = OLD_EMP.EMP_NO;

Ответ