CREATING THE PHYSICAL MODEL WITH SQL SERVER - A Developer's Guide to Data Modeling for SQL Server - page 216

IF @payment_type= 1 --Credit Card
BEGIN
IF @cc_type IS NULL
OR @cc_number IS NULL
OR @cc_expire IS NULL
OR @ccv_code IS NULL
BEGIN
RAISERROR ('Error: Credit Card Data is Missing.',11,1)
ROLLBACK
END
END
IF @payment_type = 2 --Gift Card
BEGIN
IF @gc_number IS NULL
BEGIN
RAISERROR ('Error: Gift Card Data is Missing.',11,1)
ROLLBACK
END
END
IF @payment_type = 3 --Bank Draft
BEGIN
IF @account_number IS NULL
OR @routing_number IS NULL
OR @license_number IS NULL
OR @license_state IS NULL
BEGIN
RAISERROR ('Error: Bank Account Data is
Missing.',11,1)
ROLLBACK
END
END
As you can see, the trigger looks at the new data and ensures that all
the required data is provided based on the payment type specified. If any-
thing is missing, an error is returned and the INSERT or UPDATE state-
ment is rolled back. Again, if you can write it in T-SQL you can manipulate
it in a trigger. In fact, you can write CLR triggers using .NET managed
code if you are using SQL Server 2005 or later. This really opens the door
to what you can do with your triggers and makes implementing business
rules for data integrity even more robust.