CREATE OR REPLACE TRIGGER "MATERIAL"."TRG_PLANDTL" AFTER
UPDATE
OR DELETE OF "PD_FLAG" ON "MATERIAL"."MM_PLANDTL" FOR EACH ROW
Declare strMainFlag char(1);
strID varchar2(12);
stmt varchar2(200);
BEGIN
strID := :new.pm_nativeid;
select min( MM_PlanDtl.PD_Flag) into strMainFlag from MM_PlanDtl where MM_PlanDtl.PM_NativeID=strID;if strMainFlag is null then
strMainFlag :='0';
end if;stmt := 'Update MM_PlanMain set PM_Flag = ' || strMainFlag || ' where MM_Planmain.Pm_NativeID=' || strID;
EXECUTE IMMEDIATE stmt;
end;这个触发器已经编译成功,简单描述
mm_planmain 主表 有字段pm_nativeid(主键),pm_flag
mm_plandtl 从表 有字段pm_nativeid(外键),pd_flag;
我想让从表的pd_flag更改时,更新主表mm_planmain的pm_flag;
请大家帮忙,谢谢!
UPDATE
OR DELETE OF "PD_FLAG" ON "MATERIAL"."MM_PLANDTL" FOR EACH ROW
Declare strMainFlag char(1);
strID varchar2(12);
stmt varchar2(200);
BEGIN
strID := :new.pm_nativeid;
select min( MM_PlanDtl.PD_Flag) into strMainFlag from MM_PlanDtl where MM_PlanDtl.PM_NativeID=strID;if strMainFlag is null then
strMainFlag :='0';
end if;stmt := 'Update MM_PlanMain set PM_Flag = ' || strMainFlag || ' where MM_Planmain.Pm_NativeID=' || strID;
EXECUTE IMMEDIATE stmt;
end;这个触发器已经编译成功,简单描述
mm_planmain 主表 有字段pm_nativeid(主键),pm_flag
mm_plandtl 从表 有字段pm_nativeid(外键),pd_flag;
我想让从表的pd_flag更改时,更新主表mm_planmain的pm_flag;
请大家帮忙,谢谢!
这句是想实现什么?你的需求好象只要update即可
这句话是更新或删除时触发触发器存在问题是在更新mm_plandtl表的pd_flag字段的值时,提示触发器函数不能读,我的数据库是oracle9i
UPDATE OF "PD_FLAG" ON "MATERIAL"."MM_PLANDTL"
FOR EACH ROW
Declare strMainFlag char(1);
strID varchar2(12);
stmt varchar2(200);
BEGIN
strID := :new.pm_nativeid;
strMainFlag := :new.pd_flag;if strMainFlag is null then
strMainFlag :='0';
end if;stmt := 'Update MM_PlanMain set PM_Flag = ' || strMainFlag || ' where MM_Planmain.Pm_NativeID=' || strID;
EXECUTE IMMEDIATE stmt;
end;
这样试试看。
You Asked (Jump to Tom's latest followup)
------------------------------------------------------------------------
Tom:can you give me some example at which situation
IN is better than exist, and vice versa.
and we said...Well, the two are processed very very differently.Select * from T1 where x in ( select y from T2 )is typically processed as:select *
from t1, ( select distinct y from t2 ) t2
where t1.x = t2.y;The subquery is evaluated, distinct'ed, indexed (or hashed or sorted) and then
joined to the original table -- typically.
As opposed to select * from t1 where exists ( select null from t2 where y = x )That is processed more like:
for x in ( select * from t1 )
loop
if ( exists ( select null from t2 where y = x.x )
then
OUTPUT THE RECORD
end if
end loopIt always results in a full scan of T1 whereas the first query can make use of
an index on T1(x).
So, when is where exists appropriate and in appropriate?Lets say the result of the subquery
( select y from T2 )is "huge" and takes a long time. But the table T1 is relatively small and
executing ( select null from t2 where y = x.x ) is very very fast (nice index on
t2(y)). Then the exists will be faster as the time to full scan T1 and do the
index probe into T2 could be less then the time to simply full scan T2 to build
the subquery we need to distinct on.
Lets say the result of the subquery is small -- then IN is typicaly more
appropriate.
If both the subquery and the outer table are huge -- either might work as well
as the other -- depends on the indexes and other factors.
--这句话已发生错误,对于行级别触发器,不能再引用触发表,却你再次引用了MM_PlanDtl.解决方法可以这样,建instead of触发器
create view v_MM_PlanDtl as select * from MM_PlanDtl;
/
CREATE OR REPLACE TRIGGER "MATERIAL"."TRG_PLANDTL"
instead of DELETE or UPDATE OF PD_FLAG ON v_MM_PLANDTL
FOR EACH ROW
Declare strMainFlag char(1);
strID varchar2(12);
stmt varchar2(200);
BEGIN
strID := :new.pm_nativeid;
select min( MM_PlanDtl.PD_Flag) into strMainFlag from MM_PlanDtl where MM_PlanDtl.PM_NativeID=strID;if strMainFlag is null then
strMainFlag :='0';
end if;Update MM_PlanMain set PM_Flag =strMainFlag where MM_Planmain.Pm_NativeID=strID;
end;
/