merge into在触发器的用法

Question

各位大侠，我现在又这样一个行级触发器，当PICKLIST表里面有数据插入的时候，我就与其他表联合查询（见触发器语句），
当不满足条件的时候我就把数据插入到PICKLIST_BKUP表里面去，但是我编译这个语句的时候
错误(6,1): PL/SQL: SQL Statement ignored
错误(6,12): PL/SQL: ORA-00913: 值过多触发器见下面
create or replace
trigger trig_picklist_backup 
after insert on picklist
begin
merge into picklist_bkup
using (select CompList.AMOUNT-CompList.ERRORS-CompList.USED Amount, PickList.AMOUNTNEED, 
PickList.COMPID,PickList.MCID,PickList.STATION,PickList.SLOT,PickList.SUBSLOT,PickList.PICKLISTID,PickList.STATUS,OperatorTrace."TIMESTAMP",
PickList.REELCOUNTER,PickList.COMPNAME,PickList.STOCKLOCATION from CompList 
inner join PickList on CompList.CompID =  PickList.CompID 
inner join OperatorTrace on PickList.CompID = OperatorTrace.CompID 
where operatortrace.ActionID = 302 ) b1
on (picklist_bkup.mcid = b1.mcid and picklist_bkup.station = b1.station and picklist_bkup.slot = b1.slot and picklist_bkup.subslot = b1.subslot and picklist_bkup.picklistid = b1.picklistid and picklist_bkup.reelcounter = b1.reelcounter)
when not matched then 
insert values (b1.Amount, b1,amountneed,b1.compid,b1.mcid,b1.station,b1.slot,b1.subslot,b1.picklistid,b1.status,b1."timestamp",b1.reelcounter,b1.compname,b1.stocklocation);
end;请大家帮我看下，谢谢。

Answer 1

ORA-00913: 值过多 picklist_bkup 表有多少列？

Answer 2

insert values (b1.Amount, b1,amountneed,b1.compid,b1.mcid,b1.station,b1.slot,b1.subslot,b1.picklistid,b1.status,b1."timestamp",b1.reelcounter,b1.compname,b1.stocklocation); 
这里的列数可能比实际的字段数多了

Answer 3

检查一下picklist_bkup表中的字段个数与你插入的值的个数是否一致

Answer 4

回4楼跟5楼，列数刚检查了，是一致的都是13，我帖的后面insert values哪里没复制完整，我再帖下。create or replace
trigger trig_picklist_backup 
after insert on picklist
begin
merge into picklist_bkup
using (select CompList.AMOUNT-CompList.ERRORS-CompList.USED Amount, PickList.AMOUNTNEED, 
PickList.COMPID,PickList.MCID,PickList.STATION,PickList.SLOT,PickList.SUBSLOT,PickList.PICKLISTID,PickList.STATUS,OperatorTrace."TIMESTAMP",
PickList.REELCOUNTER,PickList.COMPNAME,PickList.STOCKLOCATION from CompList 
inner join PickList on CompList.CompID =  PickList.CompID 
inner join OperatorTrace on PickList.CompID = OperatorTrace.CompID 
where operatortrace.ActionID = 302 ) b1
on (picklist_bkup.mcid = b1.mcid and picklist_bkup.station = b1.station and picklist_bkup.slot = b1.slot and picklist_bkup.subslot = b1.subslot and picklist_bkup.picklistid = b1.picklistid and picklist_bkup.reelcounter = b1.reelcounter)
when not matched then 
insert values (b1.Amount, b1,amountneed,b1.compid,b1.mcid,b1.station,b1.slot,b1.subslot,b1.picklistid,b1.status,b1."timestamp",b1.reelcounter,b1.compname,b1.stocklocation);
end;

Answer 5

可以insert后指定要插入picklist_bkup的哪些字段

Answer 6

我USING后面的子查询的所有字段都要插入PICKLIST_BKUP表里面去的，所以我没有在INSERT后面指定字段，这个语法是没问题的。

Answer 7

