在table上建一个触发器,table中有一个字段mubiaobiao是xxx.yyyy的格式,需要根据每条插入记录的mubiaobiao字段值,写到不同的临时表,xxx是临时表的名字,yyyy是临时表中要写入的值,该怎么写?CREATE TRIGGER TRIGGER AFTER INSERT ON table
FOR EACH ROW
BEGIN
SET @v_char=(select targetdocid from table inserted)
(这里该怎么写) INSERT INTO xxx VALUES(xxx,1);
END;
FOR EACH ROW
BEGIN
SET @v_char=(select targetdocid from table inserted)
(这里该怎么写) INSERT INTO xxx VALUES(xxx,1);
END;
(
mubiaobiao varchar2(20)
);create table temp_tab01
(
val varchar2(10)
);create table temp_tab02
(
val varchar2(10)
);create or replace trigger trg_tab
after insert on tab
referencing new as new old as old
for each row
declare
v_mubiaobiao tab.mubiaobiao%type;
f_val varchar2(10);
l_val varchar2(10);
temp_sql varchar2(50);
begin
v_mubiaobiao:=:new.mubiaobiao;
f_val:=SUBSTR(v_mubiaobiao,1,INSTR(v_mubiaobiao,'.')-1);
l_val:=SUBSTR(v_mubiaobiao,INSTR(v_mubiaobiao,'.')+1,length(v_mubiaobiao));
temp_sql:='insert into '||f_val||' values('''||l_val||''')';
execute immediate temp_sql;
commit;
end trg_tab;insert into tab values('temp_tab01.abcd');
insert into tab values('temp_tab02.efgh');
改成
temp_sql:='insert into '||f_val||' values('||''''||l_val||''''||')';
substr(:new.mubiaobiao,4,4)是值
AFTER INSERT ON tablename
FOR EACH ROW
BEGIN
INSERT INTO tableXXX VALUES(:new.targetdocid,1);
END;
CREATE SEQUENCE tab$_SEQ START WITH 1 MINVALUE 1 MAXVALUE 2147483647create or replace trigger trg_tab
after insert on tab
referencing new as new old as old
for each row
declare
v_mubiaobiao tab.mubiaobiao%type;
f_val varchar2(10);
l_val varchar2(10);
temp_sql varchar2(50);
begin
v_mubiaobiao:=:new.mubiaobiao;
f_val:=SUBSTR(v_mubiaobiao,1,INSTR(v_mubiaobiao,'.')-1);
l_val:=SUBSTR(v_mubiaobiao,INSTR(v_mubiaobiao,'.')+1,length(v_mubiaobiao));
temp_sql:='insert into '||f_val||' values('tab$_SEQ.NEXTVAL','''||l_val||''')';
execute immediate temp_sql;
commit;
end trg_tab;报不允许使用序列,该怎么解决?
temp_sql:='insert into '||f_val||' values('tab$_SEQ.NEXTVAL','''||l_val||''')';
改成
temp_sql:='insert into '||f_val||' values('||tab$_SEQ.NEXTVAL||','||''''||l_val||''''||')';
试试看
报错:
PLS-00357:在此上下文中不允许表,试图或者序列引用
你这里又不是进行DDL操作,为什么要使用动态sql 语句呢?直接执行insert into tablename values()
这样就可以避免字符串连接的一些问题了。
CREATE SEQUENCE tab$_SEQ START WITH 1 MINVALUE 1 MAXVALUE 2147483647create or replace trigger trg_tab
after insert on tab
for each row
declare
v_mubiaobiao tab.mubiaobiao%type;
f_val varchar2(10);
l_val varchar2(10);
temp_sql varchar2(50);
begin
v_mubiaobiao:=:new.mubiaobiao;
f_val:=SUBSTR(v_mubiaobiao,1,INSTR(v_mubiaobiao,'.')-1);
l_val:=SUBSTR(v_mubiaobiao,INSTR(v_mubiaobiao,'.')+1,length(v_mubiaobiao));
insert into f_val values(tab$_SEQ.nextval,l_val);
--我觉得这里应该进行异常处理。如果要插入的表不存在呢?
end trg_tab;
f_val是变量,这样不能执行吧?
下面我写的一个trigger的部分语句--.....
declare
temp_par_id number;
---................
select max(id) into temp_par_id from zserver;
temp_par_id:=temp_par_id+1;
temp_par:='bmhier:'||temp_par_id;
if(zClass_id=46 and :new.zfather is not null ) theninsert into bumgt(id,PERSID,PARENT,CHILD,SYM,REL_TYPE)
values(temp_par_id,temp_par,:new.zfather,:new.id,'关系',407);
--...........................
CREATE OR REPLACE TRIGGER tab_I_TRIG AFTER INSERT ON tab
referencing new as new old as old
for each row
declare
v_id tab.id%type;
f_tbname varchar2(20);
l_storeid varchar2(10);
temp_sql varchar2(50);
begin
v_id:=:new.id;
f_tbname:=SUBSTR(v_id,1,INSTR(v_id,'.')-1);
f_tbname:='tab'||f_tbname;
f_tbname:=f_tbname||'$_temp';
l_storeid:=SUBSTR(v_id,INSTR(v_id,'.')+1,length(v_id));
temp_sql:='insert into '||f_tbname||' values('||TAB501$_SEQ.NEXTVAL||','||l_storeid||',1,0)';
execute immediate temp_sql;
end tab_I_TRIG;
报错:
PLS-00357:在此上下文中不允许表,试图或者序列引用
怎么解决?
所有代码如下:
create table tab
(
mubiaobiao varchar2(20)
);create table temp_tab01
(
id varchar2(10),
val varchar2(10)
);create table temp_tab02
(
id varchar2(10),
val varchar2(10)
);create sequence seq_tab_id
start with 1
increment by 1;create or replace trigger trg_tab
after insert on tab
referencing new as new old as old
for each row
declare
v_mubiaobiao tab.mubiaobiao%type;
f_val varchar2(10);
l_val varchar2(10);
temp_sql varchar2(50);
seq_id number(10);
begin
v_mubiaobiao:=:new.mubiaobiao;
f_val:=SUBSTR(v_mubiaobiao,1,INSTR(v_mubiaobiao,'.')-1);
l_val:=SUBSTR(v_mubiaobiao,INSTR(v_mubiaobiao,'.')+1,length(v_mubiaobiao));
select seq_tab_id.nextval into seq_id from dual;
temp_sql:='insert into '||f_val||' values('||to_char(seq_id)||','||''''||l_val||''''||')';
execute immediate temp_sql;
--exception
end trg_tab;
insert into tab values('temp_tab01.abcd');
insert into tab values('temp_tab02.efgh');