首页新闻找找看学习计划

SQL Server 触发器

0
悬赏园豆:20 [已解决问题] 解决于 2015-08-13 17:13

USE [ebiddingkazak]
GO
/****** Object:  Trigger [dbo].[tri_order_State]    Script Date: 08/10/2015 10:29:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Trigger [dbo].[tri_order_State]
  on [dbo].[ebidding_order]
  for insert,update
  as
  declare @oid int
  declare @customer int
  declare @customerName nvarchar(120)
  declare @handler int
  declare @business int
  declare @type smallint
  declare @state smallint
  declare @progress smallint
  declare @stime datetime
  declare @code varchar(30)
  declare @name nvarchar(20)
  declare @intro nvarchar(max)
  declare @notes nvarchar(max)
  declare @files nvarchar(max)
  declare @amount float
  declare @budget float
  declare @branch int
  declare @projectNo varchar(40)
  declare @projectOwner int
  declare @productLines varchar(100)
  declare @productLineOther nvarchar(100)
  declare @contact nvarchar(60)
  declare @startDate datetime
  declare @dueDate datetime
  declare @creator int
  declare @created datetime
  declare @sended datetime
  declare @finished datetime
  declare @remark nvarchar(max)
  declare @productionTeam nvarchar(max)
  declare @mode int
  declare @tax float
  declare @vendorGroup int
  declare @terminalReasons varchar(50)
  declare @approving datetime
  declare @vendorInviter int
  declare @biddingApplicant int
  declare @biddingApproval int
  declare @country int
  declare @getNewTime datetime
 
  --获取order 表中的数据
    if UPDATE([state])
    begin
       begin
          select @oid=oid,@customer=customer,@customerName=customerName,@handler=handler,
          @business=business,@type=[type],@state=[state],@progress=progress,@stime=stime,
          @code=code,@name=name,@intro=intro,@notes=notes,@files=files,@amount=amount,
          @budget=budget,@branch=branch,@projectNo=projectNo,@projectOwner=projectOwner,
          @productLines=productLines,@productLineOther=productLineOther,@contact=contact,
          @startDate=startDate,@dueDate=dueDate,@creator=creator,@created=created,
          @sended=sended,@finished=finished,@remark=remark,@productionTeam=productionTeam,
          @mode=mode,@tax=tax,@vendorGroup=vendorGroup,@terminalReasons=terminalReasons,
          @approving=approving,@vendorInviter=vendorInviter,@biddingApplicant=biddingApplicant,
          @biddingApproval=biddingApproval,@country=country,@getNewTime=dateadd(HOUR,0,CURRENT_TIMESTAMP) from inserted;
       
          insert into dbo.ebidding_order_history
          (oid, customer, customerName, handler, business, type, state, progress, stime, code, name, intro, notes, files, amount, budget, branch, projectNo, projectOwner, productLines, productLineOther, contact, startDate, dueDate, creator, created, sended, finished, remark, productionTeam, mode, tax, vendorGroup, terminalReasons, approving, vendorInviter, biddingApplicant, biddingApproval, country, getNewTime)
          values
          (@oid,@customer,@customerName,@handler,@business,@type,@state,@progress,@stime,@code,@name,@intro,@notes,@files,@amount,@budget,@branch,@projectNo,@projectOwner,@productLines,@productLineOther,@contact,@startDate,@dueDate,@creator,@created,@sended,@finished,@remark,@productionTeam,@mode,@tax,@vendorGroup,@terminalReasons,@approving,@vendorInviter,@biddingApplicant,@biddingApproval,@country,@getNewTime)
      end  
  end
 
求大神,看一下有什么问题没?

在我这不知道为什么会有空的记录添加到dbo.ebidding_order_history表中,求大神指教!


 
 

IT小伙儿的主页 IT小伙儿 | 初学一级 | 园豆:11
提问于:2015-08-10 10:46
< >
分享
最佳答案
0

那就是你的参数没有传进来咯。insert into dbo.ebidding_order_history   看看这个。你应该加点判断。判断这些必要的值@oid,@customer,@customerName是不是Null.再执行这个insert 。

收获园豆:10
贫民窟大侠 | 老鸟四级 |园豆:4270 | 2015-08-10 11:45

恩恩 是啊 应该在插入数据之前做一个为空判断就好了 谢谢提示

IT小伙儿 | 园豆:11 (初学一级) | 2015-08-13 17:14
其他回答(2)
0

楼上方案应该可以解决  但是我觉得还是应该调查一下为NULL的原因。。

收获园豆:5
小白菜T | 园豆:564 (小虾三级) | 2015-08-10 16:21
0

如果你表里更新的时候记录本来就是空,可以说得过去。

还有不建议这样写一条一条的

你可以直接 insert into xxx select xxx from inserted 即解决了多条插入的问题,写法上写简化很多。

收获园豆:5
gw2010 | 园豆:1394 (小虾三级) | 2015-08-11 16:06
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册