27,579
社区成员
发帖
与我相关
我的任务
分享
CREATE TRIGGER NUMBER_UPDATE ON T_APP_INFO
FOR INSERT,UPDATE
AS
BEGIN
UPDATE T_APP_NUMBER
SET APPEDNUMBER=ISNULL(APPEDNUMBER,0)+B.QTY,
REMAINNUMBER=ISNULL(REMAINNUMBER,0)-B.QYT
FROM T_APP_NUMBER A
JOIN
(SELECT ExcuteHospital,StudyType,SUM(CASE WHEN AppStatus=0 THEN 1 ELSE -1 END) AS QTY
FROM INSERTED
GROUP BY ExcuteHospital,StudyType) AS B ON A.STUDYHOSPITAL=B.ExcuteHospital AND A.StudyType=B.StudyType
END
CREATE TRIGGER NUMBER_UPDATE ON T_APP_INFO
FOR INSERT,UPDATE
AS
BEGIN
UPDATE T_APP_NUMBER
SET APPEDNUMBER=ISNULL(APPEDNUMBER,0)+B.QTY,
REMAINNUMBER=ISNULL(REMAINNUMBER,0)-B.QYT
FROM T_APP_NUMBER A
JOIN
(SELECT ExcuteHospital,SUM(CASE WHEN AppStatus=0 THEN 1 ELSE -1 END) AS QTY
FROM INSERTED
GROUP BY ExcuteHospital) AS B ON A.STUDYHOSPITAL=B.ExcuteHospital
END
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[T_APP_INFO](
[InfoIdentity] [bigint] NOT NULL,
[PatientType] [int] NULL,
[HisCode] [nvarchar](32) NOT NULL,
[GetTime] [datetime] NULL,
[PatientName] [nvarchar](32) NULL,
[Sex] [nvarchar](16) NULL,
[Age] [int] NULL,
[AgeUnit] [nvarchar](16) NULL,
[PhoneNumber] [nvarchar](32) NULL,
[PatientId] [nvarchar](32) NULL,
[BedNo] [nvarchar](16) NULL,
[PatientRecordNo] [nvarchar](32) NULL,
[ChinaIdCard] [nvarchar](32) NULL,
[Modality] [nvarchar](32) NULL,
[Address] [nvarchar](128) NULL,
[OrderId] [nvarchar](32) NOT NULL,
[StudyItem] [nvarchar](64) NULL,
[HisItem] [nvarchar](64) NULL,
[StudyType] [nvarchar](64) NULL,
[AppDoctor] [nvarchar](32) NULL,
[AppDoctorId] [nvarchar](32) NULL,
[RegisterTime] [datetime] NULL,
[AppStatus] [tinyint] NULL,
[ItemFee] [nvarchar](16) NULL,
[FeeStatus] [tinyint] NULL,
[ExcuteHospital] [nvarchar](64) NULL,
[APPHospital] [nvarchar](64) NULL,
[AppTime] [datetime] NULL,
[OtherInfor] [varchar](300) NULL,
[AppTimeSeg] [varchar](64) NULL,
CONSTRAINT [PK_T_APP_INFO] PRIMARY KEY CLUSTERED
(
[InfoIdentity] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
[HisCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
[OrderId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[T_APP_NUMBER] (
[NumberIdentity] bigint NOT NULL,
[StudyHospital] nvarchar(64) COLLATE Chinese_PRC_CI_AS NULL,
[BegTime] time(7) NULL,
[EndTime] time(7) NULL,
[TotalNumber] int NULL,
[AppedNumber] int NULL,
[RemainNumber] int NULL,
[StudyType] nvarchar(64) COLLATE Chinese_PRC_CI_AS NULL,
[StudyItem] nvarchar(64) COLLATE Chinese_PRC_CI_AS NULL,
[AppDate] date NULL,
[NumberComment] varchar(300) COLLATE Chinese_PRC_CI_AS NULL,
[Deleted] tinyint NULL,
CONSTRAINT [PK_T_APP_NUMBER] PRIMARY KEY CLUSTERED ([NumberIdentity])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
)