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='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
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!