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

触发器使用项目案例1、添加一个商家系统自动增加一个商家帐号-- 在商家表中设计Insert触发器CREATE TRIGGER t_business_InsertON shop_businesstbAFTER INSERTAS BEGINDECLARE @businessname nvarchar(15)SELECT @businessname = businessname FROM Insertedprint '恭喜您，添加了一个商家，商家名称：' + @businessnameinsert into dbo.shop_adminusertb (adminid,adminuser,pass,adminright,admintype,busid,admindepart,mobilephone)select busid,businessname,'1111','20',12,busid,businessname,contactmobile from InsertedENDGO-- 在管理员表中设计Insert触发器Create TRIGGER t_adminusertb_InsertON dbo.shop_adminusertbAFTER INSERTAS BEGINDECLARE @adminid varchar(20),@adminuser varchar(50),@pass varchar(10),@adminright varchar(10)SELECT    @adminid = adminid ,          @adminuser = adminuser,          @pass= pass,          @adminright= adminrightFROM Insertedprint '添加了一个帐号'+ char(13) +'用户名称：' + @adminuser    +    char(13) + '用户名：' + @adminid +    char(13) +'密　码：' + @pass +    char(13) +'权　限：' + @adminrightEND2、变更订单为订单失败，系统自动退还商品库存-- 变更订单Create TRIGGER t_order_updateStateON dbo.shop_shopcarttb FOR UPDATEASBEGINSET NOCOUNT ONif UPDATE (orderstate)BEGINdeclare @orderstate int,@oldorderstate intset @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)beginupdate dbo.shop_gift11tb set giftnum = giftnum + 1 , giftchangenum = giftchangenum + 1 where giftid = (select giftid from inserted)endENDSET NOCOUNT OFFEND-- 商品库存变更Create TRIGGER t_gift_updateStateON dbo.shop_gift11tbFOR UPDATEASBEGINif UPDATE (giftnum)BEGINdeclare @newNum int, @oldNum int, @giftid varchar(20)set @oldNum = (select giftnum from deleted)select @newNum = giftnum, @giftid = giftid from insertedprint '变更商品(' + @giftid + ')库存，由 ' + cast(@oldNum as varchar(5)) + ' 变更为 ' + cast(@newNum as varchar(5))ENDENDGO3、删除一个商家，系统自动删除该商家的登录帐号-- 在商家表中设计Delete触发器Create TRIGGER t_business_DeleteON dbo.shop_businesstbFOR DELETEASBEGINSET NOCOUNT ONDECLARE @businessname nvarchar(15)SELECT @businessname = businessname FROM Deletedprint '删除了' + CAST(@@RowCount as varchar(5)) + '个商家，商家名称：' + @businessnameDELETE dbo.shop_adminusertb FROM dbo.shop_adminusertb INNER JOIN Deleted ON shop_adminusertb.adminid = Deleted.busidSET NOCOUNT OFFENDGo-- 在管理员表中设计Delete触发器Create TRIGGER t_adminusertb_DeleteON dbo.shop_adminusertbAFTER DELETEAS BEGINDECLARE @adminid varchar(20),@adminuser varchar(50),@pass varchar(10),@adminright varchar(10)SELECT    @adminid = adminid ,          @adminuser = adminuser,          @pass= pass,          @adminright= adminrightFROM Deletedprint '删除了' + CAST(@@RowCount as varchar(5)) + '个帐号'+ char(13) +     '用户名称：' + @adminuser    +    char(13) +      '用户名：' + @adminid +    char(13) +     '密　码：' + @pass +    char(13) +     '权　限：' + @adminrightENDGO