Shulou(Shulou.com)05/31 Report--

This article is about how sql triggers are used. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Triggers in sql are certain conditions triggered when operating on a table. You can use CREATE statements to create triggers, DROP statements to delete triggers, ALTER statements to disable triggers, and so on.

Trigger

In SQL Server, that is, a certain operation on a table, triggering a certain condition, and thus the execution of a program. A trigger is a special stored procedure.

CREATE TRIGGER tr_update_Stock-automatic purchase trigger

ON T_Product

FOR update

AS

IF (update (Stock))-determines whether the stock field is updated

BEGIN

-- find out the consumption of products with inventory below the lower limit for nearly a month

-- create temporary tables

IF object_id ('[tempdb]. [dbo]. # # table1') IS NOT NULL-determines whether temporary table # tmp exists or deletes if it exists

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)

BEGIN

WHILE @ curIndex

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.