--创建用于监视对TestResult表进行更新数据的触发器 ifexists (select*from dbo.sysobjects where id =object_id(N'[dbo].[TestResult_Update]') andOBJECTPROPERTY(id, N'IsTrigger') =1) droptrigger[dbo].[TestResult_Update] go ifexists (select*from dbo.sysobjects where id =object_id(N'[dbo].[TestResult_ServerUpdate]') andOBJECTPROPERTY(id, N'IsTrigger') =1) droptrigger[dbo].[TestResult_ServerUpdate] GO CREATETRIGGER TestResult_ServerUpdate ON TestResult AFTER UPDATE AS --IF UPDATE (CPH) BEGIN DECLARE@UniqIDuniqueidentifier, @idbigint, @StationCodenvarchar(50), @TestLineCodenvarchar(50) --检测线代码 DECLARE c3 CURSORFOR --SELECT TestResult.AutoRTID,TestResult.ResultUniqID,TestResult.TestLineCode,TestResult.StationNo --FROM TestResult, deleted --WHERE TestResult.AutoRTID = deleted.AutoRTID and TestResult.StationNo= deleted.StationNo and TestResult.ResultUniqID=deleted.ResultUniqID and TestResult.TestLineCode=deleted.TestLineCode SELECT AutoRTID,ResultUniqID,TestLineCode,StationNo FROM DELETED OPEN c3 FETCHNEXTFROM c3 INTO@id,@UniqID,@TestLineCode,@StationCode WHILE@@fetch_status=0 BEGIN INSERTINTO Server_UpdateRecordTable (TableName,NumOfUniqRecord,NumOfRecordID,NumOfCheckStation,NumOfCheckGroup) VALUES('TestResult',@UniqID,@id,@StationCode,@TestLineCode) FETCHNEXTFROM c3 INTO@id,@UniqID,@TestLineCode,@StationCode END CLOSE c3 DEALLOCATE c3 END GO --测试 --select * from testresult --select * from server_updaterecordtable --update testresult set autortid=99999 where autortid=99999 --delete from testresult where autortid=99999 --go --drop trigger savedel --go --CREATE TRIGGER savedel -- ON testresult --FOR DELETE --AS -- INSERT INTO Server_UpdateRecordTable (NumOfUniqRecord,NumOfRecordID,NumOfCheckStation) -- SELECT resultuniqid,autortid,stationcode FROM deleted --go --创建监视插入Server_InsertRecordTable表中数据的触发器 --用于更新Server_UpdateRecordTable 的纪录唯一编号,好用于后面的更新操作 ifexists (select*from dbo.sysobjects where id =object_id(N'[dbo].[InsertRecordTable_ServerInsert]') andOBJECTPROPERTY(id, N'IsTrigger') =1) droptrigger[dbo].[InsertRecordTable_ServerInsert] go CREATETRIGGER InsertRecordTable_ServerInsert ON Server_InsertRecordTable FORINSERT AS DECLARE @UniqIDuniqueidentifier, --在服务器上插入纪录后的生成的惟一的纪录编号 @idbigint, --在检测站的数据库中的纪录编号 @StationCodenvarchar(50), --检测站代码 @TestLineCodenvarchar(50) --检测线代码 DECLARE c2 CURSORFOR SELECT Server_InsertRecordTable.NumOfUniqRecord,inserted.NumOfRecordID,inserted.NumOfCheckStation,inserted.NumOfCheckGroup FROM Server_InsertRecordTable, inserted WHERE Server_InsertRecordTable.NumOfUniqRecord = inserted.NumOfUniqRecord and Server_InsertRecordTable.NumOfRecordID= inserted.NumOfRecordID and Server_InsertRecordTable.NumOfCheckStation=inserted.NumOfCheckStation and Server_InsertRecordTable.NumOfCheckGroup=inserted.NumOfCheckGroup OPEN c2 FETCHNEXTFROM c2 INTO@UniqID, @id,@StationCode,@TestLineCode WHILE@@fetch_status=0 BEGIN UPDATE Server_UpdateRecordTable set NumOfUniqRecord=@UniqIDWHERE NumOfRecordID=@idand NumOfCheckStation=@StationCodeand NumOfCheckGroup=@TestLineCode FETCHNEXTFROM c2 INTO@UniqID,@id,@StationCode,@TestLineCode END CLOSE c2 DEALLOCATE c2 GO selecttop3*from testresult selecttop3*from server_insertrecordtable select*from Server_UpdateRecordTable