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条数据'

 

-----------------------------------------------