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#自定义表值函数