新手,请大家多多指教
我想建立一个触发器,具体描述如下:
A(a1,a2,a3);
B(b1,b2,b3);
触发条件是在A表中插入时,如果a3不为空,此时如果B.b1=A.a1的结果为空,则
对B插入(a1,'0',a3);
我是这样写的
create trigger ck_lev
after insert on A
for each row
when (NEW.a3 is NOT NULL)
declare
num number;
begin
select count(*)
into num
from B
where b3 = :NEW.a3;
if (num=0) then
insert into B values (:NEW.a1,'0',:NEW.a3);
end if;
end;
他总是报错,说我into num那一行missing equal sign;
另外我再问一个弱的问题,create trigger ..when..和create trigger begin if() end有区别吗?
谢谢大家,急,在线等
我想建立一个触发器,具体描述如下:
A(a1,a2,a3);
B(b1,b2,b3);
触发条件是在A表中插入时,如果a3不为空,此时如果B.b1=A.a1的结果为空,则
对B插入(a1,'0',a3);
我是这样写的
create trigger ck_lev
after insert on A
for each row
when (NEW.a3 is NOT NULL)
declare
num number;
begin
select count(*)
into num
from B
where b3 = :NEW.a3;
if (num=0) then
insert into B values (:NEW.a1,'0',:NEW.a3);
end if;
end;
他总是报错,说我into num那一行missing equal sign;
另外我再问一个弱的问题,create trigger ..when..和create trigger begin if() end有区别吗?
谢谢大家,急,在线等
into num
这个地方
你可能写错了,漏了空格之类的,仔细检查一下。
你这个触发器从语法上看是没问题的
create trigger ck_lev
after insert on tickets
for each row
when (NEW.CARDNO is NOT NULL)
declare
num number;
begin
select count(*) into num from notify n
where n.cardno = :NEW.cardno;
insert into credits values(:NEW.ticketid,:NEW.cardno,(select f.miles from flights f where f.flightid=:NEW.flightid));
if (num>0) then
update notify set max(totalmiles)=totalmiles+(select f.miles
from flights f where f.flightid=:NEW.flightid) where cardno =:NEW.cardno;
end if;
end;
这里面涉及了3个表,提示是ERROR at line 8: PL/SQL: ORA-00927: missing equal sign
表信息如下
CARDS (CARDNO, NAME, STATUS)
FLIGHTS (FLIGHTID, DEPARTURETIME, DEPARTURECITY, ARRIVALCITY, MILES)
TICKETS (TICKETID, FLIGHTID, FLIGHTDATE, NAME, CARDNO*)
CREDITS (TICKETID, CARDNO, MILES)
NOTIFY (CARDNO, NOTIFYNO, NOTIFYDATE, OLDSTATUS, NEWSTATUS, TOTALMILES)
update notify set max(totalmiles)=totalmiles+(select f.miles
from flights f where f.flightid=:NEW.flightid) where cardno =:NEW.cardno;
a1 a2 a3
( 1 0 3 )
( 1 1 8)
( 1 2 10)
a2是主键,
我想把a1=1里,最大的a3行,给这一行的a3再加一个5,怎么实现呢?
谢谢