如何检查行是否已经存在于日志表从内部删除后触发在SQL Server 2008

Question

我在这里发现了一个类似的问题，但不明白如何在我的触发器中实现它。我用的是SQL Server 2008。

这是我的触发器。

ALTER TRIGGER [dbo].[TRG_DeletedRecordLog]
ON [dbo].[ProjectRecords] 
FOR DELETE
AS
     INSERT INTO dbo.DeletedProjectRecords  (RecordId, BatchId, ImageId, Parish, Surname, MilitaryNumber, Nationality, DateOfBirthAndSpecialNumber, FirstName, BarnOrChild, DateOfMarriage, SpouseOrGuardianDateOfBirth, Occupation, BirthplaceParish, BirthplaceCounty, EarlierCensus1, EarlierCensus1Parish, EarlierCensus1County, EarlierCensus1Place, EarlierCensus2, EarlierCensus2Parish, EarlierCensus2County, EarlierCensus2Place, GuardianFirstNameInitials, PostalAddressLivingplace, PostalAddressCity, SailorHouseNumber, NumberOnImage, EntryUserId, EntryDate, VerifyUserId, VerifyDate, WorkstationId, ImageFocusPosition, ProjectId, ImageType, NumberOnImageFocus, SurnameFocus, MilitaryNumberFocus, NationalityFocus, DateOfBirthAndSpecialNumberFocus, FirstNameFocus, BarnOrChildFocus, DateOfMarriageFocus, SpouseOrGuardianDateOfBirthFocus, OccupationFocus, BirthplaceParishFocus, BirthplaceCountyFocus, EarlierCensus1Focus, EarlierCensus1ParishFocus, EarlierCensus1CountyFocus, EarlierCensus1PlaceFocus, EarlierCensus2Focus, EarlierCensus2ParishFocus, EarlierCensus2CountyFocus, EarlierCensus2PlaceFocus, GuardianFirstNameInitialsFocus, PostalAddressLivingplaceFocus, PostalAddressCityFocus, SailorHouseNumberFocus, SerialNumber, SoftwareVersion, IsParishVerified, IsNumberOnImageVerified, IsSurnameVerified, IsMilitaryNumberVerified, IsNationalityVerified, IsDateOfBirthAndSpecialNumberVerified, IsFirstNameVerified, IsBarnOrChildVerified, IsDateOfMarriageVerified, IsSpouseOrGuardianDateOfBirthVerified, IsOccupationVerified, IsBirthplaceParishVerified, IsBirthplaceCountyVerified, IsEarlierCensus1Verified, IsEarlierCensus1ParishVerified, IsEarlierCensus1CountyVerified, IsEarlierCensus1PlaceVerified, IsEarlierCensus2Verified, IsEarlierCensus2ParishVerified, IsEarlierCensus2CountyVerified, IsEarlierCensus2PlaceVerified, IsGuardianFirstNameInitialsVerified, IsPostalAddressLivingplaceVerified, IsPostalAddressCityVerified, IsSailorHouseNumberVerified, OtherInformation, OtherInformationFocus, IsOtherInformationVerified, DeletedBy, DeleteDate)
     SELECT 
        RecordId, BatchId, ImageId, Parish, Surname, MilitaryNumber, 
        Nationality, DateOfBirthAndSpecialNumber, FirstName, BarnOrChild, 
        DateOfMarriage, SpouseOrGuardianDateOfBirth, Occupation, BirthplaceParish,  
        BirthplaceCounty, EarlierCensus1, EarlierCensus1Parish, EarlierCensus1County, 
        EarlierCensus1Place, EarlierCensus2, EarlierCensus2Parish, 
        EarlierCensus2County, EarlierCensus2Place, GuardianFirstNameInitials, 
        PostalAddressLivingplace, PostalAddressCity, SailorHouseNumber, 
        NumberOnImage, EntryUserId, EntryDate, VerifyUserId, VerifyDate, 
        WorkstationId, ImageFocusPosition, ProjectId, ImageType, NumberOnImageFocus, 
        SurnameFocus, MilitaryNumberFocus, NationalityFocus, 
        DateOfBirthAndSpecialNumberFocus, FirstNameFocus, BarnOrChildFocus, 
        DateOfMarriageFocus, SpouseOrGuardianDateOfBirthFocus, OccupationFocus, 
        BirthplaceParishFocus, BirthplaceCountyFocus, EarlierCensus1Focus, 
        EarlierCensus1ParishFocus, EarlierCensus1CountyFocus, 
        EarlierCensus1PlaceFocus, EarlierCensus2Focus, EarlierCensus2ParishFocus, 
        EarlierCensus2CountyFocus, EarlierCensus2PlaceFocus, 
        GuardianFirstNameInitialsFocus, PostalAddressLivingplaceFocus, 
        PostalAddressCityFocus, SailorHouseNumberFocus, SerialNumber, SoftwareVersion, 
        IsParishVerified, IsNumberOnImageVerified, IsSurnameVerified, 
        IsMilitaryNumberVerified, IsNationalityVerified, 
        IsDateOfBirthAndSpecialNumberVerified, IsFirstNameVerified, 
        IsBarnOrChildVerified, IsDateOfMarriageVerified, 
        IsSpouseOrGuardianDateOfBirthVerified, IsOccupationVerified, 
        IsBirthplaceParishVerified, IsBirthplaceCountyVerified, 
        IsEarlierCensus1Verified, IsEarlierCensus1ParishVerified, 
        IsEarlierCensus1CountyVerified, IsEarlierCensus1PlaceVerified, 
        IsEarlierCensus2Verified, IsEarlierCensus2ParishVerified, 
        IsEarlierCensus2CountyVerified, IsEarlierCensus2PlaceVerified, 
        IsGuardianFirstNameInitialsVerified, IsPostalAddressLivingplaceVerified, 
        IsPostalAddressCityVerified, IsSailorHouseNumberVerified, OtherInformation, 
        OtherInformationFocus, IsOtherInformationVerified, null, GETDATE()
     FROM DELETED 

