你的意思是删除一个员工信息时,并不实际删除该信息,只是把员工编号设置为负值吧。
可以使用替换型触发器:
create trigger trg_DelCustInfo
on 员工表
instead of delete
as
update 员工表
set 员工编号 = -员工编号
where 员工编号 = (select 员工编号 from deleted)
该代码经过了测试。
create or replace trigger delete_employee
before delete on 员工表
for each row
begin
insert into 员工表(employee_id,字段1,字段2,字段3)
value(-employee_id,字段1,字段2,字段3)
end;
在对某个表进行触发器操作的时候,不能再对该表进行操作了,所以楼上朋友的触发器报错
可否允许使用其他中间表做处理?必须要在原表更新吗?
SQL> drop table emp;
Table dropped.
SQL>
SQL> create table emp as select * from scott.emp;
Table created.
SQL>
SQL> create unique index idx_emp_empno on emp(empno) nologging parallel compute statistics;
Index created.
SQL>
SQL> create or replace package pkg_emp
2 is
3 type emp_list is table of emp%rowtype
4 index by binary_integer;
5 emp_list_old emp_list;
6 v_number binary_integer :=0;
7 end;
8 /
Package created.
SQL>
SQL>
SQL>
SQL>
SQL> create or replace trigger tr_emp_af_de_row after delete on emp for each row
2 declare
3 has_del exception;
4 p_old emp%rowtype;
5 begin
6 pkg_emp.v_number := pkg_emp.v_number+1;
7 p_old.ename := :old.ename;
8 p_old.job := :old.job;
9 p_old.mgr := :old.mgr;
10 p_old.hiredate := :old.hiredate;
11 p_old.sal := :old.sal;
12 p_old.comm := :old.comm;
13 p_old.deptno := :old.deptno;
14 if :old.empno > 0 then
15 p_old.empno := 0-:old.empno;
16 pkg_emp.emp_list_old(pkg_emp.v_number) := p_old;
17 else
18 --raise_application_error(-20001,'the record had bing deleted!');
19 p_old.empno := :old.empno;
20 pkg_emp.emp_list_old(pkg_emp.v_number) := p_old;
21 raise has_del;
22 end if;
23 exception
24 when has_del then
25 dbms_output.put_line('The record : empno--'||:old.empno||' had be deleted!');
26 when others then
27 dbms_output.put_line('index table key :'||pkg_emp.v_number);
28 pkg_emp.v_number := 0;
29 raise;
30 end;
31 /
Trigger created.
SQL>
SQL>
SQL>
SQL> create or replace trigger tr_emp_af_de_stat
2 after delete on emp
3 begin
4 forall i in 1 .. pkg_emp.v_number
5 insert into emp values pkg_emp.emp_list_old(i);
6 pkg_emp.v_number := 0;
7 end;
8 /
Trigger created.
SQL>
SQL>
SQL> declare
2 n number := 0;
3 begin
4 dbms_output.put_line('init the global var is:'||pkg_emp.v_number);
5 if pkg_emp.v_number <> 0 then
6 pkg_emp.v_number := n;
7 end if;
8 dbms_output.put_line('Now the global var is:'||pkg_emp.v_number);
9 end;
10 /
init the global var is:0
Now the global var is:0
PL/SQL procedure successfully completed.
SQL>
SQL> delete emp where empno = 7369;
1 row deleted.
SQL>
SQL> delete emp where job = 'CLERK';
The record : empno---7369 had be deleted!
4 rows deleted.
SQL>
SQL> delete emp where sal < 1100;
The record : empno---7900 had be deleted!
The record : empno---7369 had be deleted!
2 rows deleted.
SQL>