Define a "self join" that matches by SSN.
YourTable::SSN = YourTable 2::SSN
In Manage | Database | relationships, make a new table occurrence of YourTable by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box.
We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.
Now this script, set to run with OnObjectExit or OnObjectSave can check for duplicate records:
If [ Count ( YourTable 2::SSN ) > 1 ]
Show Custom Dialog ["A duplicate record exists...."]
Do i have to link both of these by SSN for the relationship?
"both"? there's only one table here linked to itself by the SSN field. That's what the first part of my last post describes.
it seems it only works if the record is commited first.
That doesn't suprise me. Before the record is committed, If [ Count ( YourTable 2::SSN ) = 1 ] is probably the indication that you have a duplicate. (Once committed, a record wil match to itself in this relationship so a count greater than 2 will tell you that you have at least one duplicate.)
You may want to commit the record as part of this test to get consistent results. You might also experiment with using the OnObjectValidate trigger with = 1 as your test for duplicates.