All, I have a new application that uses an administration database (SQL Server 2008 R2) to enforce data structure constraints. It is important that the table structure of this database is maintained at all costs, and the biggest danger of the structure not being upheld is via a user going into the database and manually changing it.
In this situation, am I best constructing a checksum on the tables I wish to 'protect' and checking against this each time I do something within the application? Or, am I best doing all the 'protecting' server-side? That is, creating a TRIGGER say
CREATE TRIGGER EnsureIntegrity ON ProtectedTable
FOR INSERT, UPDATE, DELETE AS
BEGIN
IF (EXISTS (Some_Condidtion)
RAISERROR(/*Some Error Message*/)
ROLLBACK TRANSACTION
END
Any advice of the best way to achieve what I want is most appreciated.