SQL server用到的SQL语句备份下
这是触发器用于关联条件的
-------------1--------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER trigger [Collection_GasInsert]
on [dbo].[Collection_Gas]
FOR INSERT
AS
declare @GasName varchar(10)
set @GasName=''
declare @GasPpb decimal(18, 0)
set @GasPpb=0
declare @AlarmName varchar(10)
set @AlarmName=''
declare @Condition decimal(18,0)
set @Condition=0
begin
select @GasName=GasName,@GasPpb=GasPpb from Collection_Gas where ID in (select Top 1 ID from Collection_Gas order by CollectTime desc)
select top 1 @AlarmName=AlarmName,@Condition=Condition from AlarmInfoSet
if (@GasPpb > @Condition)
insert into AlarmInfoList(AlarmName,GasName,AlarmContext) VALUES (@GasPpb,@GasName,@GasName);
print 'Collection_Gas 下 AlarmInfoList表更新了1条数据'
end
----------触发器
CREATE TRIGGER BaseInfoUpdate
ON BaseInfo
for update
AS
print 'BaseInfoUpdate表更新了'
---
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER TRIGGER [BaseInfoUpdate]
ON [dbo].[BaseInfo]
for update
AS
print 'BaseInfoUpdate表更新了1条数据'
------------1结尾------
插入语句 简单备份下 -----2
use GHGD;
insert into Collection_Gas (GasName,GasPpb,GasMaxPpb,GasR2,CollectAddress,CollectTime,Operator,Isalarm)
values('NA1','141','19','19','19','1989/9/9','19','0');
SELECT * FROM Collection_Gas;
SELECT * FROM AlarmInfoList;
update BaseInfo set Operatorer = '陈玲玲1',Address='香山',CollectionTime='15' where id = 1;
use ghgd;
insert into Collection_Gas (GasName,GasPpb,GasMaxPpb,GasR2,CollectAddress,CollectTime,Operator,Isalarm)
values('NA1','11','19','19','19','2909/9/09 00:01:00','19','0');
-----------2结尾----
更新的存储过程------------3
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [dbo].[UpdateCheckBoxFrmSetControl_AndnalyseINFO]
(@ID int,@Unit int,@Unitratio int,@Algorithm int,@Lenth int,@SetInitialData bit,@Setdensity bit,@Transit bit)
as
begin
update Andnalyse set Unit=@Unit,Unitratio=@Unitratio,Algorithm=@Algorithm,
Lenth=@Lenth,SetInitialData=@SetInitialData,Setdensity=@Setdensity,Transit=@Transit
where ID=@ID
end
-----
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
create procedure [dbo].[FrmSetting_BaseInfo_UpdateInfo]
(@caoZuoZhe varchar(50),@jianCeDiDian varchar(50),@caiJiShiJian varchar(50),@idfst int)
as
begin
update BaseInfo set Operatorer=@caoZuoZhe,Address=@jianCeDiDian,CollectionTime=@caiJiShiJian where ID=@idfst
end
---------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [dbo].[Get_Frmsetting_DicInfo]
(@str varchar(50))
as
begin
select Name from Dic where Type = @str
end
--------------------------------3 结束------------------
0------------4 重点-----------------查询出气体名称 、时间分组的 再通过关联关系 查询出所有数据------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [dbo].[FrmCount_Collection_Gas_GetInfoDescTime]
as
begin
select distinct a.GasName, a.CollectTime,a.GasPpb,a.GasMaxPpb,a.GasR2,a.Operator from Collection_Gas a,
(select distinct GasName,max(CollectTime) CollectTime from Collection_Gas group by GasName) b where a. GasName = b. GasName and a.CollectTime = b.CollectTime
end
----------------------4 结束-----------------------
-----------------------------5---------------这个功能代码是：创建触发器 通过触发器的关联条件插入到另一行
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER trigger [AlarmInfoListInsert]
on [dbo].[AlarmInfoList]
FOR INSERT
AS
declare @GasName varchar(10)
set @GasName=''
declare @GasPpb decimal(18, 0)
set @GasPpb=0
declare @AlarmName varchar(10)
set @AlarmName=''
declare @Condition decimal(18,0)
set @Condition=0
begin
select @GasName=GasName,@GasPpb=GasPpb from Collection_Gas where ID in (select Top 1 ID from Collection_Gas order by CollectTime desc)
select top 1 @AlarmName=AlarmName,@Condition=Condition from AlarmInfoSet
--if(@GasPpb>@Condition)
insert into AlarmInfoList(AlarmName,GasName,AlarmContext) VALUES (@AlarmName,@GasName,@GasName);
end
print 'AlarmInfoList表更新了1条数据'
-----------------------------------------------
