我写的语句如下：[emp是scott用户下默认就有的表]
(1).after语句触发器
create table aud_upd_tab(
host varchar2(30),statement varchar2(100),exectime date);
create or replace trigger tr_upd_emp
after update on emp
declare
sql_txt ora_name_list_t;
v_stmt varchar2(100);
n binary_integer;
begin
n :=ora_sql_txt(sql_txt);
for i in 1..n loop
v_stmt :=v_stmt||sql_txt(i);
end loop;
insert into aud_upd_tab values(sys_context('userenv','host'),v_stmt,sysdate);
end;
/
update emp set sal=30 where empno=7369;
执行上面的update语句则报错如图片所示：
select statement,exectime from aud_upd_tab;
(2)after行触发器
create table audit_emp_change(name varchar2(10),oldsal number(6,2),
newsal number(6,2),time DATE);
create or replace trigger tr_sal_change
after update of sal on emp for each row
declare
v_temp int;
begin
select count(*) into v_temp from audit_emp_change where name=:old.ename;
if v_temp=0 then
insert into audit_emp_change values(:old.ename,:old.sal,:new.sal,sysdate);
else
update audit_emp_change set oldsal=:old.sal,newsal=:new.sal,time=sysdate where name=:old.ename;
end if;
end;
/
update emp set sal=sal*1.1 where empno=7369;
执行上面的update语句则报错如图片所示：
select * from audit_emp_change;
希望各位大侠帮帮忙，很急哦！
