(With triggers, you can either roll back or continue the transaction, depending on how you handle referential integrity.) Nevertheless they are the most commonly used constraint and simple to define and alter. A unique index checks that all data in the column is unique when it is created and also when an insert or update is attempted.You cannot create a unique index on a column that contains null values in more than one row – an error is, obviously, triggered.
Triggers can be quite complex, as Pop Rivett explains elsewhere on this site with his excellent ‘Auditing’ trigger. Imagine we want to check addresses that are being placed in an address table.BOL gives the mysterious advice to use Check constraints instead, as if the two devices had the same functionality.Rules are used to check data that is being inserted or updated in columns.If you need to perform a fairly complex check, and it has to be done in more than one table, then a rule is very handy.One rule can be referenced from any number of tables or user-defined data types in the database. You can create a rule easily in EM or SMSS but don’t even think about altering the rule if you have bound it to an object.Rules, defaults, indexes, and triggers are used for: I have a certain difficulty with this workbench, as the SQL Server team have given advanced notice of its intention to replace Rules and Defaults, probably with ANSI Domains.