我现在要实现在oracle中,有一张表(表1)中有数据(id,name),另张表(表2)和表1数据结构一样但多一个字段type(0:无变化 1:增加 2:删除 3:修改),从表一将数据导入到表2中,我现在要根据表1的数据和表2中的数据进行比较,如果存在一样的就不导入并将type改为0,name不一样并且id一样说明是修改的将type改为3,name和id都不存在说明是增加的,改为1,原先导入一个值再次导入如果数据不存在说明删除了改为2。 帮帮忙啊!要用到触发器的! 随便问问oracle中的:new.字段名到底能不取到最新插入的值。该怎么使用啊?有源码更好啊!
create or replace trigger trg_sample
on tableName after insert
for each rowbegin
--:new 可以取到最新的值
insert into table1(id,name) values(:new.id,:new.name);
end;
before INSERT ON test1 for each row
DECLARE
sCount varchar2(50);
delID varchar2(50);
ADDID varchar2(50);
updaID varchar2(50);
NameAll varchar2(50);
BEGIN
select count(*) into sCount from TEST1; IF sCount=0
THEN commit;
END IF;
select id into delID from (select t1.id from TEST1 t1 minus select t.id from TEST t);--删除
if delID is not null
then update TEST1 set content=3 where id in delID;
rollback;
END IF; select id into ADDID from ( select t.id from TEST t minus select t1.id from TEST1 t1);--增加
if ADDID is not null
then update TEST1 set content=1 where id in ADDID;
rollback;
END IF; select id into updaID from ( select t.id from TEST t intersect select t1.id from TEST1 t1);--修改和无变化的
if updaID is not null
then select NAME into NameAll from TEST1 where id in updaID;
update TEST1 set CONTENT=0 where NameAll=:NEW.NAME;
update TEST1 set CONTENT=2,NAME=:NEW.NAME where NameAll<>:NEW.NAME;END IF;
END;
我写的,看下有什么错啊?怎么老是报触发器/函数不能读它
using 表1
on(表2.id=表2.id)
when matched then
update
set 表2.xxx=表1.xxx
when not matched then
insert
values(表1.xx,表1.xxx)
http://topic.csdn.net/u/20100608/14/1b3cccd2-5ae2-4266-b52f-d841df7e7125.html