zjcxc(邹建)大虾进来帮忙一个触发器，昨天已经经过你的指导了

xjzjgydx 2004-04-20 12:45:52

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_employee_department]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[employee] DROP CONSTRAINT FK_employee_department
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[getdayout]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[getdayout]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[card]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[card]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dayout]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[dayout]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[department]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[department]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[employee]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[employee]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[meal]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[meal]
GO

CREATE TABLE [dbo].[card] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[cardId] [int] NOT NULL ,
[useCardData] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[dayout] (
[employeeId] [int] NOT NULL ,
[employeeName] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[departmentId] [int] NOT NULL ,
[departmentName] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[mealDate] [datetime] NOT NULL ,
[useCardData] [char] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[totalFee] [money] NOT NULL ,
[totalNum] [int] NOT NULL ,
[breakfastNum] [int] NOT NULL ,
[lunchNum] [int] NOT NULL ,
[supperNum] [int] NOT NULL ,
[cardid] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[department] (
[departmentId] [int] NOT NULL ,
[departmentName] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[employee] (
[employeeId] [int] NOT NULL ,
[emplyeeName] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[departmentId] [int] NOT NULL ,
[cardId] [int] NOT NULL ,
[position] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[age] [int] NULL ,
[sex] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[IDCard] [char] (18) COLLATE Chinese_PRC_CI_AS NULL ,
[educationReceived] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[meal] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[mealName] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[StartTime] [datetime] NOT NULL ,
[EndTime] [datetime] NOT NULL ,
[fee] [money] NOT NULL
) ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
以上是我键表的语句

下面是对card的触发器

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER trigger 插入dayout
on card
for insert
as
begin
declare @cardid int,--卡号
@usedate datetime,
@usetime datetime,
@employeeid int,
@emloyeename varchar(10),
@departmentid int,
@departmentname varchar(10),
@totalfee float,
@totalnum int,
@breakfastnum int,
@lunchnum int,
@suppernum int,

@早餐开始时间 datetime,
@早餐结束时间 datetime,
@早餐价格 money,

@午餐开始时间 datetime,
@午餐结束时间 datetime,
@午餐价格 money,

@晚餐开始时间 datetime,
@晚餐结束时间 datetime,
@晚餐价格 money
set @totalfee=0
set @totalnum=0
set @breakfastnum=0
set @lunchnum=0
set @suppernum=0
select @早餐结束时间=convert(varchar(8),endtime,108),@早餐开始时间=convert(varchar(8),starttime,108),@早餐价格=fee from meal where mealname='早餐'
select @午餐结束时间=convert(varchar(8),endtime,108),@午餐开始时间=convert(varchar(8),starttime,108),@午餐价格=fee from meal where mealname='午餐'
select @晚餐结束时间=convert(varchar(8),endtime,108),@晚餐开始时间=convert(varchar(8),starttime,108),@晚餐价格=fee from meal where mealname='晚餐'

select @cardid=cardid,
@usedate=convert(varchar(8),usecarddata,110),
@usetime=convert(varchar(8),usecarddata,108)
from inserted
select @employeeid=employeeid,@emloyeename=emplyeename,@departmentid=employee.departmentid,@departmentname=departmentname from employee join department on employee.departmentid=department.departmentid where employee.cardid=@cardid
--判断此前有没有刷卡
if not exists(select * from dayout where cardid=@cardid and convert(varchar(10),mealdate,110)=@usedate)
--如没有则新插入记录
begin
if(@usetime>@早餐开始时间 and @usetime<@早餐结束时间)
begin
set @totalfee=@早餐价格
set @totalnum=1
set @breakfastnum=1

insert into dayout
values(@employeeid,@emloyeename,@departmentid,@departmentname,@usedate,@usetime,@totalfee,@totalnum,@breakfastnum,@lunchnum,@suppernum,@cardid)
end
if(@usetime>@午餐开始时间 and @usetime<@午餐结束时间)
begin
set @totalfee=@午餐价格
set @totalnum=1
set @lunchnum=1

insert into dayout
values(@employeeid,@emloyeename,@departmentid,@departmentname,@usedate,@usetime,@totalfee,@totalnum,@breakfastnum,@lunchnum,@suppernum,@cardid)
end
if(@usetime>@晚餐开始时间 and @usetime<@晚餐结束时间)
begin
set @totalfee=@晚餐价格
set @totalnum=1
set @suppernum=1

insert into dayout
values(@employeeid,@emloyeename,@departmentid,@departmentname,@usedate,@usetime,@totalfee,@totalnum,@breakfastnum,@lunchnum,@suppernum,@cardid)
end
end
else
begin

if(@usetime>@早餐开始时间 and @usetime<@早餐结束时间)
begin

update dayout set usecarddata=usecarddata+','+convert(varchar(8),@usetime,108),
totalnum=totalnum+1,breakfastnum=breakfastnum+1,totalfee=totalfee+@早餐价格 where cardid=@cardid and convert(varchar(10),mealdate,110)=@usedate

end
end
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

对昨天的问题我写了触发器，来实现每次插入card（就是刷卡纪录）这张表时就去判断如果日报表没有的话就直接插入日报表，如果同一张卡同一天有数据的话，就去跟心相应的字段，跟心我只写了早餐的，现在的错误是从datetime 转换到datetime 值越界，
我在card里插入2004-1-1 7：00
那么在（dayout）的usecarddate的数据显示应该是7：00现在是01 1 1900 7：00am，我用108怎么会有am出现，
如果我在card里在添加一个2004-1-1 7：12，的话按照我的触发器，dayout里的usecarddate里应该显示7：00，7：12 但是现在是01 1 1900 ，01 1 1900
帮忙，谢谢

...全文