当前位置: 代码迷 >> Sql Server >> 怎么写触发器
  详细解决方案

怎么写触发器

热度:66   发布时间:2016-04-27 12:41:24.0
如何写触发器
新手,小白问题,求助。
需求:
用户的权限变更,
若原来是学员,变更后保留其study表中的记录,(usertype由0->1);
若原来是管理员(usertype由1->0),变更后
  1.如果原来没有学习记录,插入所有需学习知识点到study表中,并将其学习状态置为未学,
  2.如果原来有学习记录,不操作study表
ALTER TRIGGER UserStudyUpdate_UserType
ON dbo.users
FOR UPDATE
AS
declare @tmpPoint TABLE
(
PointNumber smallint,
flag tinyint
)

declare @TypeDel smallint,@TypeIns smallint
select @TypeDel=UserType from delected
select @TypeIns=UserType from inserted

declare @EmployeeNumber nvarchar(50),@PointNumber smallint,@i int

if @TypeDel=1 and @TypeIns=0
begin
if not exists select [email protected] from study
begin
insert @tmpPoint select PointNumber,0 from points
set @i=1
while( @i>=1)
begin
select @EmployeeNumber=EmployeeNumber from inserted
select @PointNumber=''
select TOP 1 @PointNumber = PointNumber from @tmpPoint WHERE flag=0
set @i=@@ROWCOUNT
if @i<=0 goto Return_Lab
Insert study values (@EmployeeNumber,@PointNumber,0,null)
if @@error=0
update @tmpPoint SET flag=1 WHERE PointNumber = @PointNumber
Return_Lab:
end
end
end

问题:在select处报错,不知道该如何处置了!

------解决方案--------------------
if not exists (select 1 from study Where [email protected])
[email protected]EmployeeNumber的记录
  相关解决方案