往A表里面插入数据,先判断这条数据的IS_TEST字段,如果是0就插入B表中,然后在A表中将这条数据删除。如果是1就不插入B表,也就是不做任何操作。这是写的插入数据的触发器,怎么在里面添加语句实现删除操作。
create or replace trigger TRG_DXP_MATERIEL_APPLY_INSERT
after insert on DXP_MATERIEL_APPLY
for each row
DECLARE
BEGIN
if :NEW.IS_TEST=0 then
INSERT INTO ods_epbi.DXP_MATERIEL_APPLY(
TAB_YEAR,
TAB_MONTH,
TAB_DAY,
APPLY_ID,
APPLY_TYPE,
UNIT_CODE,
UNIT_NAME,
IS_TEST,
DXP_MATERIEL_APPLY_ID,
DEPT_CODE) VALUES(
:NEW.TAB_YEAR,
:NEW.TAB_MONTH,
:NEW.TAB_DAY,
:NEW.APPLY_ID,
:NEW.APPLY_TYPE,
:NEW.UNIT_CODE,
:NEW.UNIT_NAME,
:NEW.IS_TEST,
:NEW.DXP_MATERIEL_APPLY_ID,
:NEW.DEPT_CODE);
end if;
end;谢谢各位了!!!!!!
create or replace trigger TRG_DXP_MATERIEL_APPLY_INSERT
after insert on DXP_MATERIEL_APPLY
for each row
DECLARE
BEGIN
if :NEW.IS_TEST=0 then
INSERT INTO ods_epbi.DXP_MATERIEL_APPLY(
TAB_YEAR,
TAB_MONTH,
TAB_DAY,
APPLY_ID,
APPLY_TYPE,
UNIT_CODE,
UNIT_NAME,
IS_TEST,
DXP_MATERIEL_APPLY_ID,
DEPT_CODE) VALUES(
:NEW.TAB_YEAR,
:NEW.TAB_MONTH,
:NEW.TAB_DAY,
:NEW.APPLY_ID,
:NEW.APPLY_TYPE,
:NEW.UNIT_CODE,
:NEW.UNIT_NAME,
:NEW.IS_TEST,
:NEW.DXP_MATERIEL_APPLY_ID,
:NEW.DEPT_CODE);
end if;
end;谢谢各位了!!!!!!
after insert on DXP_MATERIEL_APPLY
for each row
DECLARE
BEGIN
if (:NEW.IS_TEST=0) then
INSERT INTO ods_epbi.DXP_MATERIEL_APPLY(
TAB_YEAR,
TAB_MONTH,
TAB_DAY,
APPLY_ID,
APPLY_TYPE,
UNIT_CODE,
UNIT_NAME,
IS_TEST,
DXP_MATERIEL_APPLY_ID,
DEPT_CODE) VALUES(
:NEW.TAB_YEAR,
:NEW.TAB_MONTH,
:NEW.TAB_DAY,
:NEW.APPLY_ID,
:NEW.APPLY_TYPE,
:NEW.UNIT_CODE,
:NEW.UNIT_NAME,
:NEW.IS_TEST,
:NEW.DXP_MATERIEL_APPLY_ID,
:NEW.DEPT_CODE);
--从a表中删除数据
delete from DXP_MATERIEL_APPLY where IS_TEST = :NEW.IS_TEST;--删除数据
end if;
end;
如果是,则可以这么做:create or replace trigger TRG_DXP_MATERIEL_APPLY_INSERT
after insert on DXP_MATERIEL_APPLY
for each row
DECLARE
BEGIN
if :NEW.IS_TEST=0 then
INSERT INTO ods_epbi.DXP_MATERIEL_APPLY(
TAB_YEAR,
TAB_MONTH,
TAB_DAY,
APPLY_ID,
APPLY_TYPE,
UNIT_CODE,
UNIT_NAME,
IS_TEST,
DXP_MATERIEL_APPLY_ID,
DEPT_CODE) VALUES(
:NEW.TAB_YEAR,
:NEW.TAB_MONTH,
:NEW.TAB_DAY,
:NEW.APPLY_ID,
:NEW.APPLY_TYPE,
:NEW.UNIT_CODE,
:NEW.UNIT_NAME,
:NEW.IS_TEST,
:NEW.DXP_MATERIEL_APPLY_ID,
:NEW.DEPT_CODE);
RAISE_APPLICATION_ERROR(-20600,'IS_TEST=0时不能插入');
end if;end;
create or replace trigger TRG_DXP_MATERIEL_APPLY_INSERT
before insert on DXP_MATERIEL_APPLY
for each row
DECLARE
BEGIN
if :NEW.IS_TEST=0 then
INSERT INTO ods_epbi.DXP_MATERIEL_APPLY(
TAB_YEAR,
TAB_MONTH,
TAB_DAY,
APPLY_ID,
APPLY_TYPE,
UNIT_CODE,
UNIT_NAME,
IS_TEST,
DXP_MATERIEL_APPLY_ID,
DEPT_CODE) VALUES(
:NEW.TAB_YEAR,
:NEW.TAB_MONTH,
:NEW.TAB_DAY,
:NEW.APPLY_ID,
:NEW.APPLY_TYPE,
:NEW.UNIT_CODE,
:NEW.UNIT_NAME,
:NEW.IS_TEST,
:NEW.DXP_MATERIEL_APPLY_ID,
:NEW.DEPT_CODE);
RAISE_APPLICATION_ERROR(-20600,'IS_TEST=0时不能插入');
end if;end;
2。在表B上也建立一个触发器,一旦表B收到一条IS_TEST=0的数据,就去A表把这条数据删掉;
推荐方法1,方法2不太好
create or replace procdure test
pragma autonomous_transaction;
begin
insert... commit;
end;
这样的话,IS_TEST=0的数据插不到A表(报错),但是数据可以插到B表