Click here to Skip to main content
15,943,008 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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:
SQL
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.
Posted
Updated 28-Dec-14 23:39pm
v4
Comments
Kornfeld Eliyahu Peter 29-Dec-14 4:51am    
Updated your code to see the flow of it - it may help you understand your problem...
Also read about If in SQL: http://msdn.microsoft.com/en-us/library/ms182717(v=sql.110).aspx
Check carefully the part about SQL statemet vs SQL statement block!
Kris_C# 29-Dec-14 5:39am    
But I dont now what is wrong, for me is easy when NET_WORTH_AF_DISCOUNT is update by one trigger, next trigger see upadte and start update date in ORDERS.NET_WORTH

Hi,

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

SQL
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

[^]
 
Share this answer
 
Comments
Kris_C# 1-Jan-15 11:46am    
This not work,
I have two table ORDER and POSITION_ORDERS. One trigger update column POSITION_ORDERS.NET_WORTH_AF_DISCOUNT, another trigger must update ORDER.NET_WORTH when POSITION_ORDERS.NET_WORTH_AF_DISCOUNT is update by the first trigger. But I have all the time NULL. If any one know how to do it?
hi

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

Hope that help
Bechir

SQL
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
 
Share this answer
 
Comments
Kris_C# 1-Jan-15 16:57pm    
every think is ok, and my preposition is also good. But problem is when trigger upade data, becouse when I updata NET_WORTH_AF_DISCOUNT form query is work but when trigger do this, and another trriger used NET_WORTH_AF_DISCOUNT to updata NET_WORTH - there is null. I dont know why I tried sp_settriggerorder @triggername = 'NET_WORTH_ORDERS', @order= 'last', @stmttype= 'INSERT' or 'UPDATE' - but is not help

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900