使用insert into 表数据,但不执行触发器

使用insert into 表数据,但不执行触发器?

当使用insert into 同步两个表数据的时候.不会执行触发器?

但只写插入一条数据.就能触发啊?

这是为什么呢?


难道插入的速度太快,不执行触发器的原因?

我的触发器里面 会多关键多个表取数据的.

-------------------------------------------------------------------------------------------------

/*************************************环节意见存储过程************************************/
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]
GO

CREATE 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
as

declare @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_id

if @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

DECLARE @t TABLE(I INT)
INSERT @t SELECT 1 WHERE 1=0
SELECT * FROM @t
/*
I
-----------

(0 行受影响)
*/

如果條件不滿足，也就不會插入任何內容。跟用IF判斷結果是一樣的。