I am getting a 547 error while executing a DELETE query within an Instead Of Trigger on a table. Obviously I could be misreading the data; however I've examined the table contents and the corresponding foreign table and there is no record in the foreign table whose key value matches that of the primary table key; thus I believe the reference constraint error is not valid. At the same time I find it unlikely that SQL 2008 is randomly throwing a referential constraint error.
I'm not a SQL guru, just a developer trying to test some stored procedures and triggers and at this point am pretty much baffled. I'd appreciate any help/pointers on how I might debug this. Is there a way to get more information out of SQL on such an error? I'm already single stepping through the code, but when I hit this DELETE there's nothing to step "into" (as there's no trigger associated with a DELETE on that table.)
Thanks in advance for any help!
- Edited by Bill Cohagan Monday, May 10, 2010 10:06 PM Make title more descriptive
A couple of things you might do. First, make sure that all the tables are referenced with the schema name in your trigger, and also fully qualify the table names when you are examining the table contents. That is, make sure that it is not the case that you and your trigger are looking at one table, but the foreign key is looking at a different table in a different schema with the same name. Second, you could see this error if your database is broken. So do a DBCC CHECKTABLE on both the parent and child table. Note that if the tables are large, DBCC CHECKTABLE can take a while. If that is going to be a problem, you can dump and restore your database somewhere else into a development environment and do the checks there. If this database was at one time on SQL Server 2000 or earlier, include the WITH DATA_PURITY option. (If you are not sure, include the DATA_PURITY option). If you want to check the whole database, you can do a DBCC CHECKDB WITH DATA_PURITY, of course, that could take some time if you have a large database.
Thanks for the quick response. I ran the DBCC CHECKDB WITH DATA_PURITY as my database has only a small amount of data in it. There were no errors. I will take your suggestion to fully qualify names; however there is only the one schema, dbo. I am using SQL Server Management Studio to dump the data so I'm sure I'm looking at the intended data. The table references in my stored procedures are NOT fully qualified however so I'll change that and see if it helps.