With some guidance from Paul Ramsey, I implemented a check constraint on my sample table to allow only a single SRID in a table. As Paul indicated in his comment on my previous post, PostGIS does this for you. SQL 2008 doesn’t yet but I hope it does. The syntax for my table was:
--only allow WGS84 ALTER TABLE SpatialTable ADD CONSTRAINT enforce_srid_geometry CHECK (GeomCol1.STSrid = 4326);
After doing that, the following SQL will properly fail:
UPDATE SpatialTable SET GeomCol1.STSrid = 4127;
Thanks to Paul for chiming in!