create or replace trigger tri_STATE_ID_change
before update of STATE_ID on EQ_T_T_INFO
for each row
when (new.STATE_ID <> old.STATE_ID)
DECLARE
ID NUMBER(9);
dCODE_NAME varchar2(50);
dCODE_NAMES varchar2(50);
dMESSAGE_CONTEXT varchar(3000);
dmemberid varchar2(50);
dsendtype varchar2(50);
ddelaydate varchar2(50);
dintervaldate varchar2(50);
cursor kc is
select MEMBER_ID,Send_type,DELAY_DATE,Interval_DATE FROM EQ_MEMBER_CONFIG WHERE EQ_ID=:old.EQ_ID;--声明游标
begin
open kc;
loop
fetch kc into dmemberid,dsendtype,ddelaydate,dintervaldate;--取出人员id,提醒方式,延时时间,间隔时间
exit when kc%notfound;
select CODE_NAME into dCODE_NAME from CM_T_M_CODE where KIND_CODE_ID=:old.EQ_ID and CODE_ID=:new.STATE_ID;
select CODE_NAMES into dCODE_NAMES from CM_T_M_CODE where KIND_CODE_ID=:old.EQ_ID and CODE_ID=:new.STATE_ID;
select MESSAGE_CONTEXT into dMESSAGE_CONTEXT from INFO_CONFIG where EQ_ID=:old.EQ_ID and MEMBER_ID=dmemberid;
dMESSAGE_CONTEXT:=replace(dMESSAGE_CONTEXT,dCODE_NAME,dCODE_NAMES);--替换字符串
SELECT NVL(MAX(ID),0) INTO ID FROM INFO_SEND_CONTEXT;--获取最大id
ID:=ID+1;--+1
--插入到信息发送内容表(INFO_SEND_CONTEXT)
insert into INFO_SEND_CONTEXT(ID,Call_State,Call_CONTEXT,EQ_CHANGE_TIME,Send_type,DELAY_DATE,Interval_DATE,MEMBER_ID,EQ_ID,STATE_ID)
values(ID,1,dMESSAGE_CONTEXT,to_Char(sysdate),dsendtype,ddelaydate,dintervaldate,dmemberid,old.EQ_ID,new.STATE_ID);
end loop;
close kc;
end tri_change;
错误信息:Compilation errors for TRIGGER EOS.TRI_STATE_ID_CHANGEError: PLS-00103: Encountered the symbol "" when expecting one of the following:
begin case declare exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge
<a single-quoted SQL string> pipe
<an alternatively-quoted SQL string>
Line: 17
Text: open kc;
before update of STATE_ID on EQ_T_T_INFO
for each row
when (new.STATE_ID <> old.STATE_ID)
DECLARE
ID NUMBER(9);
dCODE_NAME varchar2(50);
dCODE_NAMES varchar2(50);
dMESSAGE_CONTEXT varchar(3000);
dmemberid varchar2(50);
dsendtype varchar2(50);
ddelaydate varchar2(50);
dintervaldate varchar2(50);
cursor kc is
select MEMBER_ID,Send_type,DELAY_DATE,Interval_DATE FROM EQ_MEMBER_CONFIG WHERE EQ_ID=:old.EQ_ID;--声明游标
begin
open kc;
loop
fetch kc into dmemberid,dsendtype,ddelaydate,dintervaldate;--取出人员id,提醒方式,延时时间,间隔时间
exit when kc%notfound;
select CODE_NAME into dCODE_NAME from CM_T_M_CODE where KIND_CODE_ID=:old.EQ_ID and CODE_ID=:new.STATE_ID;
select CODE_NAMES into dCODE_NAMES from CM_T_M_CODE where KIND_CODE_ID=:old.EQ_ID and CODE_ID=:new.STATE_ID;
select MESSAGE_CONTEXT into dMESSAGE_CONTEXT from INFO_CONFIG where EQ_ID=:old.EQ_ID and MEMBER_ID=dmemberid;
dMESSAGE_CONTEXT:=replace(dMESSAGE_CONTEXT,dCODE_NAME,dCODE_NAMES);--替换字符串
SELECT NVL(MAX(ID),0) INTO ID FROM INFO_SEND_CONTEXT;--获取最大id
ID:=ID+1;--+1
--插入到信息发送内容表(INFO_SEND_CONTEXT)
insert into INFO_SEND_CONTEXT(ID,Call_State,Call_CONTEXT,EQ_CHANGE_TIME,Send_type,DELAY_DATE,Interval_DATE,MEMBER_ID,EQ_ID,STATE_ID)
values(ID,1,dMESSAGE_CONTEXT,to_Char(sysdate),dsendtype,ddelaydate,dintervaldate,dmemberid,old.EQ_ID,new.STATE_ID);
end loop;
close kc;
end tri_change;
错误信息:Compilation errors for TRIGGER EOS.TRI_STATE_ID_CHANGEError: PLS-00103: Encountered the symbol "" when expecting one of the following:
begin case declare exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge
<a single-quoted SQL string> pipe
<an alternatively-quoted SQL string>
Line: 17
Text: open kc;
......
ID :=ID+1;--+1
--插入到信息发送内容表(INFO_SEND_CONTEXT)
insert into INFO_SEND_CONTEXT(ID,Call_State,Call_CONTEXT,EQ_CHANGE_TIME,
Send_type,DELAY_DATE,Interval_DATE,MEMBER_ID,EQ_ID,STATE_ID)
values(ID,1,dMESSAGE_CONTEXT,to_Char(sysdate),dsendtype,
ddelaydate,dintervaldate,dmemberid,old.EQ_ID,new.STATE_ID);--这里
--这里是:old.EQ_ID,:new.STATE_ID,而不是old.EQ_ID,new.STATE_ID
--在<trigger body>里面只能引用:old.和:new.不能使用old.和new.
end loop;
close kc;
......