CREATE TRIGGER EMP_UPDATE AFTER UPDATE OF EMP_SAL, EMP_BONUS ON EMP
REFERENCING OLD ROW AS OLD_EMP NEW ROW AS NEW_EMP
FOR EACH ROW
INSERT INTO AUDIT1
ROW (NEW_EMP.EMP_NO, CURRENT_TIMESTAMP,
OLD_EMP.EMP_SAL, OLD_EMP.EMP_BONUS,
NEW_EMP.EMP_SAL, NEW_EMP.EMP_BONUS);
CREATE TRIGGER EMP_DELETE AFTER DELETE ON EMP
REFERENCING OLD ROW AS OLD_EMP
FOR EACH ROW
INSERT INTO AUDIT1
ROW (OLD_EMP.EMP_NO, CURRENT_TIMESTAMP,
OLD_EMP.EMP_SAL, OLD_EMP.EMP_BONUS, 0, 0);
CREATE TRIGGER EMP_INSERT1 AFTER INSERT ON EMP
INSERT INTO AUDIT2
(SELECT DISTINCT NE.DEPT_NO, CURRENT_TIMESTAMP,
AVG (NE.EMP_SAL),
AVG (NE.EMP_BONUS)
FROM EMP NE
WHERE NE.DEPT_NO = NEW_EMP.DEPT_NO
GROUP BY NE.DEPT_NO
HAVING AVG (NE.EMP_SAL), AVG (NE.EMP_BONUS) <
(SELECT AVG (NE1.EMP_SAL), AVG (NE1.EMP_BONUS)
FROM EMP NE1
WHERE NE.PRO_NO = NE1.PRO_NO));
CREATE TRIGGER EMP_UPDATE1 AFTER UPDATE OF EMP_SAL, EMP_BONUS ON EMP
INSERT INTO AUDIT2
(SELECT DISTINCT NE.DEPT_NO, CURRENT_TIMESTAMP,
AVG (NE.EMP_SAL),
AVG (NE.EMP_BONUS)
FROM NEW_EMP NE
WHERE NE.DEPT_NO = NEW_EMP.DEPT_NO
GROUP BY NE.DEPT_NO
HAVING AVG (NE.EMP_SAL), AVG (NE.EMP_BONUS) <
(SELECT AVG (NE1.EMP_SAL), AVG (NE1.EMP_BONUS)
FROM NEW_EMP NE1
WHERE NE.PRO_NO = NE1.PRO_NO));
CREATE TRIGGER EMP_DELETE1 AFTER DELETE ON EMP
INSERT INTO AUDIT2
(SELECT DISTINCT NE.DEPT_NO, CURRENT_TIMESTAMP,
AVG (NE.EMP_SAL),
AVG (NE.EMP_BONUS)
FROM NEW_EMP NE
WHERE NE.DEPT_NO = NEW_EMP.DEPT_NO
GROUP BY NE.DEPT_NO
HAVING AVG (NE.EMP_SAL), AVG (NE.EMP_BONUS) <
(SELECT AVG (NE1.EMP_SAL), AVG (NE1.EMP_BONUS)
FROM NEW_EMP NE1
WHERE NE.PRO_NO = NE1.PRO_NO));
CREATE TRIGGER EMP_INSERT AFTER INSERT ON EMP
REFERENCING NEW ROW AS NEW_EMP
FOR EACH ROW
BEGIN ATOMIC
INSERT INTO AUDIT1
ROW (NEW_EMP.EMP_NO, CURRENT_TIMESTAMP, 0, 0,
NEW_EMP.EMP_SAL, NEW_EMP.EMP_BONUS);
INSERT INTO AUDIT2
(SELECT DISTINCT NE.DEPT_NO, CURRENT_TIMESTAMP,
AVG (NE.EMP_SAL), AVG (NE.EMP_BONUS)
FROM EMP NE
WHERE NE.DEPT_NO = NEW_EMP.DEPT_NO
GROUP BY NE.DEPT_NO
HAVING AVG (NE.EMP_SAL), AVG (NE.EMP_BONUS) <
(SELECT AVG (NE1.EMP_SAL), AVG (NE1.EMP_BONUS)
FROM EMP NE1
WHERE NE.PRO_NO = NE1.PRO_NO));
END;
CREATE TRIGGER EMP_UPDATE AFTER UPDATE OF EMP_SAL, EMP_BONUS ON EMP
REFERENCING OLD ROW AS OLD_EMP NEW ROW AS NEW_EMP
FOR EACH ROW
BEGIN ATOMIC
INSERT INTO AUDIT1
ROW (NEW_EMP.EMP_NO, CURRENT_TIMESTAMP,
OLD_EMP.EMP_SAL, OLD_EMP.EMP_BONUS,
NEW_EMP.EMP_SAL, NEW_EMP.EMP_BONUS);
INSERT INTO AUDIT2
(SELECT DISTINCT NE.DEPT_NO, CURRENT_TIMESTAMP,
AVG (NE.EMP_SAL), AVG (NE.EMP_BONUS)
FROM EMP NE
WHERE NE.DEPT_NO = NEW_EMP.DEPT_NO
GROUP BY NE.DEPT_NO
HAVING AVG (NE.EMP_SAL), AVG (NE.EMP_BONUS) <
(SELECT AVG (NE1.EMP_SAL), AVG (NE1.EMP_BONUS)
FROM EMP NE1
WHERE NE.PRO_NO = NE1.PRO_NO));
END;
CREATE TRIGGER EMP_DELETE AFTER DELETE ON EMP
REFERENCING OLD ROW AS OLD_EMP
FOR EACH ROW
BEGIN ATOMIC
BEGIN ATOMIC
INSERT INTO AUDIT1
ROW (OLD_EMP.EMP_NO, CURRENT_TIMESTAMP,
OLD_EMP.EMP_SAL, OLD_EMP.EMP_BONUS, 0, 0);
INSERT INTO AUDIT2
(SELECT DISTINCT NE.DEPT_NO, CURRENT_TIMESTAMP,
AVG (NE.EMP_SAL), AVG (NE.EMP_BONUS)
FROM EMP NE
WHERE NE.DEPT_NO = NEW_EMP.DEPT_NO
GROUP BY NE.DEPT_NO
HAVING AVG (NE.EMP_SAL), AVG (NE.EMP_BONUS) <
(SELECT AVG (NE1.EMP_SAL), AVG (NE1.EMP_BONUS)
FROM EMP NE1
WHERE NE.PRO_NO = NE1.PRO_NO));
END;
CREATE TRIGGER EMP_UPDATE AFTER ON EMP
REFERENCING OLD ROW AS OLD_EMP NEW ROW AS NEW_EMP
FOR EACH ROW
BEGIN ATOMIC
INSERT INTO AUDIT1
ROW (NEW_EMP.EMP_NO, CURRENT_TIMESTAMP,
OLD_EMP.EMP_SAL, OLD_EMP.EMP_BONUS,
NEW_EMP.EMP_SAL, NEW_EMP.EMP_BONUS);
INSERT INTO AUDIT2
(SELECT DISTINCT NE.DEPT_NO, CURRENT_TIMESTAMP,
AVG (NE.EMP_SAL), AVG (NE.EMP_BONUS)
FROM EMP NE
WHERE NE.DEPT_NO = NEW_EMP.DEPT_NO
GROUP BY NE.DEPT_NO
HAVING AVG (NE.EMP_SAL), AVG (NE.EMP_BONUS) <
(SELECT AVG (NE1.EMP_SAL), AVG (NE1.EMP_BONUS)
FROM EMP NE1
WHERE NE.PRO_NO = NE1.PRO_NO));
END; -
Для определения базовых и типизированных таблиц используется один и тот же оператор CREATE TABLE. Каким образом, глядя на определение таблицы, можно точно сказать, к какой из двух категорий относится это определение?
в определении типизированной таблицы присутствует раздел UNDER
в определении типизированной таблицы присутствует определение самоссылающегося столбца -
в определении типизированной таблицы присутствует раздел OF
