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

times. Be aware of what you are doing in your trigger, and try to keep the
code as quick and efficient as possible.
Where does Mountain View Music use triggers? One is on tbl_
payment to ensure that we get all the information we need based on the
payment type. You will remember we decided to implement payments in
a single table even though we can accept three payment methods: gift
cards, credit cards, and direct bank draft. To accommodate this, all the
columns in tbl_payment that don't pertain to all payment types allow
NULL data. Obviously, if the customer pays with a credit card, we require
the credit card number, expiration date, type, and credit card verification
(CCV) code. To enforce the required data to be present, we use the fol-
lowing trigger.
CREATE TRIGGER trg_payment_data
ON dbo.tbl_payment
FOR INSERT, UPDATE
AS
DECLARE @payment_type tinyint
,@cc_type varchar(16)
,@cc_number bigint
,@cc_expire date
,@ccv_code smallint
,@gc_number bigint
,@account_number bigint
,@routing_number int
,@license_number varchar(20)
,@license_state char(10)
SELECT @payment_type = payment_type
,@cc_type = cc_type
,@cc_number = cc_number
,@cc_expire = cc_expire
,@ccv_code = ccv_code
,@gc_number = gc_number
,@account_number = account_number
,@routing_number = routing_number
,@license_number = license_number
,@license_state = license_state
FROM inserted