触发器使用其他触发器的数据 [英] Triggers use data other triggers

问题描述

我有触发器问题，一个触发器更新数据列'net_worth_af_discount'，另一个触发器必须使用这个新数据并更新不同表中的另一个数据 - column - net_worth_all。代码如下所示：

  CREATE   TRIGGER  NET_WORTH_ORDERS  ON  POSITION_ORDERS 
 AFTER  INSERT ，更新 
  AS  
  BEGIN  
  IF  TRIGGER_NESTLEVEL（）>  1  
  RETURN  
 
  IF  更新（NET_WORTH_AF_DISCOUNT）
  DECLARE   @ID_ORDERS   INT ， @ NET_WORTH_AF_DISCOUNT   DECIMAL （ 9 ， 2 ）， @NET_WORTH   DECIMAL （ 9 ， 2 ）
 
  SELECT  
  @ ID_ORDERS  = I .ID_ORDERS，
  @ NET_WORTH_AF_DISCOUNT  = I.NET_WORTH_AF_DISCOUNT，
  @ NET_WORTH  = Z .NET_WORTH 
  FROM  INSE RTED I  JOIN  ORDERS O  ON  I.ID_ORDERS = O.ID_ORDERS 
 
 < span class =code-keyword> UPDATE  ORDERS  SET  NET_WORTH = @ NET_WORTH + @ NET_WORTH_AF_DISCOUNT  WHERE  ID_ORDERS = @ ID_ORDERS 
  END  
  GO  

所以我认为问题是因为数据不会改变，如果有人知道什么是错的请写。

解决方案

您的触发器必须如下所示，

使用DELETED表来检索旧值，并使用INSERTED表来获取最新值

 创建  TRIGGER  NET_WORTH_ORDERS  ON  POSITION_ORDERS 
 AFTER  INSERT ，更新 
  AS  
  BEGIN  
  IF  TRIGGER_NESTLEVEL（）>  1  
  RETURN  
 
  IF  更新（NET_WORTH_AF_DISCOUNT）
  BEGIN  
  UPDATE  O 
  SET  O.NET_WORTH = O.NET_WORTH  -  ISNULL（D.NET_WORTH_AF_DISCOUNT， 0 ）
  FROM  POSITION_ORDERS O 
  INNER   JOIN  DELETED D  ON  D.ID_ORDERS = O.ID_ORDERS 
 
 
 更新 O 
  SET  O.NET_WORTH = O.NET_WORTH + ISNULL（I .NET_WORTH_AF_DISCOUNT， 0 ）
  FROM  POSITION_ORDERS O 
  INNER   JOIN  INSERTED I  ON  I.ID_ORDERS = O.ID_ORDERS 
  END  
  END  
  GO  

但还有另一个解决方案更简单，然后使用触发器来更新列，这取决于另一列。创建表时使用Computed Columns。



这是链接



[ ^ ]

hi



这个例子对我来说很好，你能给我发表结构和简单数据吗。



希望帮助

Bechir

 创建 < span class =code-keyword> TABLE  POSITION_ORDERS 
（
 ID_POSITION_ORDERS  INT   IDENTITY （ 1 ， 1 ），
 ID_ORDERS  INT ，
 NET_WORTH_AF_DISCOUNT  DECIMAL （ 18 ， 2 ）
）
  GO  
  CREATE   TABLE  ORDERS 
（
 ID_ORDERS  INT ，
 NET_WORTH  DECIMAL （ 18 ， 2 ）
）
  GO  
  CREATE   TRIGGER  NET_WORTH_ORDERS  ON  POSITION_ORDERS 
 AFTER  INSERT ，更新 
  AS  
  BEGIN  
  IF  TRIGGER_NESTLEVEL（）>  1  
  RETURN  
 
  IF  更新（NET_WORTH_AF_DISCOUNT）
  BEGIN  
  UPDATE  O 
  SET  O.NET_WORTH = O.NET_WORTH  -  ISNULL（D.NET_WORTH_AF_DISCOUNT， 0 ）
  FROM  ORDERS O 
  INNER   JOIN  DELETED D  ON  D.ID_ORDERS = O.ID_ORDERS; 
 
 更新 O 
  SET  O.NET_WORTH = O.NET_WORTH + ISNULL（I.NET_WORTH_AF_DISCOUNT， 0 ）
  FROM  ORDERS O 
  INNER   JOIN  INSERTED I  ON  I.ID_ORDERS = O .ID_ORDERS 
  END  
  END  
  GO  

