oracle一个触发器，这么简单也不能编译通过，晕死

Question

create or replace trigger TIB_ERCP_T_PayResult
  before insert on ercp_payresult  
  for each row
declare
  -- local variables here
begin
--update t1 set t1.ReturnAddress = t2.ReturnAddress,t1.AccountNum=t2.AccountNum,t1.ProcessCode=t2.ProcessCode,t1.Amount=t2.Amount,t1.CurCode=t2.CurCode,t1.TransDatetime=t2.TransDatetime,t1.AcqSsn=t2.AcqSsn,t1.Ltime=t2.Ltime,t1.Ldate=t2.Ldate,t1.SettleDate=t2.SettleDate,t1.UpsNo=t2.UpsNo,t1.TsNo=t2.TsNo,t1.Reference=t2.Reference,t1.RespCode=t2.RespCode,t1.TerminalNo=t2.TerminalNo,t1.MerchantNo=t2.MerchantNo,t1.OrderNo=t2.OrderNo,t1.OrderState=t2.OrderState,t1.Description=t2.Description,t1.Re=t2.Re,t1.ValidTime=t2.ValidTime,t1.OrderType=t2.OrderType,t1.TransData=t2.TransData,t1.Pin=t2.Pin,t1.LoginPin=t2.LoginPin,t1.Mac=t2.Mac,t1.CreateTime=t2.CreateTime,t1.State=t2.State from ERCP_PayResult t1 inner join inserted  t2 on t1.OrderNo=t2.OrderNo;/*update ERCP_PayResult a set(ReturnAddress,AccountNum,ProcessCode,Amount,CurCode,TransDatetime,AcqSsn,Ltime,Ldate,SettleDate,UpsNo,TsNo,Reference,RespCode,TerminalNo,MerchantNo,OrderNo,OrderState,Description,Re,ValidTime,OrderType,TransData,Pin,LoginPin,Mac,CreateTime,State)
=select(b.ReturnAddress,b.AccountNum,b.ProcessCode,b.Amount,b.CurCode,b.TransDatetime,b.AcqSsn,b.Ltime,b.Ldate,b.SettleDate,b.UpsNo,b.TsNo,b.Reference,b.RespCode,b.TerminalNo,b.MerchantNo,b.OrderNo,b.OrderState,b.Description,b.Re,b.ValidTime,b.OrderType,b.TransData,b.Pin,b.LoginPin,b.Mac,b.CreateTime,b.State from
inserted b where b.OrderNo=a.OrderNo)
where exists (select 1 
from ERCP_PayResult b 
where b.OrderNo=a.OrderNo 
) */select * from ERCP_PayResult
end TIB_ERCP_T_PayResult;我的目的就是想在插入的时候如果存在OrderNo 则更新相应的字段，可是根本就编译不通过，怎么办？

Answer 1

select * from ERCP_PayResult
触发器，不能只是一个查询语句

Answer 2

create or replace trigger TIB_ERCP_T_PayResult
  before insert on ercp_payresult  
  for each row
declare
  -- local variables here
begin
update ERCP_PayResult a set(ReturnAddress,AccountNum,ProcessCode,Amount,CurCode,TransDatetime,AcqSsn,Ltime,Ldate,SettleDate,UpsNo,TsNo,Reference,RespCode,TerminalNo,MerchantNo,OrderNo,OrderState,Description,Re,ValidTime,OrderType,TransData,Pin,LoginPin,Mac,CreateTime,State)=select(b.ReturnAddress,b.AccountNum,b.ProcessCode,b.Amount,b.CurCode,b.TransDatetime,b.AcqSsn,b.Ltime,b.Ldate,b.SettleDate,b.UpsNo,b.TsNo,b.Reference,b.RespCode,b.TerminalNo,b.MerchantNo,b.OrderNo,b.OrderState,b.Description,b.Re,b.ValidTime,b.OrderType,b.TransData,b.Pin,b.LoginPin,b.Mac,b.CreateTime,b.State from inserted b where b.OrderNo=a.OrderNo)where exists (select 1 from ERCP_PayResult b where b.OrderNo=a.OrderNo );
end TIB_ERCP_T_PayResult;
我改为这样的啦。还是不行

