使用insert into 表数据,但不执行触发器
来源：互联网 发布：mac魔兽世界插件 编辑：程序博客网 时间：2024/05/21 07:42
使用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判斷結果是一樣的。
- 使用insert into 表数据,但不执行触发器
- insert into select 插入不进去数据
- 执行数据导入的Bulk Insert与Insert Into
- Hibernate: insert into project 成功但数据库表中没有数据
- MYSQL INSERT INTO SELECT 不插入重复数据
- MYSQL INSERT INTO SELECT 不插入重复数据
- 求救！用Access数据库插入数据，INSERT INTO语句总是出现错误，但同样的语句格式，用SQL Server 2000数据库，不出现错误！
- sql server insert into 表数据转移
- insert into插入数据
- 表数据的copy，insert into select/select into from
- 触发器例子,insert into all的用法
- 插入数据sql使用“insert into set”形式的理解
- 插入数据 insert into / select
- 将Sqlserver表数据导出成insert into语句
- Insert Into ...Select 加 Update From 同步表数据
- 将Sqlserver表数据导出成insert into语句 .
- 根据MySQL表数据导出INSERT INTO语句的方法
- Oracle 执行Insert into 语句失效解决方法
- python 错误的原因 token Error: EOF in multi-line statement
- 分类
- 悟人生
- ORACLE备份策略(ORACLE BACKUP STRATEGY)
- C#hashtable使用说明【总结性】
- 使用insert into 表数据,但不执行触发器
- 修改VMWARE BIOS的三种方法
- 保存密码
- C++ primer 习题练习：9.13
- 成长
- [转帖]了解AOP
- Oracle UTL_RAW
- sse 指令优化叉乘
- 【转】Fedora 9 Samba 服务器的构建
