How to use sql trigger

Triggers in sql are certain conditions that are triggered when operating on a certain table. You can use the CREATE statement to create a trigger, the DROP statement to delete the trigger, the ALTER statement to disable the trigger, etc.

Trigger

In SQL Server, it is a program that performs a certain operation on a certain table, triggers a certain condition, and is executed. A trigger is a special stored procedure.

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=&#39;JH&#39;+replace(replace(replace(convert(VARCHAR,getdate(),120),&#39;-&#39;,&#39;&#39;),&#39; &#39;,&#39;&#39;),&#39;:&#39;,&#39;&#39;) --进货单号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(&#39;[tempdb].[dbo].#PTable&#39;) IS NOT NULL --判断临时表#tmp是否存在,存在则删除
       DROP TABLE #PTable
      IF object_id(&#39;[tempdb].[dbo].#OTable&#39;) 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

This is an automatic procurement trigger. The main trigger conditions that need to be paid attention to are the role of the temporary table.

Use of triggers

Create triggers

CREATE TRIGGER tr_update_Stock

Delete triggers

DROP  TRIGGER tr_update_Stock

Disable

ALTER TABLE trig_example DISABLE TRIGGER trig1
GO

Recovery

ALTER TABLE trig_example ENABLE TRIGGER trig1
GO

Disable all triggers on a table

ALTER TABLE 你的表 DISABLE TRIGGER all

Enable all triggers on a table

ALTER TABLE 你的表 enable TRIGGER all

Disable all triggers on all tables

exec sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER all'

Enable all triggers on all tables

exec sp_msforeachtable 'ALTER TABLE ? enable TRIGGER all'

The above is the detailed content of How to use sql trigger. For more information, please follow other related articles on the PHP Chinese website!