create or replace trigger trg_doer_base_message
after update on doer_order_bi
for each row
declare
my_sendno varchar2(1024); --下一处理人编号
my_sendname varchar2(1024); --下已处理人名称
messageBody varchar2(1024); --消息体
begin
--当更新时要进行的操作
if updating Then
select c_action_disc into messageBody from doer_workflow_role where C_ACTION_STATE_CODE =:new.C_BI_STATE;
my_sendno := :new.C_BI_NOWDEALSERIALNO;
select c_name into my_sendname from doer_base_user t where t.c_userserialno = my_sendno;
insert all into doer_base_message(
c_messageno,c_sendno,c_sendname,c_receiveno,c_receivename,c_body,c_read,c_flag,c_sendtime,c_orderno,c_ordercode
)values(
c_messageno,c_sendno,c_sendname,c_receiveno,c_receivename,c_body,c_read,c_flag,c_sendtime,c_orderno,c_ordercode
)
select sys_guid() as c_messageno,
my_sendno as c_sendno,
my_sendname as c_sendname,
c_receiveno,
c_receivename ,
messageBody as c_body,
'1' as c_read,
'1' as c_flag,
sysdate as c_sendtime,
:new.c_bi_serialno as c_orderno,
:new.c_bi_code as c_ordercode
from(
select c_receiveno,c_receivename from(
select t_rec.column_value as c_receiveno,t_user.c_name as c_receivename from (
table(
fn_split(:new.C_BI_NEXTDEALSERIALNO,'|')
)
) t_rec
left join doer_base_user t_user on t_rec.column_value = t_user.c_userserialno
) t_temp1
) t_temp2;
End if;
end trg_doer_base_message;