17,086
社区成员
发帖
与我相关
我的任务
分享建表时触发器的问题
我用Stream.execuQuery(sql);建一个触发器，每次通过这里总是报出索引中丢失IN或者OUT参数：1请教各位大神如何去解决这个问题。我把执行的sql语句打印出来的源码在数据库又能够执行。网上有找过资料，有的人说是主体中含有new的关键字导致的，说是他们的一个BUG。
...全文
请发表友善的回复…
发表回复
xxingchen_7 2013-01-10
- 打赏
- 举报
可是为什么，那到数据去执行它却能创建啊。就算表不存在，你只是建立触发器并没有触发那个事件。我想这样的错误按道理不应该存在啊。
xxingchen_7 2013-01-09
- 打赏
- 举报
还有，会不会是因为那个触发事件里的表不存在导致的呢？例如触发器是在这个Tbb_SaleArticle
表下创建的，结果执行的时候发现这个Tbb_ImpArticle表不存在那么这种情况会不会报那种错误呢
xxingchen_7 2013-01-09
- 打赏
- 举报
难道会是驱动的问题，我把它放到数据库中执行起来又没问题
linwaterbin 2013-01-09
- 打赏
- 举报
表不存在就没有事件来挂载触发这个触发器，不会执行
linwaterbin 2013-01-09
- 打赏
- 举报
你采用的jdbc瘦客户端？如果是，换个最新驱动的试试
shadow1986a 2013-01-09
- 打赏
- 举报
嗯，有可能， 你可以换个思路啊。建立个执行动态SQL的过程，然后调用该过程把这个SQL语句传进去执行。
xxingchen_7 2013-01-09
- 打赏
- 举报
我用的是oracle10g下面是我打印出来的源码：create or replace trigger TG_TBB_ARTICLE_U_BEFORE
BEFORE update
on Tbb_Article
for each row
declare
begin
update Tbb_SaleArticle
set UseStatus = :NEW.UseStatus
where :NEW.UseStatus <> :OLD.UseStatus
and Tbb_SaleArticle.ArtiId = :NEW.ArtiId
and InclQty<>1;
update Tbb_ImpArticle
set UseStatus = :NEW.UseStatus
where :NEW.UseStatus <> :OLD.UseStatus
and Tbb_ImpArticle.ArtiId = :NEW.ArtiId;
case
when updating('RETAILSALEPRICE') or updating('RetailPrice') then
update Tbb_SaleArticle
set SALEARTISPELL = :NEW.ARTISPELL||'/'||RETAILSALEPRICE
where ArtiId = :NEW.ArtiId;
:NEW.ARTISPELL := :NEW.ARTISPELL||'/'||:NEW.RetailPrice;
else
null;
end case;
update Tbb_SaleArticle
set SALEARTINAME = :NEW.ARTINAME,
SALEARTIABBR = :NEW.ARTIABBR,
SALEARTIFACTORYCODE = :NEW.ARTIFACTORYCODE,
SALEMANUFACTORY = :NEW.MANUFACTORY,
SALEPROCESSPRICE = case when InclQty = 1 then :NEW.PROCESSPRICE else case when InclQty > 0 then round(:NEW.PROCESSPRICE *InclQty,6) else -round(:NEW.PROCESSPRICE / (case when nvl(InclQty,0) = 0 then 1 else InclQty end),4) end end ,
SALESTAXRATE = :NEW.STAXRATE--,
-- SALEARTILIMIT = case when InclQty = 1 then :NEW.ARTILIMIT else case when InclQty > 0 then round(:NEW.ARTILIMIT *InclQty,4) else -round(:NEW.ARTILIMIT / InclQty,4) end end
where ArtiId = :NEW.ArtiId;
case
when updating('RetailPrice') then
update Tbb_SaleArticle
set RetailSalePrice = :NEW.RetailPrice,
ModifyDate = getdate()
where Tbb_SaleArticle.ArtiId = :NEW.ArtiId
and InclQty = 1;
if nvl(:NEW.AgioPrice,0) = 0 then
update Tbb_ArticleAdd
set RetailPriceAdd = :NEW.RetailPrice
where Tbb_ArticleAdd.ArtiId =:NEW.ArtiId;
end if;
update Tbb_SaleArticle
set RetailSalePrice = case when InclQty > 0 then round(:NEW.RetailPrice *InclQty,6) else -round(:NEW.RetailPrice / (case when InclQty = 0 then 1 else InclQty end),6) end,
ModifyDate = getdate()
where Tbb_SaleArticle.ArtiId = :NEW.ArtiId
and InclQty <> 1;
else
null;
end case;
case
when updating('AgioPrice') then
update Tbb_SaleArticle
set AgioSalePrice = :NEW.AgioPrice,
ModifyDate = getdate()
where Tbb_SaleArticle.ArtiId = :NEW.ArtiId
and InclQty = 1;
if :NEW.AgioPrice > 0 then
update Tbb_ArticleAdd
set RetailPriceAdd = :NEW.AgioPrice
where Tbb_ArticleAdd.ArtiId =:NEW.ArtiId;
end if;
update Tbb_SaleArticle
set AgioSalePrice = case when InclQty > 0 then round(:NEW.AgioPrice *InclQty,6) else -round(:NEW.AgioPrice / (case when InclQty = 0 then 1 else InclQty end),6) end,
ModifyDate = getdate()
where Tbb_SaleArticle.ArtiId = :NEW.ArtiId
and InclQty <> 1;
else
null;
end case;
case
when updating('AwardAmt') then
update Tbb_SaleArticle
set AwardAmt = :NEW.AwardAmt,
ModifyDate = getdate()
where Tbb_SaleArticle.ArtiId = :NEW.ArtiId
and InclQty = 1;
update Tbb_SaleArticle
set AwardAmt = case when InclQty > 0 then round(:NEW.AwardAmt *InclQty,6) else -round(:NEW.AwardAmt / (case when InclQty = 0 then 1 else InclQty end),6) end,
ModifyDate = getdate()
where Tbb_SaleArticle.ArtiId = :NEW.ArtiId
and InclQty <> 1;
else
null;
end case;
case
when updating('CostPrice') then
update Tbb_SaleArticle
set SaleCostPrice = :NEW.CostPrice,
ModifyDate = getdate()
where Tbb_SaleArticle.ArtiId = :NEW.ArtiId
and InclQty = 1;
update Tbb_SaleArticle
set SaleCostPrice = case when InclQty > 0 then round(:NEW.CostPrice *InclQty,6) else -round(:NEW.CostPrice / (case when InclQty = 0 then 1 else InclQty end),6) end,
ModifyDate = getdate()
where Tbb_SaleArticle.ArtiId = :NEW.ArtiId
and InclQty <> 1;
else
null;
end case;
case
when updating('UseStatus') then
if :NEW.UseStatus<>100 then
update Tbb_SaleArticle
set UseStatus = :NEW.UseStatus
where ArtiId = :NEW.ArtiId
and InclQty<>1;
end if;
update Tbb_ImpArticle
set UseStatus = :NEW.UseStatus
where ArtiId = :NEW.ArtiId;
update Tbb_UsedArtiInfo
set UseStatus = :NEW.UseStatus
where ArtiId = :NEW.ArtiId;
if :NEW.UseStatus = 110 then
update Tbd_StkCell
set ArtiId = 0, UseStatus = 0
where ArtiId = :NEW.ArtiId;
elsif :NEW.UseStatus =0 and :OLD.UseStatus=200 then
update Tbb_UsedArtiInfo
set CheckDate = getdate()
where ArtiId = :NEW.ArtiId;
elsif :NEW.UseStatus =110 and :OLD.UseStatus=0 then
update Tbb_UsedArtiInfo
set DisDate = getdate()
where ArtiId = :NEW.ArtiId;
end if;
else
null;
end case;
end;
