T-SQL 知识点

1.删除数据并重置自增种子

truncate table TableName

2.触发器

create trigger [dbo].[TR_People_Change]
on [dbo].[People]
for INSERT,UPDATE
AS
BEGIN
--新增
if( exists (select 1 from inserted) and not exists(select 1 from deleted))
begin

update TableVersions set [Version]=[Version]+1 where Name='People';

update People set [Version] =  (select Version from TableVersions where Name='People') where ID    in(select ID from inserted);

end

--修改
else if( exists(select 1 from inserted)  and exists(select 1 from deleted))
begin

if UPDATE(LeftIris) or UPDATE(RightIris) or UPDATE(IdCardPhoto) or UPDATE(Name) or UPDATE(Sex) or UPDATE([Password]) or UPDATE(CardNum) or UPDATE(IdentifyWay) or UPDATE(DevAuth) or UPDATE(DeptID)
begin

update TableVersions set [Version]=[Version]+1 where Name='People';

update People set [Version] = (select Version from TableVersions where Name='People') where ID    in(select ID from inserted);

end
end

end

3.游标

DECLARE PERSON_ABNORMAL_EXIT CURSOR SCROLL FOR
select ID FROM People where UsedFlag=1 and Position<>@Position

OPEN PERSON_ABNORMAL_EXIT
DECLARE @pID INT


FETCH NEXT FROM PERSON_ABNORMAL_EXIT INTO @pID

WHILE @@FETCH_STATUS=0
BEGIN


update People set Position=@Position where ID=@pID
insert into AttendentRecords(PersonID,DeviceID,AccessType,CheckTime,Direction,UsedFlag) VALUES(@pID,-1,7,GETDATE(),1,1)

FETCH NEXT FROM PERSON_ABNORMAL_EXIT INTO @pID

END

CLOSE PERSON_ABNORMAL_EXIT
DEALLOCATE PERSON_ABNORMAL_EXIT

4.Sql Server调用C#自定义表值函数