怎么使用sql觸發器

本篇文章給大家分享的是有關怎么使用sql觸發器，小編覺得挺實用的，因此分享給大家學習，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著小編一起來看看吧。

  sql中的觸發器是對某個表進行操作時而觸發的某種條件，可以用CREATE語句創建觸發器，DROP語句刪除觸發器，ALTER語句禁用觸發器等

觸發器

在SQL Server里面也就是對某一個表的一定的操作，觸發某種條件，從而執行的一段程序。觸發器是一個特殊的存儲過程。

CREATE TRIGGER tr_update_Stock   --自動采購觸發器
ON T_Product
FOR update
AS
IF (update(Stock)) --判斷stock字段是否更新
BEGIN
  --查詢出庫存低于下限的產品近一個月的消耗量
 --創建臨時表
 IF object_id('[tempdb].[dbo].##table1') IS NOT NULL --判斷臨時表#tmp是否存在,存在則刪除
    DROP TABLE ##table1
            SELECT * INTO ##table1 FROM (SELECT row_number() OVER (ORDER BY ProductID) AS rowNo,c.ZNumber - c.Stock AS purchaseNum,* FROM (
 SELECT  ProductID,SUM(s.Numbers) ZNumber,p.Stock,SUM(s.OutMoney) ZMoney,p.Price 
 FROM [dbo].[T_StorageOut] t inner join[dbo].[T_StorageRelation] s 
 ON 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 INT
   SET @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 INT    
SET @curIndex = 1
SELECT @rowCount = COUNT(1) FROM ##table1
IF(@rowCount>0)
BEGIN
   WHILE @curIndex <= @rowCount
   BEGIN
      --查詢出第一行數據的采購數量，單價，產品id
      SELECT @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 #PTable
      IF 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 FROM  T_OrderDetails WHERE ProductId=@StockProductId) and OrderState=0) AS c
      SELECT  @PTCount = COUNT(1) FROM #PTable
      SELECT  @OTCount = COUNT(1) FROM #OTable
          IF (@PTCount=0) --已全部審批
          BEGIN
             IF(@OTCount=0) --=0表示包含該產品的訂單均已入庫，可以生成新的
              BEGIN
               INSERT INTO [dbo].[T_Purchase_OrderDetails] VALUES(@PurchaseNumber,@StockProductId,@purchaseNum,@DPurchasePrice)
              END
          END
      SET @curIndex = @curIndex + 1
   END
END
  set @DopurchaseId = 16646 --自動生成
  set @DopurchaseTime=Convert(NVARCHAR,getdate(),23)   --當前時間 2017-10-31
  set @PurchaseTotal =@ZpurchaseTotal
  set @PurchasePrice =@totalMoney
  set @PurchaseState =0 --未審核
  set @PurchaseType = 2 --自動生成 
  --變量賦值完成，對采購單做添加操作
   IF (@PTCount=0)
   BEGIN
    INSERT INTO T_Purchase_Order VALUES(@PurchaseNumber,@DopurchaseId,@DopurchaseTime,@PurchaseTotal,@PurchasePrice,@PurchaseState,@PurchaseType)
   END
END

這是一個自動采購的觸發器，主要需要注意的觸發條件，以及臨時表的作用。

觸發器的使用

創建觸發器

CREATE TRIGGER tr_update_Stock

刪除觸發器

DROP  TRIGGER tr_update_Stock

禁用

ALTER TABLE trig_example DISABLE TRIGGER trig1
GO

恢復

ALTER TABLE trig_example ENABLE TRIGGER trig1
GO

禁用某個表上的所有觸發器

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觸發器，小編相信有部分知識點可能是我們日常工作會見到或用到的。希望你能通過這篇文章學到更多知識。更多詳情敬請關注億速云行業資訊頻道。