Question

Oracle 触发器怎么写?

例如现在有一个A表,一个B表 ,A表中有2个字段,一个ID,一个fieldname,
在A表执行insert 操作之后,在B表中添加一个新字段,字段名就是刚刚添加的fieldname
高悬赏求助.

Answer 1

这个只能遗憾的告诉你，不能实现
因为触发器中不能运行 ddl语句和commit，rollback语句
所谓的ddl语句就是用语定义和管理数据库中的对象，如Create,Alter，Drop，truncate等，DDL操作是隐性提交的！

其实刚才写了一个，创建的时候没问题，但是往a里插入数据时报错
create or replace trigger t_add_col
after insert on a
for each row
begin
execute immediate 'alter table b add '||:new.fieldname||' varchar2(30)';
end;

错误就是ora-04092，你可以看一下

Answer 2

create or replace trigger t_insert
after insert
on A
for each row
declare
new_field varchar2(30);
begin
select fieldname into new_field
from (select id,fieldname,rownum rn from A order by 3 desc)
where rownum<=1;
execute immediate 'alter table B add ' || new_field || ' varchar2(30)';
end;

Answer 3

-- 可以使用触发器+DBMS_JOB实现，以下是测试语句
-- 虽然可以实现，但是不推荐使用这样的方法实现数据库列的添加方法
-- 测试环境 Oracle9i
Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0
Connected as scott

sxdtgsh> drop table a;

Table dropped

sxdtgsh> create table a(id integer, fieldname varchar2(30));

Table created

sxdtgsh>
sxdtgsh> create or replace trigger t_add_col
2 after insert on a
3 for each row
4 declare
5 v_sql varchar2(500);
6 myjob integer;
7 begin
8 v_sql:= 'begin execute immediate ' || chr(39) || 'alter table b add '||:new.fieldname||' varchar2(30)' || Chr(39) || '; end;';
9 dbms_output.put_line(v_sql);
10 dbms_job.submit(myjob, v_sql, sysdate, null, false);
11 end;
12 /

Trigger created

sxdtgsh> drop table b;

Table dropped

sxdtgsh> create table b(ID integer);

Table created

sxdtgsh> desc b;
Name Type Nullable Default Comments
---- ------- -------- ------- --------
ID INTEGER Y

sxdtgsh> insert into A(id, fieldname) values (1, 'COL1');

begin execute immediate 'alter table b add COL1 varchar2(30)'; end;

1 row inserted

sxdtgsh> desc b
Name Type Nullable Default Comments
---- ------- -------- ------- --------
ID INTEGER Y

sxdtgsh> commit;

Commit complete

sxdtgsh> desc b;
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
ID INTEGER Y
COL1 VARCHAR2(30) Y

sxdtgsh> insert into A(id, fieldname) values (2, 'COL2');

begin execute immediate 'alter table b add COL2 varchar2(30)'; end;

1 row inserted

sxdtgsh> desc b
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
ID INTEGER Y
COL1 VARCHAR2(30) Y

sxdtgsh> rollback;

Rollback complete

sxdtgsh> desc b
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
ID INTEGER Y
COL1 VARCHAR2(30) Y

sxdtgsh> insert into A(id, fieldname) values (2, 'COL2');

begin execute immediate 'alter table b add COL2 varchar2(30)'; end;

1 row inserted

sxdtgsh> commit;

Commit complete
--因为是Job是异步的，所以需要等待job执行完成才可以看到结果。
sxdtgsh> desc b
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
ID INTEGER Y
COL1 VARCHAR2(30) Y

sxdtgsh> desc b
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
ID INTEGER Y
COL1 VARCHAR2(30) Y
COL2 VARCHAR2(30) Y