MSSQL Delete trigger and multiple rows problem

[ChrisH2]

Not being a SQL expert i'm having great difficulty with trigger.
I am trying to update the total_value on the orderhdr table when a row or multiple rows is deleted from the orderline table.
I have included the tables, triggers and my delete statement below.

Please help its driving me mad. Seems like the answer is almost in reach.



CREATE TABLE [orderhdr] (
[unique_id] [int] IDENTITY (1, 1) NOT NULL ,
[po_ref] [int] NOT NULL ,
[total_value] [decimal](13, 2) NULL ,
)
GO

CREATE TABLE [orderline] (
[unique_id] [int] IDENTITY (1, 1) NOT NULL ,
[po_ref] [int] NOT NULL ,
[line_no] [smallint] NULL ,
[line_value] [decimal](13, 2) NULL ,
)
GO

ALTER TABLE [orderhdr] WITH NOCHECK ADD
CONSTRAINT [ord_key1] PRIMARY KEY CLUSTERED
(
[unique_id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [orderline] WITH NOCHECK ADD
CONSTRAINT [ordl_key1] PRIMARY KEY CLUSTERED
(
[unique_id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [orderhdr] WITH NOCHECK ADD
CONSTRAINT [ord_key2] UNIQUE NONCLUSTERED
(
[po_ref]
) ON [PRIMARY]

GO

ALTER TABLE [orderline] WITH NOCHECK ADD
CONSTRAINT [ordl_key2] UNIQUE NONCLUSTERED
(
[po_ref],
[line_no]
) ON [PRIMARY]
GO


CREATE TRIGGER delete_orderline
ON orderline
FOR DELETE
AS
UPDATE orderhdr SET
total_value = oh.total_value - d.line_value
FROM orderhdr oh
INNER JOIN deleted AS d
ON oh.po_ref = d.po_ref
GO




INSERT orderhdr VALUES(1,100)
INSERT orderline values (1,1,10)
INSERT orderline values (1,2,15)
INSERT orderline values (1,3,15)
INSERT orderline values (1,4,60)

SELECT * from orderhdr
SELECT * from orderline

unique_id po_ref total_value
--------- ------ -----------
1 1 100.00


unique_id po_ref line_no line_value
--------- ------ ------- ----------
1 1 1 10
2 1 2 15
3 1 3 15
4 1 4 60


DELETE orderline WHERE line_value = 15


SELECT * from orderhdr
SELECT * from orderline

unique_id po_ref total_value
--------- ------ -----------
1 1 85.00

unique_id po_ref line_no line_value
--------- ------ ------- ----------
1 1 1 10
4 1 4 60


total_value should be 70 can anyone tell me what I need to do with my trigger to correct this.

thanks.

 

[SQLDenis]

Sum it up

CREATE TRIGGER delete_orderline 
ON orderline
FOR DELETE
AS
	declare @Value [decimal](13, 2)
	select @Value = sum(line_value) from deleted
    
	UPDATE orderhdr SET
    total_value = oh.total_value - @Value
    FROM orderhdr oh
    INNER JOIN deleted AS d 
    ON oh.po_ref = d.po_ref
GO

you might need to account for multiple deletes for different op_ref's in that case you will need to build a subquery and join on that

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[ChrisH2]

Thanks for that. Thats solved the problem. I don't need to delete multiple po_ref and lines in this case.

 

[vongrunt]

Freakin' TT search... here it is: thread183-1122574.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)