Question

创建oracle触发器，要求每当要删除一个员工信息的时候，改为将其员工编号改为负值。

Answer 1

你的意思是删除一个员工信息时，并不实际删除该信息，只是把员工编号设置为负值吧。
可以使用替换型触发器：

create trigger trg_DelCustInfo
on 员工表
instead of delete
as
update 员工表
set 员工编号 = -员工编号
where 员工编号 = (select 员工编号 from deleted)

该代码经过了测试。

追问

instead of 触发器不是只可以用在视图上的么？？

追答

instead of这种触发器可以应用于表或视图，用触发器中的代码代替更新操作(插入、修改和删除)。

追问

毛，我用的是oracle11g sql plus，里面说instead of触发器不能用于表，而且第四行的as根本不是触发器语法好不好，触发器是用begin、end，开始、结束的
你用的是什么oracle版本啊，测试成功了？

追答

对不起，看错了，我以为是SQL Server。
看来，要实现你的要求，必须使用自治事务了。
完整的测试过程如下：

--创建测试数据表
create table scott.customer
(
custid int primary key,
custname varchar(20)
);

--添加样例数据
insert into scott.customer values(1, 'Machael');
insert into scott.customer values(2, 'White');

--应用自治事务的存储过程
create or replace procedure prc_DelCustInfo
(
cid int,
cname varchar
)
as
pragma autonomous_transaction;
begin
insert into scott.customer values(cid, cname);
commit;
end;

--触发器调用存储过程
create or replace trigger trg_DelCustInfo
after delete on scott.customer
for each row
begin
prc_DelCustInfo(-:old.custid, :old.custname);
end;

--测试触发器的执行
delete scott.customer where custid=1;
commit;

Answer 2

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;

追问

你自己运行过了么，这样触发器创建成功了，但是delete from emp；运行后它说触发器运行失败啊

Answer 3

在对某个表进行触发器操作的时候，不能再对该表进行操作了，所以楼上朋友的触发器报错
可否允许使用其他中间表做处理？必须要在原表更新吗？

Answer 4

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>