组态软件，使用SqlServer做报表的一种方式
关注老董，学习更多电气自动化知识！ 组态软件要往数据库里存数据，各个品牌的实现方法不同，但是其实实现的思路基本相同，而且所有组态软件都有这个功能！我就不讲怎么存的了，根据组态软件不同，各位自己去搜索如：“WINCC存数据到SqlServer”,”InTouch存数
1、创建视图 ：
CREATE VIEW [schema].[view_name] //[schema].[view_name] 视图的名称
–WITH ENCRYPTION, SCHEMABINDING, VIEW_METADATA
AS
///视图需要执行的查询语句
— WITH CHECK OPTION
GO
2、创建索引
CREATE NONCLUSTERED INDEX index_name //NONCLUSTERED 可选参数 UNIQUE-唯一索引
ON [schema].[owner_name] ( column_name ) // [schema].[owner_name] 数据库表 //column_name 要创建索引的列名
–WITH PAD_INDEX
— | FILLFACTOR = fillfactor
— | IGNORE_DUP_KEY
— | DROP_EXISTING
— | STATISTICS_NORECOMPUTE
— | SORT_IN_TEMPDB, .. as required
— ON filegroup
GO
3、查询数据库所有的序列
SELECT * FROM sys.sequences
4、将当前序列的值初始化到我需要的值
SELECT
NEXT VALUE FOR dbo.S_住院_床位信息表_床位ID
GO 61
5、创建索引
CREATE NONCLUSTERED INDEX index_company
ON dbo.Company (ognName,parentId,sort )
–WITH PAD_INDEX
— | FILLFACTOR = fillfactor
— | IGNORE_DUP_KEY
— | DROP_EXISTING
— | STATISTICS_NORECOMPUTE
— | SORT_IN_TEMPDB, .. as required
— ON filegroup
GO
CREATE NONCLUSTERED INDEX index_department
ON dbo.Department( ognName,parentId,sort,head,c_head,branched )
–WITH PAD_INDEX
— | FILLFACTOR = fillfactor
— | IGNORE_DUP_KEY
— | DROP_EXISTING
— | STATISTICS_NORECOMPUTE
— | SORT_IN_TEMPDB, .. as required
— ON filegroup
GO
CREATE NONCLUSTERED INDEX index_user
ON dbo.[User] (account,password,sort,name,sex )
–WITH PAD_INDEX
— | FILLFACTOR = fillfactor
— | IGNORE_DUP_KEY
— | DROP_EXISTING
— | STATISTICS_NORECOMPUTE
— | SORT_IN_TEMPDB, .. as required
— ON filegroup
GO
CREATE NONCLUSTERED INDEX index_userKey
ON dbo.UserKey ( userId,ognId )
–WITH PAD_INDEX
— | FILLFACTOR = fillfactor
— | IGNORE_DUP_KEY
— | DROP_EXISTING
— | STATISTICS_NORECOMPUTE
— | SORT_IN_TEMPDB, .. as required
— ON filegroup
GO
6、创建触发器
—单位触发器
CREATE TRIGGER trigger_Upate_Company
ON dbo.Company
AFTER UPDATE
AS
BEGIN
IF (SELECT enable FROM Deleted)=0
BEGIN
UPDATE dbo.Department SET enable=0 WHERE parentId IN(SELECT Deleted.ognId FROM Deleted)
END
END
GO
–部门触发器
CREATE TRIGGER trigger_Upate_Department
ON dbo.Department
AFTER UPDATE
AS
BEGIN
IF (SELECT enable FROM Deleted)=0
BEGIN
UPDATE dbo.UserKey SET enable=0 WHERE ognId IN(SELECT Deleted.ognId FROM Deleted)
END
END
GO
—部门和用户的中间件触发器
CREATE TRIGGER trigger_Upate_UserKey
ON dbo.UserKey
AFTER UPDATE
AS
BEGIN
IF (SELECT enable FROM Deleted)=0
BEGIN
IF(SELECT COUNT(userId) FROM dbo.UserKey WHERE userId IN(SELECT Deleted.userId FROM Deleted))=1
BEGIN
UPDATE dbo.[User] SET enable=0 WHERE userId IN(SELECT Deleted.userId FROM Deleted)
END
END
END
【云技术在线】为您提供以下专业技术服务：
上云服务：上云架构设计，大流量高并发架构设计，混合云架构设计，上云迁移，云上容灾，跨国云访问加速等；
安全服务：等保咨询及实施，渗透测试，漏洞扫描，木马查杀，安全加固，应急响应，DDOS/CC防护，勒索病毒防护，安全托管等；
运维服务：Linux系统运维，数据恢复及备份，负载均衡集群搭建及维护，数据库集群搭建及维护，网站运行环境搭建及维护，系统性能调优，中间件性能调优，数据库性能调优，数据库读写分离，分库分表，性能（压力）测试，7*24小时监控及故障应急响应，故障排查，大流量高并发活动护航等。