/*************************************环节意见存储过程************************************/ IF EXISTS (SELECT * FROM sysobjects WHERE name = 'p_copy_t_case_stage_suggestion' AND user_name(uid) = 'dbo') DROP PROCEDURE [dbo].[p_copy_t_case_stage_suggestion] GOCREATE PROCEDURE [dbo].[p_copy_t_case_stage_suggestion] --WITH ENCRYPTION AS BEGIN SET NOCOUNT ON
BEGIN TRANSACTION; declare @executer_time datetime
set @executer_time = [dbo].[p_fetch_executer_datetime]() delete from SZUM_ZHZF_ExChange.dbo.t_case_stage_suggestion where id in ( select id from SZUM_ZHZF_IntergratedPlatform.dbo.t_case_stage_suggestion where db_last_updated_date>@executer_time )
insert into SZUM_ZHZF_ExChange.dbo.t_case_stage_suggestion select * from SZUM_ZHZF_IntergratedPlatform.dbo.t_case_stage_suggestion where db_last_updated_date>@executer_time
COMMIT TRANSACTION;
END GO这是同步两个表中数据的存储过程 /************************************************环节意见表*********************************************/ IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[trg_t_case_stage_suggestion_insert]')) DROP TRIGGER [dbo].[trg_t_case_stage_suggestion_insert] GO create trigger [dbo].[trg_t_case_stage_suggestion_insert] on [dbo].[t_case_stage_suggestion] for insert asdeclare @byla_activity_id uniqueidentifier --不予立案环节标识 set @byla_activity_id = 'c91d6740-2cd1-4c0c-aa84-9ac00104c7ac' declare @assh_activity_id uniqueidentifier --案审审核环节标识 set @assh_activity_id = 'b595e19f-0092-42a9-be15-9ac001052e60' declare @fdzsh_activity_id uniqueidentifier --副队长审核 set @fdzsh_activity_id = '8a540876-88c2-47a6-9567-9ac0010550c4' declare @ddzsh_activity_id uniqueidentifier --大队长审核 set @ddzsh_activity_id = 'e5536807-fd17-4941-9969-9ac0010562df' declare @zzaj_activity_id uniqueidentifier --中止案件 set @zzaj_activity_id = '70592e50-b326-4c51-83d7-9adb011a6248' declare @zhongzhi_activity_id uniqueidentifier --终止案件 set @zhongzhi_activity_id = '8704d3df-7f7b-4222-949a-ea8faa4fff6e'declare @activity_id uniqueidentifier select @activity_id = acitivity_id from inserted --select @activity_id = t3.activity_identifier --from inserted t1 -- , ty_wf_ex_local_activity_instance t2 -- , ty_wf_ex_local_activity_extend t3 --where t1.acitivity_instance_id = t2.id -- and t2.workflow_activity_id = t3.activity_idif @activity_id = @byla_activity_id begin --不予立案 insert into SZUM_ZHZF_ExChange.dbo.T_TEMP_XZZF_BYLA ( ZJID ,AJID ,BYLAYY ,SQR ,SQSJ ,PZR ,PZYJ ,PZSJ ,JHZT ,LRSJ ,REMARK1 ,REMARK2 ,REMARK3 ,modiid ,IsExec ) select a.id as ZJID ,a.case_id as AJID ,isnull(c.BYLAYY,'') as BYLAYY ,isnull(c.SQR,'') as SQR ,isnull(c.SQSJ,'') as SQSJ ,d.user_name as PZR ,isnull(a.handle_suggestion,'') as PZYJ ,isnull(a.handle_date,'') as PZSJ ,'0' as JHZT ,a.db_created_date as LRSJ ,NULL as REMARK1 ,'N' as REMARK2 ,NULL as REMARK3 ,1 --新增 ,0 from inserted as a , SZUM_ZHZF_IntergratedPlatform.dbo.ty_wf_ex_local_activity_instance as b , (select t1.acitivity_instance_id, t3.handle_suggestion as BYLAYY, t4.user_name as SQR, t3.handle_date as SQSJ from inserted t1 , SZUM_ZHZF_IntergratedPlatform.dbo.ty_wf_ex_local_activity_instance t2 , SZUM_ZHZF_IntergratedPlatform.dbo.t_case_stage_suggestion t3 , SZUM_ZHZF_IntergratedPlatform.dbo.t_sys_extention_user t4 where t1.acitivity_instance_id = t2.id and t2.prev_activity_instance_id = t3.acitivity_instance_id and t3.db_created_id = t4.pmi_user_id) c , SZUM_ZHZF_IntergratedPlatform.dbo.t_sys_extention_user d --, t_case_basic_info e where a.acitivity_instance_id = b.id and a.acitivity_instance_id = c.acitivity_instance_id and a.db_created_id = d.pmi_user_id --and a.case_id = e.case_id -- and e.case_code is not null -- and e.case_code <> '' end else if @activity_id = @assh_activity_id begin --案件审核 insert into SZUM_ZHZF_ExChange.dbo.T_TEMP_XZZF_SH ( ZJID ,AJID ,CLYJ ,SHR ,SHSJ ,JHZT ,LRSJ ,REMARK1 ,REMARK2 ,REMARK3 ,modiid ,IsExec ) select a.id as ZJID ,c.case_code as AJID ,isnull(a.handle_suggestion,'') as CLYJ ,d.user_name as SHR ,isnull(a.handle_date,'') as SHSJ ,'0' as JHZT ,a.db_created_date as LRSJ ,NULL as REMARK1 ,'N' as REMARK2 ,NULL as REMARK3 ,1 --新增 ,0 from inserted as a , SZUM_ZHZF_IntergratedPlatform.dbo.t_sys_extention_user d , SZUM_ZHZF_IntergratedPlatform.dbo.t_case_basic_info c where a.db_created_id = d.pmi_user_id and a.case_id = c.case_id and c.case_code is not null and c.case_code <> '' end else if @activity_id = @fdzsh_activity_id or @activity_id = @ddzsh_activity_id begin --案件批准 insert into SZUM_ZHZF_ExChange.dbo.T_TEMP_XZZF_PZ ( ZJID ,AJID ,CLYJ ,PZR ,PZSJ ,JHZT ,LRSJ ,REMARK1 ,REMARK2 ,REMARK3 ,modiid ,IsExec ) select a.id as ZJID ,c.case_code as AJID ,isnull(a.handle_suggestion,'') as CLYJ ,d.user_name as SHR ,isnull(a.handle_date,'') as SHSJ ,'0' as JHZT ,a.db_created_date as LRSJ ,NULL as REMARK1 ,'N' as REMARK2 ,NULL as REMARK3 ,1 --新增 ,0 from inserted as a , SZUM_ZHZF_IntergratedPlatform.dbo.t_sys_extention_user d , SZUM_ZHZF_IntergratedPlatform.dbo.t_case_basic_info c where a.db_created_id = d.pmi_user_id and a.case_id = c.case_id and c.case_code is not null and c.case_code <> '' end else if @activity_id = @zzaj_activity_id begin --案件中止 insert into SZUM_ZHZF_ExChange.dbo.T_TEMP_XZZF_AJZZ ( ZJID ,AJID ,SQHJ ,SQYY ,SQR ,SQSJ ,PZR ,PZYJ ,PZSJ ,JHZT ,LRSJ ,REMARK1 ,REMARK2 ,REMARK3 ,modiid ,IsExec ) select a.id as ZJID ,e.case_code as AJID ,c.SQHJ ,isnull(c.SQYY,'') as SQYY ,isnull(c.SQR,'') as SQR ,isnull(c.SQSJ,'') as SQSJ ,d.user_name as PZR ,isnull(a.handle_suggestion,'') as PZYJ ,isnull(a.handle_date,'') as PZSJ ,'0' as JHZT ,a.db_created_date as LRSJ ,NULL as REMARK1 ,'N' as REMARK2 ,NULL as REMARK3 ,1 --新增 ,0 from inserted as a , SZUM_ZHZF_IntergratedPlatform.dbo.ty_wf_ex_local_activity_instance as b , (select t1.acitivity_instance_id, t3.handle_suggestion as SQYY, t4.user_name as SQR, t3.handle_date as SQSJ, t5.activity_name as SQHJ from inserted t1 , SZUM_ZHZF_IntergratedPlatform.dbo.ty_wf_ex_local_activity_instance t2 , SZUM_ZHZF_IntergratedPlatform.dbo.t_case_stage_suggestion t3 , SZUM_ZHZF_IntergratedPlatform.dbo.t_sys_extention_user t4 , SZUM_ZHZF_IntergratedPlatform.dbo.ty_wf_ex_local_activity_extend t5 where t1.acitivity_instance_id = t2.id and t2.prev_activity_instance_id = t3.acitivity_instance_id and t3.db_created_id = t4.pmi_user_id and t2.workflow_activity_id = t5.activity_id) c , SZUM_ZHZF_IntergratedPlatform.dbo.t_sys_extention_user d , SZUM_ZHZF_IntergratedPlatform.dbo.t_case_basic_info e where a.acitivity_instance_id = b.id and a.acitivity_instance_id = c.acitivity_instance_id and a.db_created_id = d.pmi_user_id and a.case_id = e.case_id and e.case_code is not null and e.case_code <> '' end else if @activity_id = @zhongzhi_activity_id begin --案件终止 insert into SZUM_ZHZF_ExChange.dbo.T_TEMP_XZZF_AJZHZ ( ZJID ,AJID ,SQHJ ,SQYY ,SQR ,SQSJ ,PZR ,PZYJ ,PZSJ ,JHZT ,LRSJ ,REMARK1 ,REMARK2 ,REMARK3 ,modiid ,IsExec ) select a.id as ZJID ,e.case_code as AJID ,c.SQHJ ,isnull(c.SQYY,'') as SQYY ,isnull(c.SQR,'') as SQR ,isnull(c.SQSJ,'') as SQSJ ,d.user_name as PZR ,isnull(a.handle_suggestion,'') as PZYJ ,isnull(a.handle_date,'') as PZSJ ,'0' as JHZT ,a.db_created_date as LRSJ ,NULL as REMARK1 ,'N' as REMARK2 ,NULL as REMARK3 ,1 --新增 ,0 from inserted as a , SZUM_ZHZF_IntergratedPlatform.dbo.ty_wf_ex_local_activity_instance as b , (select t1.acitivity_instance_id, t2.handle_content as SQYY, t4.user_name as SQR, t2.execute_date as SQSJ, '案件执行' as SQHJ from inserted t1 ,SZUM_ZHZF_IntergratedPlatform.dbo.t_case_execute_info t2 ,SZUM_ZHZF_IntergratedPlatform.dbo.t_sys_extention_user t4 where t1.case_id = t2.case_id and t2.db_created_id = t4.pmi_user_id) c , SZUM_ZHZF_IntergratedPlatform.dbo.t_sys_extention_user d , SZUM_ZHZF_IntergratedPlatform.dbo.t_case_basic_info e where a.acitivity_instance_id = b.id and a.acitivity_instance_id = c.acitivity_instance_id and a.db_created_id = d.pmi_user_id and a.case_id = e.case_id and e.case_code is not null and e.case_code <> '' end GO这是触发器
把判斷的變量的條件寫在WHERE之後,不需要變量。
也就是写多条insert 语句吗?
DECLARE @t TABLE(I INT) INSERT @t SELECT 1 WHERE 1=0 SELECT * FROM @t /* I -----------(0 行受影响) */ 如果條件不滿足,也就不會插入任何內容。跟用IF判斷結果是一樣的。
BEGIN TRANSACTION; declare @executer_time datetime
set @executer_time = [dbo].[p_fetch_executer_datetime]() delete from SZUM_ZHZF_ExChange.dbo.t_case_stage_suggestion where id in ( select id from SZUM_ZHZF_IntergratedPlatform.dbo.t_case_stage_suggestion where db_last_updated_date>@executer_time )
insert into SZUM_ZHZF_ExChange.dbo.t_case_stage_suggestion select * from SZUM_ZHZF_IntergratedPlatform.dbo.t_case_stage_suggestion where db_last_updated_date>@executer_time
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'p_copy_t_case_stage_suggestion' AND user_name(uid) = 'dbo')
DROP PROCEDURE [dbo].[p_copy_t_case_stage_suggestion]
GOCREATE PROCEDURE [dbo].[p_copy_t_case_stage_suggestion]
--WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON
BEGIN TRANSACTION;
declare @executer_time datetime
set @executer_time = [dbo].[p_fetch_executer_datetime]() delete from SZUM_ZHZF_ExChange.dbo.t_case_stage_suggestion
where id in
(
select id from SZUM_ZHZF_IntergratedPlatform.dbo.t_case_stage_suggestion
where db_last_updated_date>@executer_time
)
insert into SZUM_ZHZF_ExChange.dbo.t_case_stage_suggestion
select * from SZUM_ZHZF_IntergratedPlatform.dbo.t_case_stage_suggestion
where db_last_updated_date>@executer_time
COMMIT TRANSACTION;
END
GO这是同步两个表中数据的存储过程
/************************************************环节意见表*********************************************/
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[trg_t_case_stage_suggestion_insert]'))
DROP TRIGGER [dbo].[trg_t_case_stage_suggestion_insert]
GO
create trigger [dbo].[trg_t_case_stage_suggestion_insert] on [dbo].[t_case_stage_suggestion] for insert
asdeclare @byla_activity_id uniqueidentifier --不予立案环节标识
set @byla_activity_id = 'c91d6740-2cd1-4c0c-aa84-9ac00104c7ac'
declare @assh_activity_id uniqueidentifier --案审审核环节标识
set @assh_activity_id = 'b595e19f-0092-42a9-be15-9ac001052e60'
declare @fdzsh_activity_id uniqueidentifier --副队长审核
set @fdzsh_activity_id = '8a540876-88c2-47a6-9567-9ac0010550c4'
declare @ddzsh_activity_id uniqueidentifier --大队长审核
set @ddzsh_activity_id = 'e5536807-fd17-4941-9969-9ac0010562df'
declare @zzaj_activity_id uniqueidentifier --中止案件
set @zzaj_activity_id = '70592e50-b326-4c51-83d7-9adb011a6248'
declare @zhongzhi_activity_id uniqueidentifier --终止案件
set @zhongzhi_activity_id = '8704d3df-7f7b-4222-949a-ea8faa4fff6e'declare @activity_id uniqueidentifier
select @activity_id = acitivity_id
from inserted
--select @activity_id = t3.activity_identifier
--from inserted t1
-- , ty_wf_ex_local_activity_instance t2
-- , ty_wf_ex_local_activity_extend t3
--where t1.acitivity_instance_id = t2.id
-- and t2.workflow_activity_id = t3.activity_idif @activity_id = @byla_activity_id
begin
--不予立案
insert into SZUM_ZHZF_ExChange.dbo.T_TEMP_XZZF_BYLA
(
ZJID
,AJID
,BYLAYY
,SQR
,SQSJ
,PZR
,PZYJ
,PZSJ
,JHZT
,LRSJ
,REMARK1
,REMARK2
,REMARK3
,modiid
,IsExec
)
select
a.id as ZJID
,a.case_id as AJID
,isnull(c.BYLAYY,'') as BYLAYY
,isnull(c.SQR,'') as SQR
,isnull(c.SQSJ,'') as SQSJ
,d.user_name as PZR
,isnull(a.handle_suggestion,'') as PZYJ
,isnull(a.handle_date,'') as PZSJ
,'0' as JHZT
,a.db_created_date as LRSJ
,NULL as REMARK1
,'N' as REMARK2
,NULL as REMARK3
,1 --新增
,0
from inserted as a
, SZUM_ZHZF_IntergratedPlatform.dbo.ty_wf_ex_local_activity_instance as b
, (select t1.acitivity_instance_id, t3.handle_suggestion as BYLAYY, t4.user_name as SQR, t3.handle_date as SQSJ
from inserted t1
, SZUM_ZHZF_IntergratedPlatform.dbo.ty_wf_ex_local_activity_instance t2
, SZUM_ZHZF_IntergratedPlatform.dbo.t_case_stage_suggestion t3
, SZUM_ZHZF_IntergratedPlatform.dbo.t_sys_extention_user t4
where t1.acitivity_instance_id = t2.id
and t2.prev_activity_instance_id = t3.acitivity_instance_id
and t3.db_created_id = t4.pmi_user_id) c
, SZUM_ZHZF_IntergratedPlatform.dbo.t_sys_extention_user d
--, t_case_basic_info e
where a.acitivity_instance_id = b.id
and a.acitivity_instance_id = c.acitivity_instance_id
and a.db_created_id = d.pmi_user_id
--and a.case_id = e.case_id
-- and e.case_code is not null
-- and e.case_code <> ''
end
else if @activity_id = @assh_activity_id
begin
--案件审核
insert into SZUM_ZHZF_ExChange.dbo.T_TEMP_XZZF_SH
(
ZJID
,AJID
,CLYJ
,SHR
,SHSJ
,JHZT
,LRSJ
,REMARK1
,REMARK2
,REMARK3
,modiid
,IsExec
)
select
a.id as ZJID
,c.case_code as AJID
,isnull(a.handle_suggestion,'') as CLYJ
,d.user_name as SHR
,isnull(a.handle_date,'') as SHSJ
,'0' as JHZT
,a.db_created_date as LRSJ
,NULL as REMARK1
,'N' as REMARK2
,NULL as REMARK3
,1 --新增
,0
from inserted as a
, SZUM_ZHZF_IntergratedPlatform.dbo.t_sys_extention_user d
, SZUM_ZHZF_IntergratedPlatform.dbo.t_case_basic_info c
where a.db_created_id = d.pmi_user_id
and a.case_id = c.case_id
and c.case_code is not null
and c.case_code <> ''
end
else if @activity_id = @fdzsh_activity_id or @activity_id = @ddzsh_activity_id
begin
--案件批准
insert into SZUM_ZHZF_ExChange.dbo.T_TEMP_XZZF_PZ
(
ZJID
,AJID
,CLYJ
,PZR
,PZSJ
,JHZT
,LRSJ
,REMARK1
,REMARK2
,REMARK3
,modiid
,IsExec
)
select
a.id as ZJID
,c.case_code as AJID
,isnull(a.handle_suggestion,'') as CLYJ
,d.user_name as SHR
,isnull(a.handle_date,'') as SHSJ
,'0' as JHZT
,a.db_created_date as LRSJ
,NULL as REMARK1
,'N' as REMARK2
,NULL as REMARK3
,1 --新增
,0
from inserted as a
, SZUM_ZHZF_IntergratedPlatform.dbo.t_sys_extention_user d
, SZUM_ZHZF_IntergratedPlatform.dbo.t_case_basic_info c
where a.db_created_id = d.pmi_user_id
and a.case_id = c.case_id
and c.case_code is not null
and c.case_code <> ''
end
else if @activity_id = @zzaj_activity_id
begin
--案件中止
insert into SZUM_ZHZF_ExChange.dbo.T_TEMP_XZZF_AJZZ
(
ZJID
,AJID
,SQHJ
,SQYY
,SQR
,SQSJ
,PZR
,PZYJ
,PZSJ
,JHZT
,LRSJ
,REMARK1
,REMARK2
,REMARK3
,modiid
,IsExec
)
select
a.id as ZJID
,e.case_code as AJID
,c.SQHJ
,isnull(c.SQYY,'') as SQYY
,isnull(c.SQR,'') as SQR
,isnull(c.SQSJ,'') as SQSJ
,d.user_name as PZR
,isnull(a.handle_suggestion,'') as PZYJ
,isnull(a.handle_date,'') as PZSJ
,'0' as JHZT
,a.db_created_date as LRSJ
,NULL as REMARK1
,'N' as REMARK2
,NULL as REMARK3
,1 --新增
,0
from inserted as a
, SZUM_ZHZF_IntergratedPlatform.dbo.ty_wf_ex_local_activity_instance as b
, (select t1.acitivity_instance_id, t3.handle_suggestion as SQYY, t4.user_name as SQR, t3.handle_date as SQSJ, t5.activity_name as SQHJ
from inserted t1
, SZUM_ZHZF_IntergratedPlatform.dbo.ty_wf_ex_local_activity_instance t2
, SZUM_ZHZF_IntergratedPlatform.dbo.t_case_stage_suggestion t3
, SZUM_ZHZF_IntergratedPlatform.dbo.t_sys_extention_user t4
, SZUM_ZHZF_IntergratedPlatform.dbo.ty_wf_ex_local_activity_extend t5
where t1.acitivity_instance_id = t2.id
and t2.prev_activity_instance_id = t3.acitivity_instance_id
and t3.db_created_id = t4.pmi_user_id
and t2.workflow_activity_id = t5.activity_id) c
, SZUM_ZHZF_IntergratedPlatform.dbo.t_sys_extention_user d
, SZUM_ZHZF_IntergratedPlatform.dbo.t_case_basic_info e
where a.acitivity_instance_id = b.id
and a.acitivity_instance_id = c.acitivity_instance_id
and a.db_created_id = d.pmi_user_id
and a.case_id = e.case_id
and e.case_code is not null
and e.case_code <> ''
end
else if @activity_id = @zhongzhi_activity_id
begin
--案件终止
insert into SZUM_ZHZF_ExChange.dbo.T_TEMP_XZZF_AJZHZ
(
ZJID
,AJID
,SQHJ
,SQYY
,SQR
,SQSJ
,PZR
,PZYJ
,PZSJ
,JHZT
,LRSJ
,REMARK1
,REMARK2
,REMARK3
,modiid
,IsExec
)
select
a.id as ZJID
,e.case_code as AJID
,c.SQHJ
,isnull(c.SQYY,'') as SQYY
,isnull(c.SQR,'') as SQR
,isnull(c.SQSJ,'') as SQSJ
,d.user_name as PZR
,isnull(a.handle_suggestion,'') as PZYJ
,isnull(a.handle_date,'') as PZSJ
,'0' as JHZT
,a.db_created_date as LRSJ
,NULL as REMARK1
,'N' as REMARK2
,NULL as REMARK3
,1 --新增
,0
from inserted as a
, SZUM_ZHZF_IntergratedPlatform.dbo.ty_wf_ex_local_activity_instance as b
, (select t1.acitivity_instance_id, t2.handle_content as SQYY, t4.user_name as SQR, t2.execute_date as SQSJ, '案件执行' as SQHJ
from inserted t1
,SZUM_ZHZF_IntergratedPlatform.dbo.t_case_execute_info t2
,SZUM_ZHZF_IntergratedPlatform.dbo.t_sys_extention_user t4
where t1.case_id = t2.case_id
and t2.db_created_id = t4.pmi_user_id) c
, SZUM_ZHZF_IntergratedPlatform.dbo.t_sys_extention_user d
, SZUM_ZHZF_IntergratedPlatform.dbo.t_case_basic_info e
where a.acitivity_instance_id = b.id
and a.acitivity_instance_id = c.acitivity_instance_id
and a.db_created_id = d.pmi_user_id
and a.case_id = e.case_id
and e.case_code is not null
and e.case_code <> ''
end
GO这是触发器
INSERT @t SELECT 1 WHERE 1=0
SELECT * FROM @t
/*
I
-----------(0 行受影响)
*/
如果條件不滿足,也就不會插入任何內容。跟用IF判斷結果是一樣的。
declare @executer_time datetime
set @executer_time = [dbo].[p_fetch_executer_datetime]() delete from SZUM_ZHZF_ExChange.dbo.t_case_stage_suggestion
where id in
(
select id from SZUM_ZHZF_IntergratedPlatform.dbo.t_case_stage_suggestion
where db_last_updated_date>@executer_time
)
insert into SZUM_ZHZF_ExChange.dbo.t_case_stage_suggestion
select * from SZUM_ZHZF_IntergratedPlatform.dbo.t_case_stage_suggestion
where db_last_updated_date>@executer_time
COMMIT TRANSACTION;
对于这个,我会先删除前段时间,
请问是否存在问题?
htl258太有才了.
OK了.按你的方法OK了.