关于Oracle触发器的问题(300)

由于不会Oracle，由Sqlserver转过来的触发器。结果语法总是错误。哪位会Oracle帮助改改create or replace TRIGGER Trg_email after insert or update ON Relex.AlertHistory for each rowAS BEGIN declare r_from int;--:所在的位置 r_len int;--长度 r_Identifier varchar2(4000);--Idnetity字符串 r_id int; --- Fracas.AlertHistory 的id r_currentname varchar2(255);--当前标示符 r_prjname varchar2(255);--系统名称 r_new varchar2(4000);--新生成的字符串,带引号的标识符 r_Setid int;--系统id r_solutionid int;---项目id r_ssql varchar2(4000);--要执行的sql语句 set serveroutput offif (updating(RecordsMatched)) thenbegin --取得记录 select new.setid into r_setid,new.id into r_id, cast(new.RecordsMatched as varchar2(4000) into r_Identifier from :new ; if (len(r_Identifier)<1) then GOTO TRG_END ; --取得：的位置和总的长度 r_from:=instr(':', r_Identifier) ; r_len:= len(@Identifier); --项目id select solutionid into r_solutionid from Relex.DatabaseFiles where [id]=r_setid; --如果到：结束，就退出 if (r_from=r_len) then GOTO TRG_END; if (upper(substr(r_identifier,1,1))='I') then -- 取得系统,从第5个开始中文，英文从11个开始 r_prjname:=substrc(r_Identifier,11,r_from-11); else r_prjname:=substr(r_Identifier,5,r_from-2-5); end if r_setid:=0; --取得setid select [id] into r_setid from Relex.DatabaseFiles where [filename]=r_prjname and solutionid=r_solutionid; --如果没有找到项目对应的id，退出 if (r_setid=0) then GOTO TRG_END; --取得总长度 r_len:=length(r_Identifier); --取得标识符字符串 r_Identifier:=substr(r_Identifier,r_from+2,r_len-r_from-2); --加引号的处理过程 r_new:='('; while (instr(',',r_Identifier)>0) Loop r_len:=instr(',',r_Identifier); r_currentname:=substr(r_Identifier,1,r_len-1); --去掉左右字符串 r_currentname:=rtrim(ltrim(r_currentname)); r_new:=r_new+''''+r_currentname +''''+','; r_Identifier:=substr(r_Identifier,r_len+1,length(r_Identifier)-r_len) ; end Loop; r_new:=r_new+''''+ltrim(rtrim(r_Identifier))+''''; r_new:=r_new +')'; --更新语句的拼写 r_ssql:='update Relex.Incidents1 set IncUserBool3=1 where ((IncUserBool3 is null) or(IncUserBool3=0)) '; r_ssql:=r_ssql+' and (setid='+cast(r_setid as varchar2(255))+')'; r_ssql:=r_ssql+' and (Identifier in '+r_new+')'; ---执行更新程序 execute immediate r_ssql;end if; Exception --暂时没有异常提示 <<TRG_END>>END;

东!

CREATE OR REPLACETRIGGER TRIGGER1BEFORE INSERT OR UPDATE ON RELEX.ALERTHISTORY referencing old as old new as new for each rowBEGIN declare r_from int;--:所在的位置 r_len int;--长度 r_Identifier varchar2(4000);--Idnetity字符串 r_id int; --- Fracas.AlertHistory 的id r_currentname varchar2(255);--当前标示符 r_prjname varchar2(255);--系统名称 r_new varchar2(4000);--新生成的字符串,带引号的标识符 r_Setid int;--系统id r_solutionid int;---项目id r_ssql varchar2(4000);--要执行的sql语句--set serveroutput off--取得记录 -- cast(new.RecordsMatched as varchar2(4000)) into r_Identifier if (updating(RecordsMatched)) thenbegin select new.setid into r_setid,new."id" into r_id, new.RecordsMatched into r_Identifier from :new; r_Identifier:=rtrim(r_Identifier); if length(r_Identifier)<1 then return; -- GOTO TRG_END ; --取得：的位置和总的长度 r_from:=instr(':', r_Identifier) ; r_len:= length(r_Identifier); --项目id select solutionid into r_solutionid from Relex.DatabaseFiles where id=r_setid; --如果到：结束，就退出 if (r_from=r_len) then GOTO TRG_END; if (upper(substr(r_identifier,1,1))='I') then -- 取得系统,从第5个开始中文，英文从11个开始 r_prjname:=substrc(r_Identifier,11,r_from-11); else r_prjname:=substr(r_Identifier,5,r_from-2-5); end if r_setid:=0; --取得setid select "id" into r_Setid from Relex.DatabaseFiles where filename=r_prjname and solutionid=r_solutionid; --如果没有找到项目对应的id，退出 if (r_setid=0) then return; -- GOTO TRG_END; --取得总长度 r_len:=length(r_Identifier); --取得标识符字符串 r_Identifier:=substr(r_Identifier,r_from+2,r_len-r_from-2); --加引号的处理过程 r_new:='('; while (instr(',',r_Identifier)>0) Loop r_len:=instr(',',r_Identifier); r_currentname:=substr(r_Identifier,1,r_len-1); --去掉左右字符串 r_currentname:=rtrim(ltrim(r_currentname)); r_new:=r_new+''''||r_currentname ||''''||','; r_Identifier:=substr(r_Identifier,r_len+1,length(r_Identifier)-r_len) ; end Loop; r_new:=r_new+''''||ltrim(rtrim(r_Identifier))||''''; r_new:=r_new ||')'; --更新语句的拼写 r_ssql:='update Relex.Incidents1 set IncUserBool3=1 where ((IncUserBool3 is null) or(IncUserBool3=0)) '; r_ssql:=r_ssql||' and (setid='||cast(r_setid as varchar2(255))||')'; r_ssql:=r_ssql||' and (Identifier in '||r_new||')'; ---执行更新程序 execute immediate r_ssql; end if; Exception when ON_DATA_FOUND Then return -1 --暂时没有异常提示 --<<TRG_END>> END;

