好资源导航 » 文章资讯 » SqlServer实现类似Oracle的before触发器示例

SqlServer实现类似Oracle的before触发器示例

2024-04-09 19:21:05 446

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

返回顶部
3162201930
czq8825@qq.com

SqlServer实现类似Oracle的before触发器示例

热门推荐

随机推荐