create trigger name_tri
before update on 表一
for each row
begin
update 表二 set mystatus=状态变量2 where myname=:new.name;
end;
/
before update on 表一
for each row
begin
update 表二 set mystatus=状态变量2 where myname=:new.name;
end;
/
After Update Of status On 表一
For Each Row
Begin
select name into v_name from 表一 where
update
If Updating And :old.name=状态变量1
Then
update 表二 set mystatus=状态变量2 where myname=old.name
End If;
End;注意表一被update时其name不能被更改,否则可能错误。
before update on table1
for each row
declare
v_StatusVar TABLE2.MYSTATUS%TYPE := '1';
begin
UPDATE TABLE2
SET MYSTATUS = v_StatusVar
WHERE MYNAME = :new.name;
end BEFOREUPDATETABLE1;
before update on 表一
for each row
begin
update 表二 set mystatus=:new.status+1 where myname=:new.name;
end;
/
before update on table1
for each row
declare
v_StatusVar TABLE2.MYSTATUS%TYPE;
begin
IF :new.STATUS = '1' THEN
v_StatusVar := '2';
ELSIF :new.STATUS = '2' THEN
v_StatusVar := '3';
ELSE
v_StatusVar := '0';
END IF;
UPDATE TABLE2
SET MYSTATUS = v_StatusVar
WHERE MYNAME = :new.NAME;
end BEFOREUPDATETABLE1;
create or replace trigger TriUser
after update on Register
for each row
declare
v_mStatus men.status%TYPE;
v_mPetsort men.petsort%TYPE;
v_wStatus women.status%TYPE;
v_wManid women.manid%TYPE;
begin
IF :new.STATUS = '1' THEN
v_mStatus := '1';
v_mPetsort := 0;
v_wStatus := '1';
v_wManid := 0;
ELSIF :new.STATUS = '0' THEN
v_mStatus := '0';
v_mPetsort := 0;
v_wStatus := '0';
v_wManid := 0;
END IF;
UPDATE men SET status = v_mStatus,petsort=v_mPetsort WHERE phone = :new.phone;
UPDATE women SET status = v_wStatus,manid=v_wManid WHERE phone = :new.phone;
end TriUser;在sqlplus里运行时结果如下:
SQL> create or replace trigger TriUser
after update on Register
for each row
declare
v_mStatus men.status%TYPE;
v_mPetsort men.petsort%TYPE;
v_wStatus women.status%TYPE;
v_wManid women.manid%TYPE;
begin
IF :new.STATUS = '1' THEN
v_mStatus := '1';
v_mPetsort := 0;
v_wStatus := '1';
v_wManid := 0;
ELSIF :new.STATUS = '0' THEN
v_mStatus := '0';
v_mPetsort := 0;
v_wStatus := '0';
v_wManid := 0;
END IF;
UPDATE men SET status = v_mStatus,petsort=v_mPetsort WHERE phone = :new.phone;
UPDATE women SET status = v_wStatus,manid=v_wManid WHERE phone = :new.phone;
end TriUser; 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
25
26
27
28
一直按下去就是数字不断增加,这是怎么回事?
Warning: Trigger created with compilation errors.
我不明白为什么上面都用到了:new.name,但是new哪里来的!?
show error
后回车,找错误