我现在又两个表A,B,其中两个表中都有pno。我想向A表中插入一条数据,要求values中的pno的值必须是在B表中已有的。例如select distinct pno from B;得到的结果是1,2,3.如果values中pno not in (1,2,3),就不让输入。trigger我是这样写的。
create or replace trigger insert_books
before insert on books
for each row
begin
if :new.pno not in (select pno from publishers) then
dbms_out.put_line( '你的pno不存在,请查证后在输入!');
end if;
end;
/
报错:[Error] PLS-00405 (2: 15): PLS-00405: subquery not allowed in this context。我想知道我这个错在哪里?正确的又要怎么写呢?
create or replace trigger insert_books
before insert on books
for each row
begin
if :new.pno not in (select pno from publishers) then
dbms_out.put_line( '你的pno不存在,请查证后在输入!');
end if;
end;
/
报错:[Error] PLS-00405 (2: 15): PLS-00405: subquery not allowed in this context。我想知道我这个错在哪里?正确的又要怎么写呢?
(
pno VARCHAR2(20)
)CREATE TABLE publishers
(
pno VARCHAR2(20)
)
触发实测编译成功:CREATE OR REPLACE TRIGGER insert_books
BEFORE INSERT ON books
FOR EACH ROW
DECLARE
vPNO publishers.pno%TYPE;
BEGIN
SELECT count(1) INTO vPNO FROM publishers WHERE pno = :New.pno;
IF vPNO = 0 then
DBMS_OUTPUT.PUT_LINE('你的pno不存在,请查证后在输入');
END IF;
END insert_books;