SqlServer实现类似Oracle的before触发器示例
1.插入数据前判断数据是否存在
SETANSI_NULLSON GO SETQUOTED_IDENTIFIERON GO --============================================= --Author:<Author,,Name> --Createdate:<CreateDate,,> --Description:<Description,,> --============================================= alterTRIGGERCategoryExistTrigger ONProductCategory insteadofinsert AS declare@categoryNamevarchar(50); BEGIN --SETNOCOUNTONaddedtopreventextraresultsetsfrom --interferingwithSELECTstatements. SETNOCOUNTON; --Insertstatementsfortriggerhere select@categoryName=CategoryNamefrominserted; ifexists(select*fromProductCategorywhereCategoryName=@categoryName) begin print'Categoryexists..' end; else begin insertintoProductCategoryselect*frominserted; end; END
2.删除表中数据时需要先删除外键表的数据
SETANSI_NULLSON GO SETQUOTED_IDENTIFIERON GO --============================================= --Author:<Author,,Name> --Createdate:<CreateDate,,> --Description:<Description,,> --============================================= alterTRIGGERDeleteOrderTrigger ONOrderHeader insteadofdelete AS declare@OrderIdvarchar(50); BEGIN SETNOCOUNTON; select@OrderId=OrderIdfromdeleted; deletefromOrderLinewhereOrderId=@OrderId; END GO