表A每增加一条记录,就在表B中添加一条记录。例如 表LPU : LPU01 LPU02 LPU03
00001 H0001 1000 表LSN : LSN01 LSN02 LSN03 LSN04 LSN05
00001 H0001 1000 Y N表LSN 的LSN04,LSN05都是固定的值
00001 H0001 1000 表LSN : LSN01 LSN02 LSN03 LSN04 LSN05
00001 H0001 1000 Y N表LSN 的LSN04,LSN05都是固定的值
after insert on lpu
for insert
declare
-- local variables here
begin
insert LSN(LSN01,LSN02,LSN03,LSN04,LSN05)
select LPU01,LPU02,LPU03,'Y','N' from inserted
end lpu-lsn;
go
会报错:缺失关键字
after insert
on LPU
for each rowbegin
insert into LSN(LSN01,LSN02,LSN03,LSN04,LSN05)
VALUES (:NEW.LSN01,:NEW.LSN02,:NEW.LSN03,'Y','N')END;這樣試試
VALUES (:NEW.LSN01,:NEW.LSN02,:NEW.LSN03,'Y','N');--這里少了分號
給你轉到Oracle版看看
before insert on LSN for each row
begin
insert LSN(LSN01,LSN02,LSN03,LSN04,LSN05)
select LPU01,LPU02,LPU03,'Y','N' from inserted
end;
试试
after insert
on LPU
for each rowbegin
insert into LSN1(LSN01,LSN02,LSN03,LSN04,LSN05,LSN06,LSN07)
values(:new.lpu01,:new.lpu03,:new.lpu05,:new.lpu02,'Y','N','0');END;
00001 H0001 1000 表LSN : LSN01 LSN02 LSN03 LSN04 LSN05
00001 H0001 1000 Y N表LSN 的LSN04,LSN05都是固定的值
现在还得加一个条件,就是在表LPU多加一个字段LPU04,当LPU04='Y'时
才可以在LSN添加数据,怎么弄?
after insert on lpk_file
for each row
declare
@lpkacti Select @lpkacti = lpkacti from lpk_filebegin
if @lpkacti='Y' then
insert into LSN1(LSN01,LSN02,LSN03,LSN04,LSN05,LSN06,LSN07)
values(:new.lpu01,:new.lpu03,:new.lpu05,:new.lpu02,'Y','N','0');
end if;
end lpk_occ;为什么会报错呢:PLS-00103:出现符号@需要在下列之一时……
create table LSN
(
LSN01 VARCHAR2(20),
LSN02 VARCHAR2(20),
LSN03 VARCHAR2(20),
LSN04 VARCHAR2(20),
LSN05 VARCHAR2(20)
) ;
-- Create table
create table LPU
(
LPU01 VARCHAR2(20),
LPU02 VARCHAR2(20),
LPU03 VARCHAR2(20),
LPU04 VARCHAR2(20)
) ;
create or replace trigger tri_LSN
after insert on lsn
for each row
BEGIN IF :NEW.LSN04 = 'Y' THEN
INSERT INTO LPU
(LPU01, LPU02, LPU03, LPU04)
VALUES
(:NEW.LSN01, :NEW.LSN02, :NEW.LSN03, :NEW.LSN04);
END IF;
END TRI_LSN;
after insert on lpk_file
for each row
declare
@lpkacti --注意语法 oracle的语法是 lpkacti varchar2(10);没有@符号Select @lpkacti = lpkacti from lpk_file --oracle语法是 select 字段 into 变量 from 表名begin
if @lpkacti='Y' then --oracle可以直接用变量名,不用加@
insert into LSN1(LSN01,LSN02,LSN03,LSN04,LSN05,LSN06,LSN07)
values(:new.lpu01,:new.lpu03,:new.lpu05,:new.lpu02,'Y','N','0');
end if;
end lpk_occ;