2 wangzhanling WANGZHANLING 于 2014.01.22 15:59 提问

新手请求帮助SQL触发器

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER TRIGGER [tr_yskq1] ON [dbo].[yskq1]
FOR INSERT
AS
declare @bh varchar(10),
@date1 datetime,
@time1 varchar(30),
@timeA varchar(30),
@timeB varchar(30),
@bmmc varchar(50),
@jh bigint,
@xm varchar(30),
@bc varchar(50),
@count smallint,
@time2 datetime,
@timeC datetime,
@time3 datetime,
@time4 datetime,
@rqsj datetime,
@aa varchar(30),
@kh varchar(20),
@xs int,
@fz int
select @bh=bh,@date1=isnull(rq,'1900-01-01'),@timeA=sj,@xm=xm,@bc=bc from inserted

  SET @xs=CAST (SUBSTRING(@timeA,1,2)AS INT)
  SET @fz=CAST (SUBSTRING(@timeA,3,2)AS INT)

IF @xs0
begin
set @xs=7
if @fz set @fz=@fz+15
end
IF @xs18
begin
set @xs=19
if @fz>30
set @fz=@fz-30
end

set @time1=convert(varchar,@xs)+ convert(varchar,@fz)+'00'

--赋初值
set @count = 0
--字符型转时间型
set @time2 = cast(@time1 as datetime)
--时间推后5分钟
set @time3 = DATEADD(minute,5,@time2)
set @time4 = DATEADD(minute,-5,@time2)

select @count=count(*) from yskq where rq = @date1 and sj <= convert(varchar(30),@time3,8) and sj >= convert(varchar(30),@time4,8) and bh=@bh
set @aa = convert(varchar(30),@time2,8)
--insert into t_test values (@time4,@time3,@time2,@time1,@count,@aa)
select @bmmc=bm from employee where bh=@bh
select @kh=mkkh from employee where bh=@bh
if @count <= 0
begin
--如果沒有該記錄則執行插入
insert into dbo.yskq(bh,rq,sj,xm,bmmc,bc,kh) values (@bh,@date1,@time1,@xm,@bmmc,@bc,@kh)
end
这个触发器其中今个判断语句是我写的。现在检出不出问题,大概需求就是当表1(yskq1)中增加一条数据时像表2(yskq)中插入一条,在插入的时候看看时间是不是在7:15--19:30之间,如果是直接插入,如果早啦把时间调到7:15--8:00,如果晚就好时间调到19:00--19:30

Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!