日期:2014-05-17  浏览次数:21190 次

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

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.Remark=t2.Remark,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,Remark,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.Remark,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 则更新相应的字段,可是根本就编译不通过,怎么办?

------解决方案--------------------
select * from ERCP_PayResult
触发器,不能只是一个查询语句
------解决方案--------------------
update ERCP_PayResult a 这里好像不能用别名吧!
------解决方案--------------------
楼主,这句话后面要加分号;
select * from ERCP_PayResult;

------解决方案--------------------
改成这样
SQL code

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,Remark,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.Remark,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;

------解决方案--------------------
你先把update写对了再来写触发器

触发器没什么问题,你的update有问题