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
四.定时作业