在提供的链接中，我不明白"OLD"是什么意思?我如何在我的脚本中找到/声明它?请帮忙!!

Answer 1

解决方案一:[我已经用过了]

USE [SwedishCensusDB]
GO
/****** Object:  Trigger [dbo].[TRG_DeletedRecordLog]    Script Date: 06/21/2014 10:39:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[TRG_DeletedRecordLog]
ON [dbo].[ProjectRecords] 
FOR DELETE
AS
INSERT INTO dbo.DeletedProjectRecords  (RecordId, BatchId, ImageId, Parish, Surname, MilitaryNumber, Nationality, DateOfBirthAndSpecialNumber, FirstName, BarnOrChild, DateOfMarriage, SpouseOrGuardianDateOfBirth, Occupation, BirthplaceParish, BirthplaceCounty, EarlierCensus1, EarlierCensus1Parish, EarlierCensus1County, EarlierCensus1Place, EarlierCensus2, EarlierCensus2Parish, EarlierCensus2County, EarlierCensus2Place, GuardianFirstNameInitials, PostalAddressLivingplace, PostalAddressCity, SailorHouseNumber, NumberOnImage, EntryUserId, EntryDate, VerifyUserId, VerifyDate, WorkstationId, ImageFocusPosition, ProjectId, ImageType, NumberOnImageFocus, SurnameFocus, MilitaryNumberFocus, NationalityFocus, DateOfBirthAndSpecialNumberFocus, FirstNameFocus, BarnOrChildFocus, DateOfMarriageFocus, SpouseOrGuardianDateOfBirthFocus, OccupationFocus, BirthplaceParishFocus, BirthplaceCountyFocus, EarlierCensus1Focus, EarlierCensus1ParishFocus, EarlierCensus1CountyFocus, EarlierCensus1PlaceFocus, EarlierCensus2Focus, EarlierCensus2ParishFocus, EarlierCensus2CountyFocus, EarlierCensus2PlaceFocus, GuardianFirstNameInitialsFocus, PostalAddressLivingplaceFocus, PostalAddressCityFocus, SailorHouseNumberFocus, SerialNumber, SoftwareVersion, IsParishVerified, IsNumberOnImageVerified, IsSurnameVerified, IsMilitaryNumberVerified, IsNationalityVerified, IsDateOfBirthAndSpecialNumberVerified, IsFirstNameVerified, IsBarnOrChildVerified, IsDateOfMarriageVerified, IsSpouseOrGuardianDateOfBirthVerified, IsOccupationVerified, IsBirthplaceParishVerified, IsBirthplaceCountyVerified, IsEarlierCensus1Verified, IsEarlierCensus1ParishVerified, IsEarlierCensus1CountyVerified, IsEarlierCensus1PlaceVerified, IsEarlierCensus2Verified, IsEarlierCensus2ParishVerified, IsEarlierCensus2CountyVerified, IsEarlierCensus2PlaceVerified, IsGuardianFirstNameInitialsVerified, IsPostalAddressLivingplaceVerified, IsPostalAddressCityVerified, IsSailorHouseNumberVerified, OtherInformation, OtherInformationFocus, IsOtherInformationVerified, DeletedBy, DeleteDate)
             SELECT RecordId, BatchId, ImageId, Parish, Surname, MilitaryNumber, Nationality, DateOfBirthAndSpecialNumber, FirstName, BarnOrChild, DateOfMarriage, SpouseOrGuardianDateOfBirth, Occupation, BirthplaceParish, BirthplaceCounty, EarlierCensus1, EarlierCensus1Parish, EarlierCensus1County, EarlierCensus1Place, EarlierCensus2, EarlierCensus2Parish, EarlierCensus2County, EarlierCensus2Place, GuardianFirstNameInitials, PostalAddressLivingplace, PostalAddressCity, SailorHouseNumber, NumberOnImage, EntryUserId, EntryDate, VerifyUserId, VerifyDate, WorkstationId, ImageFocusPosition, ProjectId, ImageType, NumberOnImageFocus, SurnameFocus, MilitaryNumberFocus, NationalityFocus, DateOfBirthAndSpecialNumberFocus, FirstNameFocus, BarnOrChildFocus, DateOfMarriageFocus, SpouseOrGuardianDateOfBirthFocus, OccupationFocus, BirthplaceParishFocus, BirthplaceCountyFocus, EarlierCensus1Focus, EarlierCensus1ParishFocus, EarlierCensus1CountyFocus, EarlierCensus1PlaceFocus, EarlierCensus2Focus, EarlierCensus2ParishFocus, EarlierCensus2CountyFocus, EarlierCensus2PlaceFocus, GuardianFirstNameInitialsFocus, PostalAddressLivingplaceFocus, PostalAddressCityFocus, SailorHouseNumberFocus, SerialNumber, SoftwareVersion, IsParishVerified, IsNumberOnImageVerified, IsSurnameVerified, IsMilitaryNumberVerified, IsNationalityVerified, IsDateOfBirthAndSpecialNumberVerified, IsFirstNameVerified, IsBarnOrChildVerified, IsDateOfMarriageVerified, IsSpouseOrGuardianDateOfBirthVerified, IsOccupationVerified, IsBirthplaceParishVerified, IsBirthplaceCountyVerified, IsEarlierCensus1Verified, IsEarlierCensus1ParishVerified, IsEarlierCensus1CountyVerified, IsEarlierCensus1PlaceVerified, IsEarlierCensus2Verified, IsEarlierCensus2ParishVerified, IsEarlierCensus2CountyVerified, IsEarlierCensus2PlaceVerified, IsGuardianFirstNameInitialsVerified, IsPostalAddressLivingplaceVerified, IsPostalAddressCityVerified, IsSailorHouseNumberVerified, OtherInformation, OtherInformationFocus, IsOtherInformationVerified, null, GETDATE()
    FROM DELETED 
    LEFT JOIN DeletedProjectRecords.RecordId = Deleted.RecordiD
    WHERE DeletedProjectRecords.RecordId IS NULL

我理解你想要插入记录，只有在DeletedProjectRecords表中没有记录可用，用于当前删除的RecordID。

这里的技巧是使用LEFT JOIN，这样WHERE子句只有在DeletedProjectRecords中没有可用的记录时才会执行。

解决方案2:[Not a Tried Solution, this will work.]

    USE [SwedishCensusDB] GO /****** Object:  Trigger [dbo].[TRG_DeletedRecordLog]    Script Date: 06/21/2014 10:39:29
    ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO

    ALTER TRIGGER [dbo].[TRG_DeletedRecordLog] ON [dbo].[ProjectRecords]  FOR DELETE AS 
IF NOT EXISTS(SELECT 1 FROM dbo.DeletedProjectRecords WHERE RecordID = Deleted.RecordID) BEGIN
         INSERT INTO dbo.DeletedProjectRecords  (RecordId, BatchId, ImageId, Parish, Surname, MilitaryNumber, Nationality, DateOfBirthAndSpecialNumber, FirstName, BarnOrChild, DateOfMarriage, SpouseOrGuardianDateOfBirth, Occupation, BirthplaceParish, BirthplaceCounty, EarlierCensus1, EarlierCensus1Parish, EarlierCensus1County, EarlierCensus1Place, EarlierCensus2, EarlierCensus2Parish, EarlierCensus2County, EarlierCensus2Place, GuardianFirstNameInitials, PostalAddressLivingplace, PostalAddressCity, SailorHouseNumber, NumberOnImage, EntryUserId, EntryDate, VerifyUserId, VerifyDate, WorkstationId, ImageFocusPosition, ProjectId, ImageType, NumberOnImageFocus, SurnameFocus, MilitaryNumberFocus, NationalityFocus, DateOfBirthAndSpecialNumberFocus, FirstNameFocus, BarnOrChildFocus, DateOfMarriageFocus, SpouseOrGuardianDateOfBirthFocus, OccupationFocus, BirthplaceParishFocus, BirthplaceCountyFocus, EarlierCensus1Focus, EarlierCensus1ParishFocus, EarlierCensus1CountyFocus, EarlierCensus1PlaceFocus, EarlierCensus2Focus, EarlierCensus2ParishFocus, EarlierCensus2CountyFocus, EarlierCensus2PlaceFocus, GuardianFirstNameInitialsFocus, PostalAddressLivingplaceFocus, PostalAddressCityFocus, SailorHouseNumberFocus, SerialNumber, SoftwareVersion, IsParishVerified, IsNumberOnImageVerified, IsSurnameVerified, IsMilitaryNumberVerified, IsNationalityVerified, IsDateOfBirthAndSpecialNumberVerified, IsFirstNameVerified, IsBarnOrChildVerified, IsDateOfMarriageVerified, IsSpouseOrGuardianDateOfBirthVerified, IsOccupationVerified, IsBirthplaceParishVerified, IsBirthplaceCountyVerified, IsEarlierCensus1Verified, IsEarlierCensus1ParishVerified, IsEarlierCensus1CountyVerified, IsEarlierCensus1PlaceVerified, IsEarlierCensus2Verified, IsEarlierCensus2ParishVerified, IsEarlierCensus2CountyVerified, IsEarlierCensus2PlaceVerified, IsGuardianFirstNameInitialsVerified, IsPostalAddressLivingplaceVerified, IsPostalAddressCityVerified, IsSailorHouseNumberVerified, OtherInformation, OtherInformationFocus, IsOtherInformationVerified, DeletedBy, DeleteDate)
         SELECT RecordId, BatchId, ImageId, Parish, Surname, MilitaryNumber, Nationality, DateOfBirthAndSpecialNumber, FirstName, BarnOrChild, DateOfMarriage, SpouseOrGuardianDateOfBirth, Occupation, BirthplaceParish, BirthplaceCounty, EarlierCensus1, EarlierCensus1Parish, EarlierCensus1County, EarlierCensus1Place, EarlierCensus2, EarlierCensus2Parish, EarlierCensus2County, EarlierCensus2Place, GuardianFirstNameInitials, PostalAddressLivingplace, PostalAddressCity, SailorHouseNumber, NumberOnImage, EntryUserId, EntryDate, VerifyUserId, VerifyDate, WorkstationId, ImageFocusPosition, ProjectId, ImageType, NumberOnImageFocus, SurnameFocus, MilitaryNumberFocus, NationalityFocus, DateOfBirthAndSpecialNumberFocus, FirstNameFocus, BarnOrChildFocus, DateOfMarriageFocus, SpouseOrGuardianDateOfBirthFocus, OccupationFocus, BirthplaceParishFocus, BirthplaceCountyFocus, EarlierCensus1Focus, EarlierCensus1ParishFocus, EarlierCensus1CountyFocus, EarlierCensus1PlaceFocus, EarlierCensus2Focus, EarlierCensus2ParishFocus, EarlierCensus2CountyFocus, EarlierCensus2PlaceFocus, GuardianFirstNameInitialsFocus, PostalAddressLivingplaceFocus, PostalAddressCityFocus, SailorHouseNumberFocus, SerialNumber, SoftwareVersion, IsParishVerified, IsNumberOnImageVerified, IsSurnameVerified, IsMilitaryNumberVerified, IsNationalityVerified, IsDateOfBirthAndSpecialNumberVerified, IsFirstNameVerified, IsBarnOrChildVerified, IsDateOfMarriageVerified, IsSpouseOrGuardianDateOfBirthVerified, IsOccupationVerified, IsBirthplaceParishVerified, IsBirthplaceCountyVerified, IsEarlierCensus1Verified, IsEarlierCensus1ParishVerified, IsEarlierCensus1CountyVerified, IsEarlierCensus1PlaceVerified, IsEarlierCensus2Verified, IsEarlierCensus2ParishVerified, IsEarlierCensus2CountyVerified, IsEarlierCensus2PlaceVerified, IsGuardianFirstNameInitialsVerified, IsPostalAddressLivingplaceVerified, IsPostalAddressCityVerified, IsSailorHouseNumberVerified, OtherInformation, OtherInformationFocus, IsOtherInformationVerified, null, GETDATE()
         FROM DELETED  END

这里的技巧是使用IF NOT EXISTS