触发器

触发器实例一：

GO
/****** 对象: Trigger [TR_annlee_BOM_ORDERS_AUTO_annlee_SEND_ORDERS]    脚本日期: 11/12/2008 17:22:36 ******/
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[TR_annlee_BOM_ORDERS_AUTO_annlee_SEND_ORDERS]'))
DROP TRIGGER [dbo].[TR_annlee_BOM_ORDERS_AUTO_annlee_SEND_ORDERS]

 

GO
/****** 对象: Trigger [dbo].[TR_annlee_BOM_ORDERS_AUTO_annlee_SEND_ORDERS]    脚本日期: 11/12/2008 17:22:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--订单触发器
CREATE TRIGGER [dbo].[TR_annlee_BOM_ORDERS_AUTO_annlee_SEND_ORDERS] ON [dbo].[annlee_bom_orders]
FOR INSERT, UPDATE
AS
/* 插入己付款的订单号 */
INSERT INTO annlee_send_orders (orderid)
    SELECT a.orderid
        FROM INSERTED a INNER JOIN DELETED b ON a.orderid=b.orderid
   where a.status = 1 AND b.status<>1 -- 己付款

触发器实例二：

GO
/****** 对象: Trigger [TR_annlee_RECE_STORAGE_AUTO_INSERT_annlee_GDM_ITEM_STORAGE]    脚本日期: 11/12/2008 17:23:49 ******/
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[TR_annlee_RECE_STORAGE_AUTO_INSERT_annlee_GDM_ITEM_STORAGE]'))
DROP TRIGGER [dbo].[TR_annlee_RECE_STORAGE_AUTO_INSERT_annlee_GDM_ITEM_STORAGE]

 

 

GO
/****** 对象: Trigger [dbo].[TR_annlee_RECE_STORAGE_AUTO_INSERT_annlee_GDM_ITEM_STORAGE]    脚本日期: 11/12/2008 17:23:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:   <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE TRIGGER [dbo].[TR_annlee_RECE_STORAGE_AUTO_INSERT_annlee_GDM_ITEM_STORAGE] ON [dbo].[annlee_rece_storage]
FOR INSERT,DELETE,UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra, result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

    -- Insert statements for trigger here

SELECT itemcode,itemcolor,itemsize,0 itemcounts INTO #TmpStorage FROM DELETED a WHERE NOT EXISTS(SELECT 1 FROM INSERTED b WHERE a.itemcode=b.itemcode AND a.itemcolor=b.itemcolor AND a.itemsize=b.itemsize)
UNION ALL
SELECT itemcode,itemcolor,itemsize,itemcounts FROM INSERTED

SELECT itemcode,itemcolor,itemsize,SUM(itemcounts) itemcounts INTO #Tmp2 FROM(
SELECT itemcode,itemcolor,itemsize,itemcounts FROM #TmpStorage
UNION ALL
SELECT c.itemcode,b.itemcolor,b.itemsize,-b.quantity
   FROM annlee_bom_orders a INNER JOIN annlee_bom_ordersitem b ON a.orderid=b.orderid
    INNER JOIN annlee_gdm_item c ON b.itemid=c.itemid
    INNER JOIN #TmpStorage d ON c.itemcode=d.itemcode AND b.itemcolor=d.itemcolor AND b.itemsize=d.itemsize
   WHERE a.status=1
) x
GROUP BY itemcode,itemcolor,itemsize

UPDATE annlee_gdm_item_storage SET itemcounts= b.itemcounts
   FROM annlee_gdm_item_storage a INNER JOIN #Tmp2 b ON a.itemcode=b.itemcode AND a.itemcolor=b.itemcolor AND a.itemsize=b.itemsize

INSERT INTO annlee_gdm_item_storage (itemcode,itemcolor,itemsize,itemcounts)
   SELECT itemcode,itemcolor,itemsize,itemcounts FROM #Tmp2 a WHERE NOT EXISTS (SELECT 1 FROM annlee_gdm_item_storage b WHERE a.itemcode=b.itemcode AND a.itemcolor=b.itemcolor AND a.itemsize=b.itemsize)
END

存储过程实例：

GO
/****** 对象: StoredProcedure [dbo].[update_pay_order_before_xx_minute]    脚本日期: 11/13/2008 12:20:21 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[update_pay_order_before_xx_minute]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[update_pay_order_before_xx_minute]

 

 

 

GO
/****** 对象: StoredProcedure [dbo].[update_pay_order_before_xx_minute]    脚本日期: 11/13/2008 12:20:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:   <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[update_pay_order_before_xx_minute]

@pMinute int

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

    -- Insert statements for procedure here
SELECT * INTO #TmpPayOrder FROM annlee_bom_pay_order WHERE DATEDIFF(MI,createtime,GETDATE()) > @pMinute

UPDATE annlee_gdm_item_storage SET itemcounts=itemcounts+b.quantity
   FROM annlee_gdm_item_storage a INNER JOIN #TmpPayOrder b ON a.itemcode=b.itemcode AND a.itemcolor=b.itemcolor AND a.itemsize=b.itemsize

DELETE FROM annlee_bom_pay_order FROM annlee_bom_pay_order a INNER JOIN #TmpPayOrder b ON a.id=b.id

END

转载于:https://www.cnblogs.com/zqmingok/archive/2009/05/22/1486923.html