Answer 3

update ERCP_PayResult a 这里好像不能用别名吧！

Answer 4

楼主，这句话后面要加分号；
select * from ERCP_PayResult;

Answer 5

那个update语句，语法明显有问题,set =号后面那个select 语句，要从select前面开始括住
 update a set (a.con1,a.con2)=(select b.con1,b.con2 from b where b.id=a.id);set =  (select b.ReturnAddress,                                                                                                                                                                                                                                                                                                   b.AccountNum,                                                                                                                                                                                                                                                                                        b.ProcessCode,                                                                                                                                                                                                                                                                                          b.Amount,                                                                                                                                                                                                                                                                                               b.CurCode,                                                                                                                                                                                                                                                                                               b.TransDatetime,                                                                                                                                                                                                                                                                                         b.AcqSsn,                                                                                                                                                                                                                                                                                         b.Ltime,                                                                                                                                                                                                                                                                                            b.Ldate,                                                                                                                                                                                                                                                                                             b.SettleDate,                                                                                                                                                                                                                                                                                           b.UpsNo,                                                                                                                                                                                                                                                                                            b.TsNo,                                                                                                                                                                                                                                                                                              b.Reference,
                                                                                                                                                                                                                                                                                                         b.RespCode,
                                                                                                                                                                                                                                                                                                         b.TerminalNo,
                                                                                                                                                                                                                                                                                                         b.MerchantNo,
                                                                                                                                                                                                                                                                                                         b.OrderNo,
                                                                                                                                                                                                                                                                                                         b.OrderState,
                                                                                                                                                                                                                                                                                                         b.Description,
                                                                                                                                                                                                                                                                                                         b.Re,
                                                                                                                                                                                                                                                                                                         b.ValidTime,
                                                                                                                                                                                                                                                                                                         b.OrderType,
                                                                                                                                                                                                                                                                                                         b.TransData,
                                                                                                                                                                                                                                                                                                         b.Pin,
                                                                                                                                                                                                                                                                                                         b.LoginPin,
                                                                                                                                                                                                                                                                                                         b.Mac,
                                                                                                                                                                                                                                                                                                         b.CreateTime,
                                                                                                                                                                                                                                                                                                         b.State from
                                                                                                                                                                                                                                                                                                         inserted b
                                                                                                                                                                                                                                                                                                         where
                                                                                                                                                                                                                                                                                                         b.OrderNo =
                                                                                                                                                                                                                                                                                                         a.OrderNo)
                                                                                                                                                                                                                                                                                                  

Answer 6

改成这样create or replace trigger TIB_ERCP_T_PayResult
  before insert on ercp_payresult  
  for each row
declare
  -- local variables here
begin
update ERCP_PayResult a set(ReturnAddress,AccountNum,ProcessCode,Amount,CurCode,TransDatetime,AcqSsn,Ltime,Ldate,SettleDate,UpsNo,TsNo,Reference,RespCode,TerminalNo,MerchantNo,OrderNo,OrderState,Description,Re,ValidTime,OrderType,TransData,Pin,LoginPin,Mac,CreateTime,State)=(select b.ReturnAddress,b.AccountNum,b.ProcessCode,b.Amount,b.CurCode,b.TransDatetime,b.AcqSsn,b.Ltime,b.Ldate,b.SettleDate,b.UpsNo,b.TsNo,b.Reference,b.RespCode,b.TerminalNo,b.MerchantNo,b.OrderNo,b.OrderState,b.Description,b.Re,b.ValidTime,b.OrderType,b.TransData,b.Pin,b.LoginPin,b.Mac,b.CreateTime,b.State from inserted b where b.OrderNo=a.OrderNo)where exists (select 1 from ERCP_PayResult b where b.OrderNo=a.OrderNo );
end TIB_ERCP_T_PayResult;

Answer 7

你先把update写对了再来写触发器触发器没什么问题，你的update有问题