使用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判斷結果是一樣的。