为表ANNOUNCEMENT创建插入触发器,原先设计的库是先将选择的所有人员插入到ANNOUNCEMENTORG表中,然后在ANNOUNCEMENT表中插入其他相关信息。
表ANNOUNCEMENTORG中的ANNOUNCEMENTCODE和ANNOUNCEMENT中的CODE关联。
create or replace trigger TR_ANNOUNCEMENT_SYIT
after insert on ANNOUNCEMENT
for each row
declare
v_usercode ANNOUNCEMENTORG.Relationcode%TYPE; --声明一个临时变量,用来存储用户名
ucode varchar2(200);
phoneNumber varchar2(20);
deptName varchar2(50);
Cursor cur_usercode is
--声明一个游标,用来存储查询出来的人员名
select relationcode from ANNOUNCEMENTORG where ANNOUNCEMENTCODE=:new.CODE;
begin
select ORGNAME into deptName from hefeiuums.sys_organization where ORGID=(select ORGID from hefeiuums.sys_User where username=:new.PUBLISHER);
open cur_usercode;
loop
fetch cur_usercode into v_usercode;
exit when cur_usercode%NOTFOUND; --读到最后一条语句后退出
select mobile into phoneNumber from hefeiuums.sys_User where usercode=v_usercode;
--当手机号码不为空,进行数据插入
if phoneNumber is not null then
insert into T_OASMS(phone,content,sender,sendernm) values(phoneNumber,deptName||'通知:'||:new.TITLE||',已发送到您的数字排水后台,请登录平台查收!',:new.PROMUGATOR,:new.PUBLISHER);
end if;
end loop;
close cur_usercode;
end;
为FLOWDOCSTATE表创建热线流程下一步短信通知功能:
create or replace trigger TR_FLOWDOCSTATE_SYIT
after insert on FLOWDOCSTATE
for each row
declare
v_userphone hefeiuums.sys_User.MOBILE%TYPE; --声明一个临时变量,用来存储手机号
title varchar2(200); --热线标题
phone varchar2(20); --分管领导手机号码
v_phone2 varchar2(20); --部门所有人员手机号码
uname varchar2(20);
deptName varchar2(20);
Cursor cur_userphone is
select MOBILE from hefeiuums.sys_User where ORGID=:new.ACCOUNTABLEUNIT; --声明一个游标,用来存储根据部门名称查询出来的手机号码
begin
if :new.WORKFLOWCODE='HotLineCase' then
select CASETITLE into title from Case where CASECODE=:new.DOCCODE;
if :new.ACCOUNTABLEUSER is not null then
select mobile into phone from hefeiuums.sys_User where usercode=:new.ACCOUNTABLEUSER;
select username into uname from hefeiuums.sys_User where usercode=:new.ACCOUNTABLEUSER;
if phone is not null then
--给流程的下一步接收者发送消息
insert into T_OASMS(phone,content,sender,sendernm) values(phone,'有新的热线信息:'||title||',请登录办公系统及时处理!',uname,uname);
end if;
elsif :new.ACCOUNTABLEUNIT is not null then
--游标遍历
open cur_userphone;
loop
fetch cur_userphone into v_phone2;
exit when cur_userphone%NOTFOUND; --读到最后一条语句后退出
if v_phone2 is not null then
--给流程的所有部门接收者发送消息
select ORGNAME into deptName from hefeiuums.sys_organization where ORGID=:new.ACCOUNTABLEUNIT;
insert into T_OASMS(phone,content,sender,sendernm) values(v_phone2,'有新的热线信息:'||title||',请登录办公系统及时处理!',deptName,deptName);
end if;
end loop;
close cur_userphone;
end if;
end if;
end;