sql触发器怎么使用

sql中的触发器是对某个表进行操作时而触发的某种条件，可以用CREATE语句创建触发器，DROP语句删除触发器，ALTER语句禁用触发器等

触发器

在SQL Server里面也就是对某一个表的一定的操作，触发某种条件，从而执行的一段程序。触发器是一个特殊的存储过程。CREATE TRIGGER tr_update_Stock--自动采购触发器ON T_ProductFOR updateASIF (update(Stock)) --判断stock字段是否更新BEGIN--查询出库存低于下限的产品近一个月的消耗量 --创建临时表 IF object_id('[tempdb].[dbo].##table1') IS NOT NULL --判断临时表#tmp是否存在,存在则删除DROP TABLE ##table1SELECT * INTO ##table1 FROM (SELECT row_number() OVER (ORDER BY ProductID) AS rowNo,c.ZNumber - c.Stock AS purchaseNum,* FROM ( SELECTProductID,SUM(s.Numbers) ZNumber,p.Stock,SUM(s.OutMoney) ZMoney,p.PriceFROM [dbo].[T_StorageOut] t inner join[dbo].[T_StorageRelation] sON t.StorageOutID=s.StorageOutID inner join T_Product p ON s.ProductID=p.Id WHERE t.MakerTime between Convert(VARCHAR,DATEADD(MONTH,-1,GETDATE()),23) and Convert(VARCHAR,GETDATE(),23) and ProductID in (SELECT Id FROM V_ProductSupplierInfo where (Stock-StockLowerLimit)<0) GROUP BY ProductID,p.Stock,p.Price) c) ddd--计算用参数DECLARE @purchaseNum INT,@Price NVARCHAR(50),@totalMoney NVARCHAR(50),@ZpurchaseTotal INT --采购数量,单价,总金额,总数量SELECT @totalMoney = 0,@purchaseNum = 0,@Price = 0,@ZpurchaseTotal=0 --采购单所需参数DECLARE @PurchaseNumber NVARCHAR(50),@DopurchaseId INT,@DopurchaseTime NVARCHAR(200),@PurchaseTotal INT,@PurchasePrice NVARCHAR(200),@PurchaseState INT,@PurchaseType INTSET @PurchaseNumber='JH'+replace(replace(replace(convert(VARCHAR,getdate(),120),'-',''),' ',''),':','') --进货单号JH20171031092322--采购单详情所需参数 （产品id，产品采购价格）DECLARE @StockProductId INT,@DPurchasePrice NVARCHAR(20)--循环用参数DECLARE @curIndex INT,@rowCount INT--判断用参数DECLARE @OTCount INT,@PTCount INTSET @curIndex = 1SELECT @rowCount = COUNT(1) FROM ##table1IF(@rowCount>0)BEGINWHILE @curIndex <= @rowCountBEGIN--查询出第一行数据的采购数量，单价，产品idSELECT @purchaseNum = purchaseNum,@Price = Price,@StockProductId =ProductID FROM ##table1 WHERE rowNo = @curIndex--采购总金额SET @totalMoney = @totalMoney + @purchaseNum * CONVERT (INT,@price)--采购总数量SET @ZpurchaseTotal =@ZpurchaseTotal + @purchaseNum--单个产品的采购金额SET @DPurchasePrice=@purchaseNum * CONVERT (INT,@price)IF object_id('[tempdb].[dbo].#PTable') IS NOT NULL --判断临时表#tmp是否存在,存在则删除DROP TABLE #PTableIF object_id('[tempdb].[dbo].#OTable') IS NOT NULL --判断临时表#tmp是否存在,存在则删除DROP TABLE #OTable--根据产品id查询出包含该产品的采购单已全部审批SELECT * INTO #PTable FROM (SELECT PurchaseState FROM [dbo].[T_Purchase_Order] WHERE PurchaseNumber in(SELECT PurchaseOrderId FROM[dbo].[T_Purchase_OrderDetails] WHERE StockProductId=@StockProductId) and PurchaseState=0) AS a--根据产品id查询出包含该产品的订单已全部入库SELECT * INTO #OTable FROM (SELECT OrderState FROM T_Order WHERE OrderNumber in(SELECT OrderNumber FROMT_OrderDetails WHERE ProductId=@StockProductId) and OrderState=0) AS cSELECT@PTCount = COUNT(1) FROM #PTableSELECT@OTCount = COUNT(1) FROM #OTableIF (@PTCount=0) --已全部审批BEGINIF(@OTCount=0) --=0表示包含该产品的订单均已入库，可以生成新的BEGININSERT INTO [dbo].[T_Purchase_OrderDetails] VALUES(@PurchaseNumber,@StockProductId,@purchaseNum,@DPurchasePrice)ENDENDSET @curIndex = @curIndex + 1ENDENDset @DopurchaseId = 16646 --自动生成set @DopurchaseTime=Convert(NVARCHAR,getdate(),23)--当前时间 2017-10-31set @PurchaseTotal =@ZpurchaseTotalset @PurchasePrice =@totalMoneyset @PurchaseState =0 --未审核set @PurchaseType = 2 --自动生成--变量赋值完成，对采购单做添加操作IF (@PTCount=0)BEGININSERT INTO T_Purchase_Order VALUES(@PurchaseNumber,@DopurchaseId,@DopurchaseTime,@PurchaseTotal,@PurchasePrice,@PurchaseState,@PurchaseType)ENDEND这是一个自动采购的触发器，主要需要注意的触发条件，以及临时表的作用。

触发器的使用

创建触发器CREATE TRIGGER tr_update_Stock删除触发器 DROPTRIGGER tr_update_Stock禁用ALTER TABLE trig_example DISABLE TRIGGER trig1GO恢复ALTER TABLE trig_example ENABLE TRIGGER trig1GO禁用某个表上的所有触发器ALTER TABLE 你的表 DISABLE TRIGGER all启用某个表上的所有触发器ALTER TABLE 你的表 enable TRIGGER all禁用所有表上的所有触发器exec sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER all'启用所有表上的所有触发器exec sp_msforeachtable 'ALTER TABLE ? enable TRIGGER all'以上就是sql触发器怎么使用的详细内容，更多请关注小潘博客其它相关文章！