sqlserver视图,存储过程以及触发器

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

 

 

四.定时作业