有两个表,主表master和人员表renyuan
两个表都有字段name,要求在主表中添加或者更新纪录的时候,
将新的name字段的值添加到renyuan表的name字段中。
也就是说使renyuan的name字段和主表的name同步。
这个操作涉及到两个触发器,添加纪录和更新纪录的时候
首先要查询ruanyuan表中有没有这个值,有则添加,无则退出
两个表都有字段name,要求在主表中添加或者更新纪录的时候,
将新的name字段的值添加到renyuan表的name字段中。
也就是说使renyuan的name字段和主表的name同步。
这个操作涉及到两个触发器,添加纪录和更新纪录的时候
首先要查询ruanyuan表中有没有这个值,有则添加,无则退出
AFTER INSERT OR UPDATE ON MASTER
FOR EACH ROW
DECLARE
tmpVar varchar2;
BEGIN
tmpVar := :NEW.Name;
if exists(select 1 from renyuan where name=tmpvar) then
insert into renyuan(name) values(tempvar); EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
END tr_master_renyuan;
/
before insert or update on MASTER
for each row
begin
if inserting then
insert into (:new.id,:new.name,...);
else
update renyuan set name=:new.name where id=:new.id;
end if;
end;
/
beckhambobo(beckham)
的方法!
错误类型:Compilation errors for TRIGGER JIAJU.TRIG_NEWUPDATE_ZJJKError: PLS-00103: 出现符号 "END"
Line: 21
Text: end trig_newUpdate_zjjk;/////////////////////////
create or replace trigger trig_newUpdate_zjjk
after insert or update on zjjk
for each row
declare
var1 varchar2;
var2 varchar2;
var3 varchar2;
begin
var1:=:NEW.ssyx;
var2:=:NEW.ssksh;
var3:=:NEW.manager_name;
if exists(select * from renshi where ssyx=var1 and ssksh=var2 and people_name=var3 ) then
insert into renshi values(var1,var2,var3);
end if;
exception
when others then
raise;
end;
end trig_newUpdate_zjjk;
BEFORE INSERT OR UPDATE
ON MASTER
FOR EACH ROW
DECLARE
BEGIN
--
if inserting then
if exists(select * from renyuan where NAME=:NEW.NAME) then
insert into renyuan(NAME) values(:NEW.NAME);
end if;
end if;
if updating('NAME') then
update renyuan set NAME=:NEW.NAME where NAME=:OLD.NAME;
end if;
END;
/
SHOW ERROR TRIGGER TR_MASTER;
AFTER INSERT OR UPDATE ON MASTER
FOR EACH ROW
DECLARE
tmp number(8);
BEGIN
select count(name) into tmp from renyuan where NAME=:NEW.NAME;
if tmp>0 then
if inserting then
insert into renyuan(NAME) values(:NEW.NAME);
end if;
if updating('NAME') then
update renyuan set NAME=:NEW.NAME where NAME=:OLD.NAME;
end if;
end if;
end;
exception
when others then
raise;
end;
应该为:
exception
when others then
raise;