if new.unitprice in ( select unitprice from matstore where mattype=new.mattype and matname=new.matname and spec=new.spec;)else ... ...可以这么写吗?判断是否有和new.unitprice相同的价格,是这么写吗?
帮我看看我写的一个触发器,该怎么改改才对呢?判断new.unitprice 是否在select unitprice from matstore where mattype=new.mattype and matname=new.matname and spec=new.spec 的纪录中,该怎么弄?上面写的肯定不对,大虾帮忙。下面是我写得触发器,帮忙看看CREATE OR REPLACE TRIGGER MATIN_TRIGGER1 BEFORE INSERT ON MATRES.MATIN FOR EACH ROW DECLARE --old_mattype varchar2(10), --old_matname varchar2(10), --old_spec varchar2(10), --old_unitprice varchar2(10), old_amount varchar2(10)BEGIN if new.unitprice in (select unitprice from matstore where mattype=new.mattype and matname=new.matname and spec=new.spec) select amount into old_amount from matstore where mattype=new.mattype and matname=new.matname and spec=new.spec and unitprice=new.unitprice; update matstore set amount=old_zmount+new.amount where mattype=new.mattype and matname=new.matname and spec=new.spec and unitprice=new.unitprice; else insert into matsore (serial,mattype,matname,spec,unit,amount,unitprice,matsum,newdate) values( Seq_matstore_serial.nextval,new.mattype,new.matname,new.spec,new.unit,new.amount,new.unitprice,new.matsum,sysdate)END;该怎么改?
if new.unitprice in ( select unitprice from matstore where mattype=new.mattype and matname=new.matname and spec=new.spec;)else ... ...这样不行,判断是否有和:new.unitprice相同的价格可以这样: l_count number; select count(1) into l_count from matstore where mattype=:new.mattype and matname=:new.matname and spec=:new.spec and unitprice=:new.unitprice; if l_count > 0 then .... else .... end if;注意不要忽略了new前面的冒号: !
在麻烦你帮忙看一下,为什么还有错呢??CREATE OR REPLACE TRIGGER MATIN_TRIGGER1 BEFORE INSERT ON MATRES.MATIN FOR EACH ROW DECLARE old_amount varchar2(10), l_count numberBEGIN select count(1) into l_count from matstore where mattype=:new.mattype and matname=:new.matname and spec=:new.spec and unitprice=:new.unitprice; if l_count>=1 then select amount into old_amount from matstore where mattype=:new.mattype and matname=:new.matname and spec=:new.spec and unitprice=:new.unitprice; update matstore set amount=old_zmount+new.amount where mattype=:new.mattype and matname=:new.matname and spec=:new.spec and unitprice=:new.unitprice; else insert into matsore (serial,mattype,matname,spec,unit,amount,unitprice,matsum,newdate) values( Seq_matstore_serial.nextval,new.mattype,new.matname,new.spec,new.unit,new.amount,new.unitprice,new.matsum,sysdate); end if END;报第二行,第26列有错,我不知道错在何处
DECLARE old_amount varchar2(10); l_count number;
if...then .... end if; 别忘了分号
CREATE OR REPLACE TRIGGER MATIN_TRIGGER1 BEFORE INSERT ON MATRES.MATIN FOR EACH ROW DECLARE old_amount varchar2(10) ; -- ; l_count number ; -- ;BEGIN select count(1) into l_count from matstore where mattype=:new.mattype and matname=:new.matname and spec=:new.spec and unitprice=:new.unitprice; if l_count>=1 then select amount into old_amount from matstore where mattype=:new.mattype and matname=:new.matname and spec=:new.spec and unitprice=:new.unitprice; update matstore set amount=old_zmount+new.amount where mattype=:new.mattype and matname=:new.matname and spec=:new.spec and unitprice=:new.unitprice; else --少了n多的: insert into matsore (serial,mattype,matname,spec,unit,amount,unitprice,matsum,newdate) values( Seq_matstore_serial.nextval,:new.mattype,:new.matname,:new.spec,:new.unit,:new.amount,:new.unitprice,:new.matsum,sysdate); end if ; --少; END;
保存时,说第九行的 = 好应该是 @= 或 。可是我这里面没有 = CREATE OR REPLACE TRIGGER MATERIALIN_TRIGGER2 BEFORE INSERT ON MATRES.MATERIALIN FOR EACH ROW DECLARE old_amount varchar2(10); l_count number;BEGIN select count(1) into l_count from matstore where mattype=:new.mattype and matname=:new.matname and spec=:new.spec and unitprice=:new.unitprice; if l_count>=1 then select amount into old_amount from matstore where mattype=:new.mattype and matname=:new.matname and spec=:new.spec and unitprice=:new.unitprice; update matstore set amount:=(old_zmount+new.amount) where mattype=:new.mattype and matname=:new.matname and spec=:new.spec and unitprice=:new.unitprice; else insert into matsore (serial,mattype,matname,spec,unit,amount,unitprice,matsum,newdate) values( Seq_matstore_serial.nextval,new.mattype,new.matname,new.spec,new.unit,new.amount,new.unitprice,new.matsum,sysdate); end if END;
再教你一招 select count(1) into l_count from matstore where mattype=:new.mattype and matname=:new.matname and spec=:new.spec and unitprice=:new.unitprice;改为 select count(1),max(amount) into l_count, old_amount from matstore where mattype=:new.mattype and matname=:new.matname and spec=:new.spec and unitprice=:new.unitprice;这样一条SQL就可以查出是否存在,若存在时的amount!也就不需要 select amount into old_amount from matstore where mattype=:new.mattype and matname=:new.matname and spec=:new.spec and unitprice=:new.unitprice; 这句了!
报告错误就是 begin 这一行,这怎么会出错呢?
应该不是begin的错,把你修改后的语句贴上来看看
CREATE OR REPLACE TRIGGER MATERIALIN_TRIGGER2 BEFORE INSERT ON MATRES.MATERIALIN FOR EACH ROW DECLARE old_amount varchar2(10); l_count number; BEGIN select count(1),max(amount) into l_count,old_amount from matstore where mattype=:new.mattype and matname=:new.matname and spec=:new.spec and unitprice=:new.unitprice; if l_count>=1 then update matstore set amount:=(old_zmount+new.amount) where mattype=:new.mattype and matname=:new.matname and spec=:new.spec and unitprice=:new.unitprice; else insert into matsore (serial,mattype,matname,spec,unit,amount,unitprice,matsum,newdate) values( Seq_matstore_serial.nextval,new.mattype,new.matname,new.spec,new.unit,new.amount,new.unitprice,new.matsum,sysdate); end if END;按照 Lastdrop(空杯)大哥的修改的。还是报begin这里出错
不对,还是有很多new没有加':' end if后也没有加';'
CREATE OR REPLACE TRIGGER MATERIALIN_TRIGGER2 BEFORE INSERT ON MATRES.MATERIALIN FOR EACH ROW DECLARE old_amount varchar2(10); l_count number; begin select count(1),max(amount) into l_count,old_amount from matstore where mattype=:new.mattype and matname=:new.matname and spec=:new.spec and unitprice=:new.unitprice; if l_count>=1 then update matstore set amount:=(old_amount+:new.amount) where mattype=:new.mattype and matname=:new.matname and spec=:new.spec and unitprice=:new.unitprice; else insert into matsore (serial,mattype,matname,spec,unit,amount,unitprice,matsum,newdate) values( Seq_matstore_serial.nextval,:new.mattype,:new.matname,:new.spec,:new.unit,:new.amount,:new.unitprice,:new.matsum,sysdate); end if; end;对不起。这是该过来的。依然报告begin错误
错误提示如下: The object was saved with errore:Line 7,Colun 28 PLS-00103:Encountered the symbol "=" when expecting one of the following: . @= The symbol "=" was inserted before "=" to continue
更改后,如下 CREATE OR REPLACE TRIGGER MATERIALIN_TRIGGER1 BEFORE INSERT ON MATRES.MATERIALIN FOR EACH ROW DECLARE old_amount varchar2(10); l_count number; begin select count(1),max(amount) into l_count,old_amount from matstore where mattype=:new.mattype and matname=:new.matname and spec=:new.spec and unitprice=:new.unitprice; if l_count>0 then update matstore set amount=old_amount+:new.amount where mattype=:new.mattype and matname=:new.matname and spec=:new.spec and unitprice=:new.unitprice; else insert into matstore (serial,mattype,matname,spec,unit,amount,unitprice,matsum,newdate) values( Seq_matstore_serial.nextval,:new.mattype,:new.matname,:new.spec,:new.unit,:new.amount,:new.unitprice,:new.matsum,sysdate); end if; end;错误变为if这一行,PLS-00904 insufficent privilege to access MATSTOREwhy??why??
begin
insert into table (...) values (:new.value...);
end;
这样应该可以吧
比如: new.mattype;
new.matname
就是刚插入的 mattype 、matname的值吗?
BEFORE
INSERT
ON MATRES.MATIN
FOR EACH ROW DECLARE
--old_mattype varchar2(10),
--old_matname varchar2(10),
--old_spec varchar2(10),
--old_unitprice varchar2(10),
old_amount varchar2(10)BEGIN
if new.unitprice in (select unitprice from matstore where mattype=new.mattype and matname=new.matname and spec=new.spec)
select amount into old_amount from matstore where mattype=new.mattype and matname=new.matname and spec=new.spec and unitprice=new.unitprice;
update matstore set amount=old_zmount+new.amount where mattype=new.mattype and matname=new.matname and spec=new.spec and unitprice=new.unitprice;
else insert into matsore (serial,mattype,matname,spec,unit,amount,unitprice,matsum,newdate) values( Seq_matstore_serial.nextval,new.mattype,new.matname,new.spec,new.unit,new.amount,new.unitprice,new.matsum,sysdate)END;该怎么改?
....
else
....
end if;注意不要忽略了new前面的冒号: !
BEFORE INSERT ON MATRES.MATIN
FOR EACH ROW DECLARE
old_amount varchar2(10),
l_count numberBEGIN
select count(1) into l_count from matstore where mattype=:new.mattype and matname=:new.matname and spec=:new.spec and unitprice=:new.unitprice;
if l_count>=1 then
select amount into old_amount from matstore where mattype=:new.mattype and matname=:new.matname and spec=:new.spec and unitprice=:new.unitprice;
update matstore set amount=old_zmount+new.amount where mattype=:new.mattype and matname=:new.matname and spec=:new.spec and unitprice=:new.unitprice;
else insert into matsore (serial,mattype,matname,spec,unit,amount,unitprice,matsum,newdate) values( Seq_matstore_serial.nextval,new.mattype,new.matname,new.spec,new.unit,new.amount,new.unitprice,new.matsum,sysdate);
end if
END;报第二行,第26列有错,我不知道错在何处
l_count number;
....
end if;
别忘了分号
BEFORE INSERT ON MATRES.MATIN
FOR EACH ROW DECLARE
old_amount varchar2(10) ; -- ;
l_count number ; -- ;BEGIN
select count(1) into l_count from matstore where mattype=:new.mattype and matname=:new.matname and spec=:new.spec and unitprice=:new.unitprice;
if l_count>=1 then
select amount into old_amount from matstore where mattype=:new.mattype and matname=:new.matname and spec=:new.spec and unitprice=:new.unitprice;
update matstore set amount=old_zmount+new.amount where mattype=:new.mattype and matname=:new.matname and spec=:new.spec and unitprice=:new.unitprice;
else
--少了n多的:
insert into matsore (serial,mattype,matname,spec,unit,amount,unitprice,matsum,newdate) values( Seq_matstore_serial.nextval,:new.mattype,:new.matname,:new.spec,:new.unit,:new.amount,:new.unitprice,:new.matsum,sysdate);
end if ; --少;
END;
CREATE OR REPLACE TRIGGER MATERIALIN_TRIGGER2
BEFORE INSERT ON MATRES.MATERIALIN
FOR EACH ROW DECLARE
old_amount varchar2(10);
l_count number;BEGIN
select count(1) into l_count from matstore where mattype=:new.mattype and matname=:new.matname and spec=:new.spec and unitprice=:new.unitprice;
if l_count>=1 then
select amount into old_amount from matstore where mattype=:new.mattype and matname=:new.matname and spec=:new.spec and unitprice=:new.unitprice;
update matstore set amount:=(old_zmount+new.amount) where mattype=:new.mattype and matname=:new.matname and spec=:new.spec and unitprice=:new.unitprice;
else
insert into matsore (serial,mattype,matname,spec,unit,amount,unitprice,matsum,newdate) values( Seq_matstore_serial.nextval,new.mattype,new.matname,new.spec,new.unit,new.amount,new.unitprice,new.matsum,sysdate);
end if
END;
select count(1) into l_count from matstore where mattype=:new.mattype and matname=:new.matname and spec=:new.spec and unitprice=:new.unitprice;改为
select count(1),max(amount) into l_count, old_amount from matstore where mattype=:new.mattype and matname=:new.matname and spec=:new.spec and unitprice=:new.unitprice;这样一条SQL就可以查出是否存在,若存在时的amount!也就不需要
select amount into old_amount from matstore where mattype=:new.mattype and matname=:new.matname and spec=:new.spec and unitprice=:new.unitprice;
这句了!
BEFORE INSERT ON MATRES.MATERIALIN
FOR EACH ROW
DECLARE
old_amount varchar2(10);
l_count number;
BEGIN
select count(1),max(amount) into l_count,old_amount from matstore where mattype=:new.mattype and matname=:new.matname and spec=:new.spec and unitprice=:new.unitprice;
if l_count>=1 then
update matstore set amount:=(old_zmount+new.amount) where mattype=:new.mattype and matname=:new.matname and spec=:new.spec and unitprice=:new.unitprice;
else
insert into matsore (serial,mattype,matname,spec,unit,amount,unitprice,matsum,newdate) values( Seq_matstore_serial.nextval,new.mattype,new.matname,new.spec,new.unit,new.amount,new.unitprice,new.matsum,sysdate);
end if
END;按照 Lastdrop(空杯)大哥的修改的。还是报begin这里出错
end if后也没有加';'
BEFORE INSERT ON MATRES.MATERIALIN
FOR EACH ROW
DECLARE
old_amount varchar2(10);
l_count number;
begin
select count(1),max(amount) into l_count,old_amount from matstore where mattype=:new.mattype and matname=:new.matname and spec=:new.spec and unitprice=:new.unitprice;
if l_count>=1 then
update matstore set amount:=(old_amount+:new.amount) where mattype=:new.mattype and matname=:new.matname and spec=:new.spec and unitprice=:new.unitprice;
else
insert into matsore (serial,mattype,matname,spec,unit,amount,unitprice,matsum,newdate) values( Seq_matstore_serial.nextval,:new.mattype,:new.matname,:new.spec,:new.unit,:new.amount,:new.unitprice,:new.matsum,sysdate);
end if;
end;对不起。这是该过来的。依然报告begin错误
The object was saved with errore:Line 7,Colun 28
PLS-00103:Encountered the symbol "=" when expecting one of the following:
. @=
The symbol "=" was inserted before "=" to continue
CREATE OR REPLACE TRIGGER MATERIALIN_TRIGGER1
BEFORE INSERT ON MATRES.MATERIALIN
FOR EACH ROW
DECLARE
old_amount varchar2(10);
l_count number;
begin
select count(1),max(amount) into l_count,old_amount from matstore where mattype=:new.mattype and matname=:new.matname and spec=:new.spec and unitprice=:new.unitprice;
if l_count>0 then
update matstore set amount=old_amount+:new.amount where mattype=:new.mattype and matname=:new.matname and spec=:new.spec and unitprice=:new.unitprice;
else
insert into matstore (serial,mattype,matname,spec,unit,amount,unitprice,matsum,newdate) values( Seq_matstore_serial.nextval,:new.mattype,:new.matname,:new.spec,:new.unit,:new.amount,:new.unitprice,:new.matsum,sysdate);
end if;
end;错误变为if这一行,PLS-00904 insufficent privilege to access MATSTOREwhy??why??