Главная /
Введение в модель данных SQL /
Предположим, что в базе данных поддерживаются две отдельные таблицы RDEMP и RMEMP с такой же структурой, что и таблица EMP, но содержащие данные о служащих, имеющих самый большой доход в своем отделе и среди участников своего проекта соответственно (если
Предположим, что в базе данных поддерживаются две отдельные таблицы RDEMP и RMEMP с такой же структурой, что и таблица EMP, но содержащие данные о служащих, имеющих самый большой доход в своем отделе и среди участников своего проекта соответственно (если для некоторого служащего размер премиальных неизвестен, премиальные не учитываются в общей сумме его дохода). Какие из приводимых ниже определений триггеров обеспечат требуемое наполнение этих таблиц?
вопрос
Правильный ответ:
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
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_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;
Сложность вопроса
50
Сложность курса: Введение в модель данных SQL
81
Оценить вопрос
Очень сложно
Сложно
Средне
Легко
Очень легко
Спасибо за оценку!
Комментарии:
Аноним
Спасибо за сайт
13 ноя 2019
Аноним
спасибо за ответ
26 май 2016
Другие ответы на вопросы из темы базы данных интуит.