使用触发器审核操作 [英] Audit operations with a trigger
问题描述
我有一个在某些情况下会触发的触发器，当我更新EMPLOYEES table中的某些数据(特别是inserting，deleting和updating comm_pct和salary时)时，所做的更改已注册到下表中:
I have a trigger that fires in certain conditions, and when I update some data in EMPLOYEES table (specifically when inserting, deleting and updating comm_pct and salary) the changes that were made are registered into the following table:
CREATE TABLE "HR"."AUDIT_E" ("USR" VARCHAR2(30 BYTE) DEFAULT USER,
"DATE" DATE DEFAULT SYSDATE,
"DML_TYPE" VARCHAR2), -- UPDATE, INSERT, DELETE
"OLD_EMPLOYEE_ID" NUMBER,
"OLD_FIRST_NAME" VARCHAR2,
...,--more fields
"OLD_JOB_ID" VARCHAR2,
"OLD_SALARY" NUMBER,
"OLD_COMMISSION_PCT" NUMBER,
"NEW_FIRST_NAME" VARCHAR2,
..., -- more fields!
"NEW_JOB_ID" VARCHAR2,
"NEW_SALARY" NUMBER,
"NEW_COMMISSION_PCT" NUMBER)
我的问题是:当updating rows与另一个值(如email与comm_pct和其他字段一起使用时，如何在AUDIT_E中做一个INSERT(因为我必须在其中注册新值和旧值) ，除了仅更新comm_pct和salary)?因为我的触发器具有以下结构:
My question is: How can I do an INSERT in AUDIT_E (Because I must register old and new values into it) when updating rows with another values (as email with comm_pct and other fields, besides only updating comm_pct and salary)? Because my trigger has the following structure:
IF DELETING THEN
--some actions
-- Insert into AUDIT_E(...) values...
ELSIF INSERTING THEN
--some actions
-- Insert into AUDIT_E(...) values...
ELSIF UPDATING ('a field') THEN --I have two of these
--some actions
-- Insert into AUDIT_E(...) values...comm_pct/salary
非常感谢您能为我提供帮助，对不起我的英语水平.
Thank you very much if you can help me and sorry for my english.
当我是inserting，deleting行和updating仅comm_pct和salary时，我的触发器将精细的registring更改为audit_e:
My trigger runs fine registring changes into audit_e when I am inserting, deleting rows and updating only comm_pct and salary:
AUDIT_E:
ID |Oper|Old_Name|Old_job_id|Old_comm_pct|Old_Salary|New_name|New_job_id|New_comm_pct|New_salary
------------------------------------------------------------------------------------------------
1 |Ins | NULL | NULL | NULL | NULL |Kappa | SA_REP | 0.2 | 4980
2 |Upd | Kappa | SA_REP | 0.2 | 4980 | NULL | NULL | 0.3 | NULL
3 |Upd | Kappa | SA_REP | 0.3 | 4980 | NULL | NULL | NULL | 5000
4 |Del | Kappa | SA_REP | 0.3 | 4980 | NULL | NULL | NULL | NULL
但是，例如，当我更改job_id(进行附加的elsif更新)时，所做的更改将错误地保存到audit_e中:
But when I am changing the job_id for example (putting an additional elsif update), the changes are saved into audit_e wrong:
AUDIT_E:
ID |Oper|Old_Name|Old_job_id|Old_comm_pct|Old_Salary|New_name|New_job_id|New_comm_pct|New_salary
------------------------------------------------------------------------------------------------
1 |Upd |Kappa | SA_REP | 0.2 | 4980 | NULL | NULL | NULL | NULL
我希望将这些更改保存到audit_e表中，如下所示:
And I want those changes saved into audit_e table like this:
AUDIT_E:
ID |Oper|Old_Name|Old_job_id|Old_comm_pct|Old_Salary|New_name|New_job_id|New_comm_pct|New_salary
------------------------------------------------------------------------------------------------
1 |Upd |Kappa | SA_REP | 0.2 | 4980 | NULL | IT_PROG | NULL | NULL
推荐答案
我仍然不确定我是否理解这个问题.我的猜测是您只想要类似的东西
I'm still not sure that I understand the question. My guess is that you just want something like
CREATE OR REPLACE TRIGGER trigger_name
AFTER INSERT OR UPDATE OR DELETE
ON employees
FOR EACH ROW
BEGIN
INSERT INTO audit_e( dml_type,
old_employee_id, new_employee_id,
old_first_name, new_first_name,
...
)
VALUES( CASE WHEN deleting
THEN 'D'
WHEN inserting
THEN 'I'
WHEN updating
THEN 'U'
ELSE 'X'
END,
:old.employee_id, :new.employee_id,
:old.first_name, :new.first_name,
...
);
END;
也就是说，我不清楚您为什么每次都要麻烦将旧数据和新数据存储在审核表中. old_*值将始终与上一行的new_值相同.通常将new_值存储在审计表中是很有意义的.
That said, it's not clear to me why you would bother storing the old and new data in your audit table each time. The old_* values are always going to be identical to the new_ values from the prior row. It generally makes sense to just store the new_ values in the audit table.
这篇关于使用触发器审核操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
