Triggers use data other triggers

Question

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 28-Dec-14 22:44pm

Kris_C#

Updated 28-Dec-14 23:39pm

v4

Add a Solution

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

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

BechirBenltaief · Answer 1 · 2014-12-30T01:34:00

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

[^]

BechirBenltaief · Answer 2 · 2015-01-01T06:48:00

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

Triggers use data other triggers

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0