create or replace trigger test before update or delete or insert on a for each row if inserting then begin if exists(select 1 from b where names=:new.names) begin insert into c select * from b where names=:new.names; end; end ; end if;
可以这样简化: create or replace trigger test before update or delete or insert on a for each row if inserting then begin insert into c select * from b where names=:new.names;end ; end if;
少了提交语句,再修改一点点: create or replace trigger test before update or delete or insert on a for each row if inserting then begin if exists(select 1 from b where names=:new.names) begin insert into c select * from b where names=:new.names; commit; end; end if; end if;
CREATE OR REPLACE TRIGGER TRI_TAB BEFORE INSERT ON TAB1 REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE V_COUNT NUMBER; BEGIN SELECT COUNT(*) INTO V_COUNT FROM TAB2 WHERE T1 = :NEW.T1 AND T2 = :NEW.T2; IF V_COUNT >= 1 THEN INSERT INTO TAB3 VALUES (:NEW.T1, :NEW.T2); END IF; END TRI_TAB;
create or replace trigger test before update or delete or insert on tablea for each row declare v_count int; begin if inserting then select count(*) into v_count from tableb where names=:new.names; --if exists(select 1 from tableb where names=:new.names) then if v_count>0 then insert into tablec select * from tableb where names=:new.names; end if; end if; end; 为什么oracle会提示if exists(select 1 from tableb where names=:new.names) then exists用于sql语句呢,用这个编译不过呢,
if exists(select * from tableb where names=:new.names) then
before update or delete or insert
on a
for each row
if inserting then
begin
if exists(select 1 from b where names=:new.names)
begin
insert into c
select * from b where names=:new.names;
end;
end ;
end if;
create or replace trigger test
before update or delete or insert
on a
for each row
if inserting then
begin
insert into c
select * from b where names=:new.names;end ;
end if;
create or replace trigger test
before update or delete or insert
on a
for each row
if inserting then
begin
if exists(select 1 from b where names=:new.names)
begin
insert into c
select * from b where names=:new.names;
commit;
end;
end if;
end if;
不知道可不可以加个pragma autonomous_transaction;
最好不要随便在表里面加trigger,有时在前台调试了半天也不知道问题在哪里?
谢谢你们的指教..
请问题 names=:new.names是什么意思啊..
我a表字段是:QUERYCODE,b表字段是:APPCODE
要求这两个字段值相等请问该怎么写啊..
BEFORE INSERT ON TAB1
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
V_COUNT NUMBER;
BEGIN
SELECT COUNT(*)
INTO V_COUNT
FROM TAB2
WHERE T1 = :NEW.T1
AND T2 = :NEW.T2;
IF V_COUNT >= 1 THEN
INSERT INTO TAB3 VALUES (:NEW.T1, :NEW.T2);
END IF;
END TRI_TAB;
before update or delete or insert
on tablea
for each row
declare
v_count int;
begin
if inserting then
select count(*) into v_count from tableb where names=:new.names;
--if exists(select 1 from tableb where names=:new.names) then
if v_count>0 then
insert into tablec
select * from tableb where names=:new.names;
end if;
end if;
end;
为什么oracle会提示if exists(select 1 from tableb where names=:new.names) then
exists用于sql语句呢,用这个编译不过呢,