How to check if row already exists in log table from inside After Delete trigger in SQL Server 2008? -

How to check if row already exists in log table from inside After Delete trigger in SQL Server 2008? -

i found similar question here not understand how implement in trigger. using sql server 2008.

this trigger.

alter trigger [dbo].[trg_deletedrecordlog] on [dbo].[projectrecords] delete insert 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() deleted

in link provided, not understand meant "old"? , how can find/declare in script? please help !!!

solution 1 : [i have used it]

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] delete insert 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() deleted left bring together deletedprojectrecords.recordid = deleted.recordid deletedprojectrecords.recordid null

i understand wanted insert record if there no record available in deletedprojectrecords table recordid in current deleted.

here trick utilize left bring together clause if there not record available in deletedprojectrecords.

solution 2: [not tried solution, work.]

utilize [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] delete if not exists(select 1 dbo.deletedprojectrecords recordid = deleted.recordid) begin insert 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() deleted end

here trick usgae of if not exists

sql-server-2008 triggers