我要编写一个触发器,功能如下
有一个表fl,表中有物品信息,有一个ML表,表中是物品流转信息,
现在新建了一个表FM,表中包含物品信息和流转信息,三个表用code字段关联,
现在要做一个触发器,在ML表Insert时触发,
首先查看FM表中是否有相同code的记录,如果有旧更新FM中的流转信息,如果没有,从FL表中提取物品信息,合并流转信息后插入FM表。请问各位大虾该怎么写啊,着急啊,帮帮忙吧
有一个表fl,表中有物品信息,有一个ML表,表中是物品流转信息,
现在新建了一个表FM,表中包含物品信息和流转信息,三个表用code字段关联,
现在要做一个触发器,在ML表Insert时触发,
首先查看FM表中是否有相同code的记录,如果有旧更新FM中的流转信息,如果没有,从FL表中提取物品信息,合并流转信息后插入FM表。请问各位大虾该怎么写啊,着急啊,帮帮忙吧
FL:
Code(varchar2)
ObjectName(varchar2)
ObjectType(varchar2)
ObjectInfo(varchar2)ML:
Code(varchar2)
SubCode(barchar2)
UseDept(varchar2)
UseDate(Date)FM:
Code(varchar2)
SubCode(barchar2)
ObjectName(varchar2)
ObjectType(varchar2)
ObjectInfo(varchar2)
UseDept(varchar2)
UseDate(Date)
before insert on ml
for each row
declare
fm_count integer;
vcode varchar2(20);
vsubcode varchar2(20);
begin
vcode := :new.code;
vsubcode := :new.subcode;
select count(*) into fm_count from fm where code=vcode and subcode=vsubcode;
if fm_count>0 then
update fm set usedept=:new.usedept,usedate=:new.usedate where code=:new.code and subcode=:new.subcode;
else
insert into fm select a.code,:new.subcode,a.objectname,a.objecttype,a.objectinfo,:new.usedept,:new.usedate
from fl a where a.code=vcode;
end if;
end;
create or replace trigger isnert_ml
before insert on ml
for each row
is
v_sql fl.ObjectName%type;
begin
select ObjectName into v_sql from fm where code=:new.code;
if no_data_found then
insert into fm
select :new.code,:new.subcode,fl.ObjectName,fl.ObjectType,fl.ObjectInfo
:new.UseDept,:new.UseDate
from fl,ml
where fl.code=:new.code;
else then
update fm
set fm.code=:new.code,
fm.SubCode=:new.SubCode,
fm.UseDept=:new.UseDept,
fm.UseDate=:new.UseDate
where fm.code=:old.code;
end if;
end insert_ml;试试这个,看可不可以
before insert on ML
for each row
declare
-- local variables here
Code varchar2(10) ;
V_ObjectName varchar2(10) ;
V_ObjectType varchar2(10) ;
V_ObjectInfo varchar2(10) ;
begin
update FM set SubCode=:new.SubCode,UseDept=:new.UseDept,UseDate=:new.UseDate
where code = :new.code;
if sql%notfound then
insert into FM(Code,SubCode,UseDept,UseDate)
values(:new.code,:new.SubCode,:new.UseDept,:new.UseDate);
end if;
select ObjectName,ObjectType,ObjectInfo into V_ObjectName,V_ObjectType,V_ObjectInfo from fl where code = :NEW.code;
update FM set ObjectName = V_ObjectName,ObjectType=V_ObjectType,ObjectInfo=V_ObjectInfo
where code = :new.code;
end InsertData;
merge into fm
using (select fl.*, ml.subcode, ml.usedept, ml.usedate from fl, ml where fl.code=ml.code and fl.code='xxx') tmp
on (fm.empno = tmp.empno)
when matched then
update set fm.subcode=tmp.subcode, ....
when not matched then
insert
(code, subcode, objectname, objecttype, objectinfo, usedept, usedate)
values
(tmp.code, tmp.subcode, tmp.objectname, tmp.objecttype, tmp.objectinfo, tmp.usedept, tmp.usedate)
before insert on ML
for each row
declare
v_count number;
begin
select count(1) into v_count from fm where code = :new.code; if v_count > 0 then
update fm set usedept=:new.usedept,usedate=:new.usedate where code=:new.code and subcode=:new.subcode;
else
insert into fm select a.code,:new.subcode,a.objectname,a.objecttype,a.objectinfo,:new.usedept,:new.usedate
from fl a where a.code=:new.code;
end if; end trigger_for_ml;
版服受到了吗?怎么也不show一下