34,591
社区成员
发帖
与我相关
我的任务
分享使用insert into 表数据,但不执行触发器
使用insert into 表数据,但不执行触发器?
当使用insert into 同步两个表数据的时候.不会执行触发器?
但只写插入一条数据.就能触发啊?
这是为什么呢?
难道插入的速度太快,不执行触发器的原因?
我的触发器里面 会多关键多个表取数据的.
当使用insert into 同步两个表数据的时候.不会执行触发器?
但只写插入一条数据.就能触发啊?
这是为什么呢?
难道插入的速度太快,不执行触发器的原因?
我的触发器里面 会多关键多个表取数据的.
...全文
请发表友善的回复…
发表回复
rjzou2006 2009-09-11
- 打赏
- 举报
[Quote=引用 8 楼 htl258 的回复:]
SQL codeDECLARE@tTABLE(IINT)INSERT@tSELECT1WHERE1=0SELECT*FROM@t/*
I
-----------
(0 行受影响)*/
如果條件不滿足，也就不會插入任何內容。跟用IF判斷結果是一樣的。
[/Quote]
这样ＯＫＯＫＯＫＯＫＯＫＯＫＯＫＯＫＯＫＯＫＯＫＯＫＯＫＯＫＯＫＯＫＯＫＯＫＯＫＯＫＯＫＯＫＯＫＯＫＯＫＯＫＯＫ了
SQL codeDECLARE@tTABLE(IINT)INSERT@tSELECT1WHERE1=0SELECT*FROM@t/*
I
-----------
(0 行受影响)*/
如果條件不滿足，也就不會插入任何內容。跟用IF判斷結果是一樣的。
[/Quote]
这样ＯＫＯＫＯＫＯＫＯＫＯＫＯＫＯＫＯＫＯＫＯＫＯＫＯＫＯＫＯＫＯＫＯＫＯＫＯＫＯＫＯＫＯＫＯＫＯＫＯＫＯＫＯＫ了
rjzou2006 2009-09-11
- 打赏
- 举报
htl258
太有才了．
ＯＫ了．
按你的方法ＯＫ了．
htl258_Tony 2009-09-11
- 打赏
- 举报
[Quote=引用 9 楼 rjzou2006 的回复:]
SQL codeBEGINTRANSACTION;declare@executer_timedatetimeset@executer_time=[dbo].[p_fetch_executer_datetime]()deletefrom SZUM_ZHZF_ExChange.dbo.t_case_stage_suggestionwhere idin
(select idfrom SZUM_ZHZF_IntergratedPlatform.dbo.t_case_stage_suggestionwhere db_last_updated_date>@executer_time
)insertinto SZUM_ZHZF_ExChange.dbo.t_case_stage_suggestionselect*from SZUM_ZHZF_IntergratedPlatform.dbo.t_case_stage_suggestionwhere db_last_updated_date>@executer_timeCOMMITTRANSACTION;
对于这个,我会先删除前段时间，
请问是否存在问题？
[/Quote]
執行語句的順序從上到下，有沒有影響你應該清楚。
SQL codeBEGINTRANSACTION;declare@executer_timedatetimeset@executer_time=[dbo].[p_fetch_executer_datetime]()deletefrom SZUM_ZHZF_ExChange.dbo.t_case_stage_suggestionwhere idin
(select idfrom SZUM_ZHZF_IntergratedPlatform.dbo.t_case_stage_suggestionwhere db_last_updated_date>@executer_time
)insertinto SZUM_ZHZF_ExChange.dbo.t_case_stage_suggestionselect*from SZUM_ZHZF_IntergratedPlatform.dbo.t_case_stage_suggestionwhere db_last_updated_date>@executer_timeCOMMITTRANSACTION;
对于这个,我会先删除前段时间，
请问是否存在问题？
[/Quote]
執行語句的順序從上到下，有沒有影響你應該清楚。
xuejiecn 2009-09-11
- 打赏
- 举报
还真复杂!你的整修触发器都在判断里，先查看下@activity_id 的值具体是什么，不是没有执行，而是没有按你的要求执行。
terry20080416 2009-09-11
- 打赏
- 举报
帮顶
rjzou2006 2009-09-11
- 打赏
- 举报
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;
对于这个,我会先删除前段时间，
请问是否存在问题？
htl258_Tony 2009-09-11
- 打赏
- 举报
DECLARE @t TABLE(I INT)
INSERT @t SELECT 1 WHERE 1=0
SELECT * FROM @t
/*
I
-----------
(0 行受影响)
*/如果條件不滿足，也就不會插入任何內容。跟用IF判斷結果是一樣的。
htl258_Tony 2009-09-11
- 打赏
- 举报
[Quote=引用 6 楼 rjzou2006 的回复:]
也就是写多条insert 语句吗?
[/Quote]
是的。如果條件不滿足，也就不會插入。
也就是写多条insert 语句吗?
[/Quote]
是的。如果條件不滿足，也就不會插入。
rjzou2006 2009-09-11
- 打赏
- 举报
也就是写多条insert 语句吗?
htl258_Tony 2009-09-11
- 打赏
- 举报
把判斷的變量的條件寫在WHERE之後，不需要變量。
rjzou2006 2009-09-11
- 打赏
- 举报
/*************************************环节意见存储过程************************************/
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这是触发器
htl258_Tony 2009-09-11
- 打赏
- 举报
這是觸發器觸發機制的問題，並不是插得太快的問題。
htl258_Tony 2009-09-11
- 打赏
- 举报
代碼貼出來看看。
翼帆 2009-09-11
- 打赏
- 举报
帮顶!
