MSSQL之十七 触发器使用项目案例
【摘要】
触发器使用项目案例 1、添加一个商家系统自动增加一个商家帐号 -- 在商家表中设计Insert触发器CREATE TRIGGER t_business_InsertON shop_businesstbAFTER INSERTAS BEGIN DECLARE @businessname nvarchar(15)SELECT @busine...
-
触发器使用项目案例
-
-
1、添加一个商家系统自动增加一个商家帐号
-
-
-- 在商家表中设计Insert触发器
-
CREATE TRIGGER t_business_Insert
-
ON shop_businesstb
-
AFTER INSERT
-
AS
-
BEGIN
-
-
DECLARE @businessname nvarchar(15)
-
SELECT @businessname = businessname FROM Inserted
-
-
print '恭喜您，添加了一个商家，商家名称：' + @businessname
-
-
insert into dbo.shop_adminusertb
-
(adminid,adminuser,pass,adminright,admintype,busid,admindepart,mobilephone)
-
select busid,businessname,'1111','20',12,busid,businessname,contactmobile from Inserted
-
-
END
-
GO
-
-
-- 在管理员表中设计Insert触发器
-
Create TRIGGER t_adminusertb_Insert
-
ON dbo.shop_adminusertb
-
AFTER INSERT
-
AS
-
BEGIN
-
DECLARE @adminid varchar(20),
-
@adminuser varchar(50),
-
@pass varchar(10),
-
@adminright varchar(10)
-
-
SELECT @adminid = adminid ,
-
@adminuser = adminuser,
-
@pass= pass,
-
@adminright= adminright
-
FROM Inserted
-
print '添加了一个帐号'+ char(13) +
-
'用户名称：' + @adminuser + char(13) +
-
'用户名：' + @adminid + char(13) +
-
'密 码：' + @pass + char(13) +
-
'权 限：' + @adminright
-
END
-
-
-
2、变更订单为订单失败，系统自动退还商品库存
-
-
-- 变更订单
-
Create TRIGGER t_order_updateState
-
ON dbo.shop_shopcarttb
-
FOR UPDATE
-
AS
-
BEGIN
-
SET NOCOUNT ON
-
-
if UPDATE (orderstate)
-
BEGIN
-
declare @orderstate int,@oldorderstate int
-
set @orderstate = (select orderstate from inserted)
-
set @oldorderstate = (select orderstate from deleted)
-
-
print '变更订单状态：由 ' + cast(@oldorderstate as varchar(5)) + ' 变更为 ' + cast(@orderstate as varchar(5))
-
-
if (@orderstate = 904)
-
begin
-
update dbo.shop_gift11tb
-
set giftnum = giftnum + 1 , giftchangenum = giftchangenum + 1
-
where giftid = (select giftid from inserted)
-
end
-
END
-
-
SET NOCOUNT OFF
-
END
-
-
-- 商品库存变更
-
Create TRIGGER t_gift_updateState
-
ON dbo.shop_gift11tb
-
FOR UPDATE
-
AS
-
BEGIN
-
-
if UPDATE (giftnum)
-
BEGIN
-
declare @newNum int, @oldNum int, @giftid varchar(20)
-
set @oldNum = (select giftnum from deleted)
-
select @newNum = giftnum, @giftid = giftid from inserted
-
print '变更商品(' + @giftid + ')库存，由 ' + cast(@oldNum as varchar(5)) + ' 变更为 ' + cast(@newNum as varchar(5))
-
END
-
-
END
-
GO
-
-
3、删除一个商家，系统自动删除该商家的登录帐号
-
-
-- 在商家表中设计Delete触发器
-
Create TRIGGER t_business_Delete
-
ON dbo.shop_businesstb
-
FOR DELETE
-
AS
-
BEGIN
-
SET NOCOUNT ON
-
-
DECLARE @businessname nvarchar(15)
-
SELECT @businessname = businessname FROM Deleted
-
print '删除了' + CAST(@@RowCount as varchar(5)) + '个商家，商家名称：' + @businessname
-
-
DELETE dbo.shop_adminusertb FROM dbo.shop_adminusertb
-
INNER JOIN Deleted ON shop_adminusertb.adminid = Deleted.busid
-
-
SET NOCOUNT OFF
-
END
-
Go
-
-
-- 在管理员表中设计Delete触发器
-
Create TRIGGER t_adminusertb_Delete
-
ON dbo.shop_adminusertb
-
AFTER DELETE
-
AS
-
BEGIN
-
DECLARE @adminid varchar(20),
-
@adminuser varchar(50),
-
@pass varchar(10),
-
@adminright varchar(10)
-
-
SELECT @adminid = adminid ,
-
@adminuser = adminuser,
-
@pass= pass,
-
@adminright= adminright
-
FROM Deleted
-
print '删除了' + CAST(@@RowCount as varchar(5)) + '个帐号'+ char(13) +
-
'用户名称：' + @adminuser + char(13) +
-
'用户名：' + @adminid + char(13) +
-
'密 码：' + @pass + char(13) +
-
'权 限：' + @adminright
-
END
-
GO
视频课 https://edu.csdn.net/course/play/7940
文章来源: aaaedu.blog.csdn.net，作者：tea_year，版权归原作者所有，如需转载，请联系作者。
原文链接：aaaedu.blog.csdn.net/article/details/52389433
【版权声明】本文为华为云社区用户转载文章，如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）