SQL 触发器不适用于审核

Question

要求： 一旦对表中的列值进行任何更新OPS_BUILD_ACTIVITY_LABEL，我们必须在表中插入一行ACTIVITY_LABEL_AUDIT_LOG该行具有列TABLE_NAME, COLUMN_NAME, OLD_VALUE, NEW_VALUE, UPDATED_BY, UPDATED_DTM

我创建了触发器，如下所示。创建具有以下编译错误的触发器：

PL/SQL: SQL Statement ignored
PL/SQL: ORA-00984: column not allowed here

请帮帮我。

create or replace TRIGGER ACTIVITY_LABEL_TRIGGER
AFTER UPDATE OF ACTIVITY_LABEL, NOTES
ON OPS_BUILD_ACTIVITY_LABEL
FOR EACH ROW
BEGIN
INSERT INTO ACTIVITY_LABEL_AUDIT_LOG
( TABLE_NAME,
COLUMN_NAME,
OLD_VALUE,
NEW_VALUE,
UPDATED_BY,
UPDATED_DTM)   
VALUES   
('OPS_BUILD_ACTIVITY_LABEL',
'ACTIVITY_LABEL',
OLD.ACTIVITY_LABEL,
NEW.ACTIVITY_LABEL,
NEW.LAST_UPDATED_BY,
NEW.LAST_UPDATED_DTM); 
END;

Answer 1

只需在旧的和新的前面加上冒号(:( 如下(ORA-00984因此而提高，顺便说一下，我认为列名没有问题(：

CREATE OR REPLACE TRIGGER ACTIVITY_LABEL_TRIGGER
AFTER ON OPS_BUILD_ACTIVITY_LABEL
FOR EACH ROW
BEGIN
if ( nvl(:OLD.ACTIVITY_LABEL,'xYz#@!') != nvl(:NEW.ACTIVITY_LABEL,'xYz#@!')
or nvl(:OLD.NOTES,'xYz#@!') != nvl(:NEW.NOTES,'xYz#@!') ) then
INSERT INTO ACTIVITY_LABEL_AUDIT_LOG
(TABLE_NAME,
COLUMN_NAME,
OLD_VALUE,
NEW_VALUE,
UPDATED_BY,
UPDATED_DTM)
VALUES  
('OPS_BUILD_ACTIVITY_LABEL',
'ACTIVITY_LABEL',
:OLD.ACTIVITY_LABEL,
:NEW.ACTIVITY_LABEL,
:NEW.LAST_UPDATED_BY,
:NEW.LAST_UPDATED_DTM);
end if;
END;

关于您的评论，我删除了部分UPDATE OF ACTIVITY_LABEL, NOTES，并对IF 语句中ACTIVITY_LABEL和NOTES两列进行了比较。如果要获取每个更新操作的每个日志，还应删除IF 语句。

Answer 2

感谢您的所有回答，根据您的答案，我终于通过以下代码达到了要求：

create or replace TRIGGER ACTIVITY_LABEL_TRIGGER
AFTER UPDATE OF ACTIVITY_LABEL, NOTES
ON OPS_BUILD_ACTIVITY_LABEL
FOR EACH ROW
BEGIN
if ( nvl(:OLD.ACTIVITY_LABEL,'xYz#@!') != nvl(:NEW.ACTIVITY_LABEL,'xYz#@!')) then
INSERT INTO ACTIVITY_LABEL_AUDIT_LOG
(TABLE_NAME,
COLUMN_NAME,
OLD_VALUE,
NEW_VALUE,
UPDATED_BY,
UPDATED_DTM)
VALUES  
('OPS_BUILD_ACTIVITY_LABEL',
'ACTIVITY_LABEL',
:OLD.ACTIVITY_LABEL,
:NEW.ACTIVITY_LABEL,
:NEW.LAST_UPDATED_BY,
:NEW.LAST_UPDATED_DTM);
end if;
if (nvl(:OLD.NOTES,'xYz#@!') != nvl(:NEW.NOTES,'xYz#@!') ) then
INSERT INTO ACTIVITY_LABEL_AUDIT_LOG
(TABLE_NAME,
COLUMN_NAME,
OLD_VALUE,
NEW_VALUE,
UPDATED_BY,
UPDATED_DTM)
VALUES  
('OPS_BUILD_ACTIVITY_LABEL',
'NOTES',
:OLD.NOTES,
:NEW.NOTES,
:NEW.LAST_UPDATED_BY,
:NEW.LAST_UPDATED_DTM);
end if;
END;