Error(25,1): PLS-00103: 出现符号 "IF"在需要下列之一时： begin function package pragma procedure subtype type use <an identifier> <a double-quoted delimited-identifier> form current cursor 符号 "begin" 被替换为 "IF" 后继续。

Error(27,15): PLS-00103: 出现符号 "."在需要下列之一时： mod <an identifier> <a double-quoted delimited-identifier> <a bind variable> current sql execute forall merge pipe 符号 "<an identifier>" 被替换为 "." 后继续。

Error(27,44): PLS-00103: 出现符号 "INTO"在需要下列之一时： . ( , % from

APPLE,帮你顶

Oracle和SqlServer真的差别太大了

编译和测试都通过了，谢谢各位。create or replace TRIGGER RELEX.TRG_EMail after INSERT OR UPDATE ON RELEX.ALERTHISTORYreferencing old as old new as new for each rowdeclare r_from int;--:所在的位置 r_len int;--长度 r_Identifier varchar2(4000);--Idnetity字符串 r_id int; --- Fracas.AlertHistory 的id r_currentname varchar2(255);--当前标示符 r_prjname varchar2(255);--系统名称 r_new varchar2(4000);--新生成的字符串,带引号的标识符 r_Setid int;--系统id r_solutionid int;---项目id r_ssql varchar2(4000);--要执行的sql语句 s_SetId varchar2(255);--存放临时转换的字符串 BEGIN r_setid := :new.setid; r_id := :new.id; r_Identifier := :new.RecordsMatched; ---要去掉 r_identifier的右边空格 r_Identifier:=rtrim(r_identifier); r_len:= length(r_Identifier); if r_len<1 then return; end if; --取得：的位置和总的长度 r_from:=instr(r_Identifier,':',1,1) ; --项目id select solutionid into r_solutionid from Relex.DatabaseFiles where id=r_setid; --如果到：结束，就退出 if (r_from=r_len) then return; end if; if (upper(substr(r_identifier,1,1))='I') then -- 取得系统,从第5个开始中文，英文从11个开始 r_prjname:=substr(r_Identifier,11,r_from-11); else r_prjname:=substrc(r_Identifier,5,r_from-2-5); end if; r_setid:=0; --取得setid select id into r_setid from Relex.DatabaseFiles where filename=r_prjname and solutionid=r_solutionid; --如果没有找到项目对应的id，退出 if (r_setid=0) then return; end if; --取得总长度 r_len:=length(r_Identifier); --取得标识符字符串 r_Identifier:=substr(r_Identifier,r_from+2,r_len-r_from-1); --加引号的处理过程 r_new:='('; while (instr(r_Identifier,',',1,1)>0) Loop r_len:=instr(r_Identifier,',',1,1); r_currentname:=substr(r_Identifier,1,r_len-1); --去掉左右字符串 r_currentname:=rtrim(ltrim(r_currentname)); r_new:=r_new||''''||r_currentname ||''''||','; r_Identifier:=substr(r_Identifier,r_len+1,length(r_Identifier)-r_len) ; end Loop; r_new:=r_new||''''||ltrim(rtrim(r_Identifier))||''''; r_new:=r_new ||')'; --更新语句的拼写 s_SetId:=rtrim(to_char(r_setid)); r_ssql:='update Relex.Incidents1 set IncUserBool3=1 where ((IncUserBool3 is null) or(IncUserBool3=0)) '; r_ssql:=r_ssql||' and (setid='||s_setId||')'; r_ssql:=r_ssql||' and (Identifier in '||r_new||')'; ---执行更新程序 execute immediate r_ssql; Exception when Others Then return ; END TRG_EMail;

多人接受答案了。