oracle - Pseudorecord best practice when trigger fires but value should never change
问题描述
Consider the following tables and trigger, is there a need to specify :new.employee_id in the insert statement or is this generally considered best practice. The trigger will only fire on the salary column of the table and the employee_id should not be affected. Is the :new.employee_id syntax just good practice when creating triggers or unnecessary? Could there be a potential issue if this is not added?
Link to Oracle HR SCHEMA employees table Employees Table HR Schema
CREATE TABLE salary_log (
whodidit VARCHAR2(25), whendidit timestamp,
oldsalary NUMBER,
newsalary NUMBER,
emp_affected NUMBER);
CREATE OR REPLACE TRIGGER saltrig
AFTER INSERT OR UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
INSERT INTO salary_log
VALUES(user,sysdate, :old.salary, :new.salary, :new.employee_id);
END;
解决方案
If you were to NOT include it:
- You would have to alter your trigger code to explicitly name the columns you are supplying values for, and
- Your log table would show that someone's salary changed, who did it and what the old and new values are, but you would not know whose salary was changed. The data in that column would be null.
Is the :new.employee_id syntax just good practice when creating triggers or unnecessary?
If you want this value in your log table, then it is required.
推荐阅读
- javascript - 单击按钮时如何删除表中的数据?
- django - 为什么打印在 Geodjango 中显示 SRID 3847?
- git - Git不时消失
- reactjs - 可重用的 FlatList 组件 React Native 项目问题
- node.js - Nodejs程序运行但前端未显示在谷歌计算引擎的浏览器上
- python - 仅基于条件、groupby 和某些行,用以前的行值填充行
- python - 如何以长期顺序使用多处理?
- javascript - 无法使用three.js显示3D obj文件+纹理
- javascript - TypeError:.then 不是函数
- c++ - 使用柯南将 PahoMqttCpp 链接为 CMake 中的静态库