Hi,
I have problem with trigger, one trigger update data in column 'net_worth_af_discount' and the other trigger must use this new data and update another data in different table - column - net_worth_all. Code looks like this:

CREATE TRIGGER NET_WORTH_ORDERS ON POSITION_ORDERS
	AFTER INSERT, UPDATE
AS
BEGIN
	IF TRIGGER_NESTLEVEL() > 1
		RETURN
 
	IF UPDATE(NET_WORTH_AF_DISCOUNT)
		DECLARE @ID_ORDERS INT, @NET_WORTH_AF_DISCOUNT DECIMAL(9,2), @NET_WORTH DECIMAL(9,2)

	SELECT 
		@ID_ORDERS = I.ID_ORDERS, 
		@NET_WORTH_AF_DISCOUNT = I.NET_WORTH_AF_DISCOUNT,
		@NET_WORTH = Z.NET_WORTH
	FROM INSERTED I JOIN ORDERS O ON I.ID_ORDERS=O.ID_ORDERS

	UPDATE ORDERS SET NET_WORTH =  @NET_WORTH+@NET_WORTH_AF_DISCOUNT WHERE ID_ORDERS=@ID_ORDERS
END
GO

So I think problem is because data not change, if anyone knows what is wrong please write.

解决方案

Hi,

Your trigger must looks like this,
Use the DELETED table to retrieve old values, and INSERTED table to have newest one

CREATE TRIGGER NET_WORTH_ORDERS ON POSITION_ORDERS
    AFTER INSERT, UPDATE
AS
BEGIN
    IF TRIGGER_NESTLEVEL() > 1
        RETURN

    IF UPDATE(NET_WORTH_AF_DISCOUNT)
    BEGIN
        UPDATE O
        SET O.NET_WORTH =  O.NET_WORTH - ISNULL(D.NET_WORTH_AF_DISCOUNT, 0)
        FROM POSITION_ORDERS O
        INNER JOIN DELETED D ON D.ID_ORDERS=O.ID_ORDERS


        UPDATE O
        SET O.NET_WORTH =  O.NET_WORTH + ISNULL(I.NET_WORTH_AF_DISCOUNT,0)
        FROM POSITION_ORDERS O
        INNER JOIN INSERTED I ON I.ID_ORDERS=O.ID_ORDERS
    END
END
GO

But there is another solution more simple then using trigger to update columns depending from another columns. Use "Computed Columns" when you create your table.

This is the link

[^]

hi

this is exemple that work fine for me, can you send me table structure and simple data.

Hope that help
Bechir

CREATE TABLE POSITION_ORDERS
(
	ID_POSITION_ORDERS INT IDENTITY(1,1),
	ID_ORDERS INT,
	NET_WORTH_AF_DISCOUNT DECIMAL(18,2)
)
GO
CREATE TABLE ORDERS
(
	ID_ORDERS INT,
	NET_WORTH DECIMAL(18,2)
)
GO
CREATE TRIGGER NET_WORTH_ORDERS ON POSITION_ORDERS
    AFTER INSERT, UPDATE
AS
BEGIN
    IF TRIGGER_NESTLEVEL() > 1
        RETURN

    IF UPDATE(NET_WORTH_AF_DISCOUNT)
    BEGIN
        UPDATE O
        SET O.NET_WORTH =  O.NET_WORTH - ISNULL(D.NET_WORTH_AF_DISCOUNT, 0)
        FROM ORDERS O
        INNER JOIN DELETED D ON D.ID_ORDERS=O.ID_ORDERS;

        UPDATE O
        SET O.NET_WORTH =  O.NET_WORTH + ISNULL(I.NET_WORTH_AF_DISCOUNT,0)
        FROM ORDERS O
        INNER JOIN INSERTED I ON I.ID_ORDERS=O.ID_ORDERS
    END
END
GO

这篇关于触发器使用其他触发器的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！