1.触发器
Table : UserInfo
字段: UserId UnitID
Table : Publish
字段: PublishID UnitID
Table : Subscribe
字段: PublishID UnitID UserId
当User插入一条数据时根据插入数据的UnitID
去Publish 表进行查询得到PublishID,结果有多条
然后把结果循环插入Subscribe
触发器应该怎么写?
CREATE TRIGGER [Userinfo_Insert_Subscribe] ON [dbo].[UserInfo]
FOR INSERT
AS
insert into dbo.Subscribe(UnitUserID,UserID,Type,DataID,CreateDate)
select i.UnitUserID,i.UserID,1,p.id,getdate() from dbo.PublishType p
,Inserted i
where p.UnitUserID =i.UnitUserID and p.IsDefaultSubscribe ='Y'
注: 之前一直卡在使用循环插入上,实际只需一条sql
二.视图
create view allMessage2 as
select MessageLog.id,MessageLog.UserID,MessageLog.UnitUserID,convert(varchar(50),MessageLog.CreateDate,21) as CreateDate ,
SendDate,MessageContent ,MessageType,SendMode,
MessageLog.UserMobile,UserInfo.UserName as UserName,cast('m'+cast(MessageLog.id as varchar(10)) as varchar(15)) as tid
from dbo.MessageLog
left join dbo.UserInfo on MessageLog.UserID = dbo.UserInfo.UserID
union all
select LogID as id,NoteLog.UserID,NoteLog.UnitUserID, convert(varchar(50),NoteLog.CreateDate,21) as CreateDate ,
convert(varchar(50),NoteLog.SendDate,21) as SendDate ,Note.Content as messageContent,6 as MessageType,
2 as SendMode,UserInfo.MobileNO as UserMobile,UserInfo.UserName as UserName,cast('n'+cast(LogID as varchar(10)) as varchar(15)) as tid
from dbo.NoteLog
left join dbo.Note on Note.NoteID = NoteLog.NoteID
left join dbo.UserInfo on NoteLog.UserID = dbo.UserInfo.UserID
where NoteLog.NoteType =1
三.存储过程
写道
CREATE PROCEDURE unitStatisticByDate2
@unitUserID int,
@startDate varchar(10),
@endDate varchar(10)
AS
BEGIN
if EXISTS (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#ut') and type='U')
drop table #ut;
create table #ut(unitUserID int)
insert into #ut(unitUserID) values(@unitUserID)
if ltrim(rtrim(@startDate))!= '' and ltrim(rtrim(@endDate))!= ''
BEGIN
select a.unitUserID ,
(select count(1) as count from dbo.VisitPageLog where unitUserID=a.unitUserID
and UserIP <> '219.232.238.17'
and VisitTime >= @startDate and VisitTime <= @endDate
) as allVisitCount ,
(select count(1) as count from dbo.SearchLog where unitUserID=a.unitUserID
and CreateDate >= @startDate and CreateDate <= @endDate
) as allUserSearchCount,
(select count(1) as count from dbo.BrowseInfo where unitUserID=a.unitUserID
and CreateDate >= @startDate and CreateDate <= @endDate
) as browseInfoCount,
(select count(1) as count from uoml where unitUserID=a.unitUserID
and CreateDate >= @startDate and CreateDate <= @endDate
) as uomlCount,
(select count(1) as count from dbo.delivery where unitUserID=a.unitUserID
and CreateDate >= @startDate and CreateDate <= @endDate
) as deliveryCount,
(select count(1) as count from dbo.Favorites where unitUserID=a.unitUserID
and CreateDate >= @startDate and CreateDate <= @endDate
) as favoritesCount,
(select count(1) as count from dbo.Subscribe where unitUserID=a.unitUserID
and CreateDate >= @startDate and CreateDate <= @endDate
) as subscribeCount,
(select count(1) as count from allMessage where unitUserID=a.unitUserID
and CreateDate >= @startDate and CreateDate <= @endDate
) as allMessageCount,
(select count(1) as count from dbo.NoteLog where NoteType =2 and unitUserID=a.unitUserID
and CreateDate >= @startDate and CreateDate <= @endDate
) as allMmsMessageCount
from #ut a
END
else
BEGIN
select a.unitUserID ,
(select count(1) as count from dbo.VisitPageLog where unitUserID=a.unitUserID
and UserIP <> '219.232.238.17'
) as allVisitCount ,
(select count(1) as count from dbo.SearchLog where unitUserID=a.unitUserID
) as allUserSearchCount,
(select count(1) as count from dbo.BrowseInfo where unitUserID=a.unitUserID
) as browseInfoCount,
(select count(1) as count from uoml where unitUserID=a.unitUserID
) as uomlCount,
(select count(1) as count from dbo.delivery where unitUserID=a.unitUserID
) as deliveryCount,
(select count(1) as count from dbo.Favorites where unitUserID=a.unitUserID
) as favoritesCount,
(select count(1) as count from dbo.Subscribe where unitUserID=a.unitUserID
) as subscribeCount,
(select count(1) as count from allMessage where unitUserID=a.unitUserID
) as allMessageCount,
(select count(1) as count from dbo.NoteLog where NoteType =2 and unitUserID=a.unitUserID
) as allMmsMessageCount
from #ut a
END
END
GO
四.定时作业
分享到:
相关推荐
SqlServer数据库字典--表.视图.函数.存储过程.触发器.主键.外键.约束.规则
SQL SERVER为每个触发器都创建了两个临时表INSERTED表和DELETED表,这两个表的逻辑结构与被触发器作用的表一样,用户可以读取这两个表的内容,但不能对它们进行修改,触发器执行完后,这两个表也会自动删除。...
SQL的存储过程、触发器等建立视图存储过程触发器函数(自定义函数)索引 视图 视图是从一个或几个基本表(或视图)导出的表。不同的是,它是一虚表,数据库中只存放视图的定义,而不存放视图对应的数据,这些数据...
SQL Server 数据库基础.pdf,SQL Server 数据管理(常用函数).pdf,SQL Server 数据查询(表的关联).pdf,SQL Server 事务索引视图.pdf,SQL Server 存储过程及触发器.pdf,SQL Server 编程及高级查询.pdf,让你从入门...
其步骤就是:利用update操作触发器产生的2个虚拟表【inserted】用来存储修改的数据信息和【deleted】表,然后将对应的数据更新到对应数据表中的字段信息中; 1.首先创建3个表: a.信息表: USE [SQL-LI] BEGIN ...
SQL储存过程等的解密,破解字节不受限制,适用于SQLSERVER2000存储过程,函数,视图,触发器
sqlserver触发器例子 一﹕ 触发器是一种特殊的存储过程﹐它不能被显式地调用﹐而是在往表中插入记录﹑更新记录或者删除记录时被自动地激活。所以触发器可以用来实现对表实施复杂的完整性约`束。 二﹕ SQL ...
SQL 2005 中触发器、事务,存储过程、视图_T-SQL语句的详细讲解课程学习PPT(含有实例和作业题)
2024SQLServer解密存储过程、函数、视图、触发器,mssql解密,WITH ENCRYPTION解密,亲测可用,支持2000、2005、2008、2012、2019...
破解字节不受限制,适用于SQLSERVER2000存储过程,函数,视图,触发器
打破了Microsoft发布SQLServer时声称的SQLServer存储过程,触发器,视图加密时采用的是不可逆算法,经加密后无法解密的神话。用SQLDecrypt将解密经过加密的任何长度的SQLServer存储过程,触发器,视图,不管其长度多...
输入字符串(字段、表、存储过程、视图、函数、触发器等任意对象名),即可查找出引用该对象的所有脚本,包括存储过程、视图、函数、触发器等, 方便数据库开发者,修改关联的地方
实验四 存储过程、触发器与索引 一、实验目的 1.熟悉大型数据库实验环境,以MS SQL SERVER为例; 2.掌握视图; 3.掌握存储过程与触发器; 4.掌握MS SQL SERVER的导入和导出; 5.掌握MS SQL SERVER的索引。 二、实验...
深入学习SQLServer,毕业设计,论文答辩 ER模型,视图,表,存储过程,触发器,事务等的详细介绍
加密测试的存储过程 IF EXISTS(SELECT 1 FROM SYSOBJECTS WHERE TYPE='P' AND NAME='P_TEST') DROP PROCEDURE P_TEST GO CREATE PROCEDURE P_TEST(@USERNAME VARCHAR(20),@MSG VARCHAR(20) OUTPUT) WITH ...
典型的数据库编程对象包括视图、存储过程、触发器、函数等。视图在第10章已经讲过了,本章将讲述其他常用的数据库编程对象。存储过程是一个可重用的代码模块,可以高效率地完成指定的操作。触发器是一种特殊类型的...
此存储过程可以破解sql 2000、2005、2008加密的函数、存储过程、视图、触发器,本人平时工作中经常使用,非常方便!
Delphi 关于SQL Server数据库的实例,利用Delphi编程技术实现解密SQL存储过程、触发器、视图、函数等,程序编译后的截图如下:
对五天的课程安排: 第一天: 初识SQL-SERVER2000;...数据完整性的设计及实现、存储过程与触发器、SQLSERVER的数据传输服务。 第五天: SQLSERVER与WEB的结合,开发模型及数据库的选择。ASP结合SQL-SERVER,ADO的概念
SQL Server 2000概述、SQL Server 2000安装和配置、SQL Server 2000工具、数据库系统基础、SQL Server ...视图、索引、触发器、存储过程、SQL Server 2000数据库的安全性管理、数据库的备份和恢复、基于Web的数据库应用...