CREATE TABLE "SYSTEM"."PICKLIST_BKUP" 
   ( "AMOUNT" NUMBER(10,0), 
"AMOUNTNEED" NUMBER(10,0), 
"COMPID" VARCHAR2(32), 
"MCID" NUMBER(6,0) NOT NULL ENABLE, 
"STATION" NUMBER(4,0) NOT NULL ENABLE, 
"SLOT" NUMBER(4,0) NOT NULL ENABLE, 
"SUBSLOT" NUMBER(4,0) NOT NULL ENABLE, 
"PICKLISTID" VARCHAR2(32) NOT NULL ENABLE, 
"STATUS" NUMBER(4,0), 
"TIMESTAMP" DATE, 
"REELCOUNTER" NUMBER(4,0) NOT NULL ENABLE, 
"COMPNAME" VARCHAR2(32), 
"STOCKLOCATION" VARCHAR2(64)
   )CREATE TABLE "SYSTEM"."PICKLIST" 
   ( "AMOUNT" NUMBER(10,0), 
"AMOUNTNEED" NUMBER(10,0), 
"COMPID" VARCHAR2(32), 
"MCID" NUMBER(6,0) NOT NULL ENABLE, 
"STATION" NUMBER(4,0) NOT NULL ENABLE, 
"SLOT" NUMBER(4,0) NOT NULL ENABLE, 
"SUBSLOT" NUMBER(4,0) NOT NULL ENABLE, 
"PICKLISTID" VARCHAR2(32) NOT NULL ENABLE, 
"STATUS" NUMBER(4,0), 
"TIMESTAMP" DATE, 
"REELCOUNTER" NUMBER(4,0) NOT NULL ENABLE, 
"COMPNAME" VARCHAR2(32), 
"STOCKLOCATION" VARCHAR2(64)
   )
MCID STATION SLOT SUBSLOT PICKLISTID REELCOUNTER主键CREATE TABLE "SYSTEM"."COMPLIST" 
   ( "COMPID" VARCHAR2(32) NOT NULL ENABLE, 
"FEEDERID" NUMBER(8,0), 
"COMPNAME" VARCHAR2(32), 
"OPENTIMESTAMP" DATE, 
"NUMDRYLEFT" NUMBER(10,0), 
"MCID" NUMBER(6,0), 
"STATION" NUMBER(4,0), 
"SLOT" NUMBER(4,0), 
"SUBSLOT" NUMBER(4,0), 
"USED" NUMBER(10,0), 
"ERRORS" NUMBER(10,0), 
"AMOUNT" NUMBER(10,0), 
"CORRECTION" NUMBER(10,0), 
"STATUS" NUMBER(10,0), 
"COMPPRPCB" NUMBER(10,0), 
"DRYTIMESTAMP" DATE, 
"PREPARATIONSTATUS" NUMBER(10,0), 
"LASTSEENONMACHINE" NUMBER(10,0)
   )
COMPID 主键CREATE TABLE "SYSTEM"."OPERATORTRACE" 
   ( "TIMESTAMP" DATE NOT NULL ENABLE, 
"OPERATORID" VARCHAR2(24) NOT NULL ENABLE, 
"ACTIONID" NUMBER(10,0) NOT NULL ENABLE, 
"MCID" NUMBER(6,0) NOT NULL ENABLE, 
"STATION" NUMBER(4,0) NOT NULL ENABLE, 
"SLOT" NUMBER(4,0) NOT NULL ENABLE, 
"SUBSLOT" NUMBER(4,0), 
"FEEDERID" NUMBER(8,0) NOT NULL ENABLE, 
"COMPID" VARCHAR2(32), 
"DESCRIPTION" VARCHAR2(128)
   )
TIMESTAMP OPERATORID ACTIONID MCID主键请帮帮忙，谢谢

Answer 8

仔细看看，一个,号惹的祸……这里应该是b1.amountneed！！！

Answer 9

将b1."timestamp"改为b1.timestamp，或者b1."TIMESTAMP",我这里已经编译通过的。

Answer 10

SQL> create or replace trigger trig_picklist_backup
  2  after insert on picklist
  3  begin
  4  merge into picklist_bkup
  5  using (select CompList.AMOUNT-CompList.ERRORS-CompList.USED Amount, PickLis
t.AMOUNTNEED,
  6  PickList.COMPID,PickList.MCID,PickList.STATION,PickList.SLOT,PickList.SUBSL
OT,PickList.PICKLISTID,PickList.STATUS,OperatorTrace."TIMESTAMP",
  7  PickList.REELCOUNTER,PickList.COMPNAME,PickList.STOCKLOCATION from CompList  8  inner join PickList on CompList.CompID =  PickList.CompID
  9  inner join OperatorTrace on PickList.CompID = OperatorTrace.CompID
 10  where operatortrace.ActionID = 302 ) b1
 11  on (picklist_bkup.mcid = b1.mcid and picklist_bkup.station = b1.station and
 picklist_bkup.slot = b1.slot and picklist_bkup.subslot = b1.subslot and picklis
t_bkup.picklistid = b1.picklistid and picklist_bkup.reelcounter = b1.reelcounter
)
 12  when not matched then
 13  insert values (b1.Amount, b1.amountneed,b1.compid,b1.mcid,b1.station,b1.slo
t,b1.subslot,b1.picklistid,b1.status,b1.timestamp,b1.reelcounter,b1.compname,b1.
stocklocation);
 14  end;
 15  /触发器已创建SQL> show error
没有错误。
SQL>