create or replace trigger monitor_staff_dict
AFTER update of INPUT_CODE
on comm.staff_dict
referencing OLD AS "OLD" NEW AS "NEW"
for EACH ROW
declare
user1 varchar2(20);
currtime date;
ip varchar2(14);
BEGIN select sysdate into currtime from dual;
select sys_context('userenv','ip_address'),sys_context('userenv','SESSION_USER') into ip,user1 from dual;
insert into comm.monitor_staff(username,currtime,ip,log_input_code,input_code)
values(user1,currtime,ip,:OLD_emp.INPUT_CODE,:NEW_emp.INPUT_CODE);
end ;这是我的语句···
PLS-00049: bad bind variable 'OLD_emp.INPUT_CODE'
这是错误信息~~~~~我想做一个触发器 监督A表INPUT_CODE列的update 如果更新就把对方的用户名,IP,时间,还有修改前和修改后的数据插到B表中~~~~~可是创建的时候报错··我还不是很理解referencing 的功能··向大家求教一下·
AFTER update of INPUT_CODE
on comm.staff_dict
referencing OLD AS "OLD" NEW AS "NEW"
for EACH ROW
declare
user1 varchar2(20);
currtime date;
ip varchar2(14);
BEGIN select sysdate into currtime from dual;
select sys_context('userenv','ip_address'),sys_context('userenv','SESSION_USER') into ip,user1 from dual;
insert into comm.monitor_staff(username,currtime,ip,log_input_code,input_code)
values(user1,currtime,ip,:OLD_emp.INPUT_CODE,:NEW_emp.INPUT_CODE);
end ;这是我的语句···
PLS-00049: bad bind variable 'OLD_emp.INPUT_CODE'
这是错误信息~~~~~我想做一个触发器 监督A表INPUT_CODE列的update 如果更新就把对方的用户名,IP,时间,还有修改前和修改后的数据插到B表中~~~~~可是创建的时候报错··我还不是很理解referencing 的功能··向大家求教一下·
--这句改成
values(user1,currtime,ip,:OLD.INPUT_CODE,:NEW.INPUT_CODE);
--referencing是給old,new起個別名,就像下面的,你可以用old_temp,但不可以用old了
--如果沒referencing,你可寫成:old.INPUT_CODE,:new.INPUT_CODE
create or replace trigger monitor_staff_dict
AFTER update of INPUT_CODE
on comm.staff_dict
referencing OLD AS "OLD_TEMP" NEW AS "NEW_TEMP"
for EACH ROW
declare
user1 varchar2(20);
currtime date;
ip varchar2(14);
BEGIN select sysdate into currtime from dual;
select sys_context('userenv','ip_address'),sys_context('userenv','SESSION_USER') into ip,user1 from dual;
insert into comm.monitor_staff(username,currtime,ip,log_input_code,input_code)
values(user1,currtime,ip,:OLD_TEMP.INPUT_CODE,:NEW_TEMP.INPUT_CODE);
end ;
/