在表建立触发器后，某些数据不能插入，触发器是update过后触发，求助！！！

[qq_35751625]

在线等
建立trigger的表是存储员工请假的表单，表单列有： 等字段，触发器的目的是当更新 finishedflag = 1 时 ，计算出请假的类型和时间插入另一张表refer_data对应字段中。但是当触发器运行后，请假的表单有些用户不能建立正常的数据，有些又能！正常和异常对比：，下面是触发器的代码，可能定义和逻辑有点不精简，海涵一下：

CREATE TRIGGER [dbo].[tr_formmain_0117_update] ON [dbo].[formmain_0117]
WITH EXEC AS CALLER
AFTER UPDATE
AS
declare @state int,@channel nvarchar(255);
	declare @var1 varchar(25),@var2 varchar;
	declare @nvar1 nvarchar(255);
	declare @data2 numeric(20,5),@data3 numeric(20,5), @data_other numeric(20,5),@data_personal numeric(20,5);
	declare @start_time datetime, @end_time datetime,@start_time_h datetime,@end_time_h datetime;
	declare @start_time_hs int,@end_time_hs int,@time_h_poor int ,@time_d_poor int;
	declare @time_all numeric(20,1);
BEGIN
	select @state=finishedflag ,@channel=field0004,@var1 = start_member_id,@start_time = field0005,@end_time = field0006 from inserted;
	select @nvar1 = NAME from ORG_MEMBER where ID = @var1;
	select @var2 = ID from refer_user where cname = @nvar1;
	select @data3 = isnull(year_leave,0),@data_other=isnull(other_leave,0),@data_personal=isnull(personal_leave,0) from refer_data where uid=@var2;
	
	set @start_time_h = right(@start_time,8);
	set @start_time_hs = DATEDIFF(s, '00:00:00',@start_time_h);
	if(@start_time_hs<32400)
		BEGIN
			set @start_time_hs = 32400;
		END
	else if (@start_time_hs>43200 and @start_time_hs<46800)
		BEGIN
			set @start_time_hs = 46800;
		END

	set @end_time_h = right(@end_time,8);
	set @end_time_hs = DATEDIFF(s,'00:00:00',@end_time_h);
	if(@end_time_hs>43200 and @end_time_hs<46800)
	BEGIN
		set @end_time_hs = 43200;
	END
	else if(@end_time_hs>63000)
	BEGIN
		set @end_time_hs = 63000;
	END

	set @time_h_poor = 0;
	if(@start_time_hs<43200 and @end_time_hs<=43200)
	BEGIN
		set @time_h_poor = @end_time_hs-@start_time_hs;
	END
	else if(@start_time_hs>=43200 and @end_time_hs>46800)
	BEGIN
		set @time_h_poor = @end_time_hs-@start_time_hs;
	END
	else if(@start_time_hs<=43200 and @end_time_hs>=43200)
	BEGIN
		set @time_h_poor = 43200-@start_time_hs+@end_time_hs-46800;
	END
  else if(@start_time_hs>=43200 and @end_time_hs<=43200)
  BEGIN
    set @time_h_poor = 63000-@start_time_hs+43200-@end_time_hs-27000;
  END
	set @time_d_poor = DATEDIFF(d,@start_time,@end_time);

	set @time_all = round(@time_h_poor/3600.00,1) + @time_d_poor*7.5;
if(@state=1)
	BEGIN
		if(@channel='3914859601377381015')
			BEGIN
				update [dbo].[refer_data] set year_leave=@data3-@time_all where uid=@var2;
			END
    else if(@channel='-1135316255604717531')
      BEGIN
        update [dbo].[refer_data] set personal_leave=@data_personal+@time_all where uid=@var2;
      END
		ELSE
			BEGIN
				update [dbo].[refer_data] set other_leave=@data_other-@time_all where uid=@var2;
			END
	END
END

[卖水果的net]

相关的表结构，也列出来，要 create table 形式的；

[qq_35751625]

这是存储计算后的表结构

[卖水果的net]

发 create 语句吧，另让帮助你的人再自己写创建表的语句了。

[qq_35751625]

这是相关用户关联表

[二月十六]

不能插入的估计是触发器逻辑判断有问题，楼主可以对比一下能插入的和不能插入的数据的区别。

[qq_35751625]

表示别人建的，我只负责写trigger, 如果关掉TRIGGER, 就不会出现任何用户提交数据异常！

[qq_35751625]

这是开启trigger 和关闭trigger 的两条正常和异常的数据对比！