MSSQL之十七触发器使用项目案例

触发器使用项目案例

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
MSSQL之十七 触发器使用项目案例

推荐阅读

MSSQL之十七触发器使用项目案例
