/******创建实时表******/
CREATE TABLE [dbo].[RTable](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[tagname] [varchar](50) NOT NULL,
	[tagvalue] [real] NOT NULL,
	[tagtype] [varchar](50) NOT NULL,
	[digcount] [int] NOT NULL,
	[unit] [varchar](50) NOT NULL,
	[description] [varchar](250) NOT NULL,
	[time] [datetime] NOT NULL,
	[rangemax] [real] NOT NULL,
	[rangemin] [real] NOT NULL,
	[status] [int] NOT NULL,
        [hist] [int],
 CONSTRAINT [PK_RTable] PRIMARY KEY CLUSTERED 
(
	[id] 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

ALTER TABLE [dbo].[RTable] ADD  CONSTRAINT [DF_RTable_tagname]  DEFAULT ('') FOR [tagname]
GO

ALTER TABLE [dbo].[RTable] ADD  CONSTRAINT [DF_RTable_tagvalue]  DEFAULT ((0)) FOR [tagvalue]
GO

ALTER TABLE [dbo].[RTable] ADD  CONSTRAINT [DF_RTable_tagtype]  DEFAULT ('Single') FOR [tagtype]
GO

ALTER TABLE [dbo].[RTable] ADD  CONSTRAINT [DF_RTable_digcount]  DEFAULT ((0)) FOR [digcount]
GO

ALTER TABLE [dbo].[RTable] ADD  CONSTRAINT [DF_RTable_unit]  DEFAULT ('') FOR [unit]
GO

ALTER TABLE [dbo].[RTable] ADD  CONSTRAINT [DF_RTable_description]  DEFAULT ('') FOR [description]
GO

ALTER TABLE [dbo].[RTable] ADD  CONSTRAINT [DF_RTable_time]  DEFAULT (getdate()) FOR [time]
GO

ALTER TABLE [dbo].[RTable] ADD  CONSTRAINT [DF_RTable_rangemax]  DEFAULT ((100)) FOR [rangemax]
GO

ALTER TABLE [dbo].[RTable] ADD  CONSTRAINT [DF_RTable_rangemin]  DEFAULT ((0)) FOR [rangemin]
GO

ALTER TABLE [dbo].[RTable] ADD  CONSTRAINT [DF_RTable_status]  DEFAULT ((0)) FOR [status]
GO


/******创建历史表******/
CREATE TABLE [dbo].[HisTable](
	[id] [int] NOT NULL,
	[value] [real] NOT NULL,
	[time] [datetime] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[HisTable] ADD  CONSTRAINT [DF_HisTable_time]  DEFAULT (getdate()) FOR [time]
GO

/******创建历史表-Index******/
CREATE NONCLUSTERED INDEX [IX_HisTable] ON [dbo].[HisTable]
(
	[id] ASC,
	[time] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


/******创建Update触发器******/
IF OBJECT_ID (N'TRIGER_RTTable_Update', N'tr') IS NOT NULL
    DROP TRIGGER TRIGER_RTTable_Update;
GO

CREATE TRIGGER [dbo].[TRIGER_RTTable_Update]
ON [dbo].[RTable]
FOR UPDATE
AS  
   if UPDATE(tagvalue) 
    begin
    declare @id int
	declare @value float
	declare @time datetime
	declare @hist int
	select @id=id,@value=tagvalue,@time=time,@hist=hist from  inserted
	if (@hist=1)
       INSERT INTO [dbo].[HisTable]([ID],[VALUE],[Time]) VALUES(@id,@value,@time)
	end
GO