Trigger on main table to update itself with detail totals

SQL Apprentice Question

I have a main table (Invoice) with a field (InvoiceTotal) that gets computed
from the sum of related detail records in another table (InvoiceDetail). If
a certain field in the Invoice table gets changed (Invoice.Version) then I
need it to re-calculate the summary field (Invoice.InvoiceTotal).

I would like to create a trigger on the Invoice table that fires only when
the Invoice.Version field is updated. However, I can't seem to get the
syntax correct to have table Invoice reference/update itself.


Here is what I am trying as a trigger on table Invoice. Any ideas where I
am going wrong or if there is another way to do this? I keep getting several
errors about " Invalid column name 'InvoiceID' "


IF UPDATE (Version)
BEGIN


UPDATE Invoice
SET Invoice.InvoiceTotal = (SELECT SUM(InvoiceDetail.TotalPrice) FROM
InvoiceDetail WHERE InvoiceDetail.InvoiceID = Invoice.InvoiceID AND
InvoiceDetail.Version = Invoice.Version)
FROM Invoice
INNER JOIN inserted on Invoice.InvoiceID = inserted.InvoiceID


END



Celko Answers
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.


>> I have a main [sic] table (Invoice) with a field [sic] (InvoiceTotal) that gets computed from the sum of related detail records [sic] in another table (InvoiceDetail). <<


Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; the goal is to remove all
redudancy from the data. That means we do not want computed columns in
the schema.


>> I would like to create a trigger on the Invoice table that fires only when


the Invoice.Version field [sic] is updated. <<

And now, procedural code in a declarative language, subverting the most
basic ideas and all the advantages of SQL.



>> where I am going wrong or if there is another way to do this? <<


1) Everywhere -- you are a textbook disaster!! Tom Johnston used your
schema in a great series of articles a few years ago that went into
details that you can Google.

2) With an RDBMS schema? Have you ever read anything? Had a course? A
mentor?


Here is a skeleton schema:


CREATE TABLE Invoices -- plural , unless there is just one!!
(invoice_nbr INTEGER NOT NULL PRIMARY KEY,
customer_id INTEGER NOT NULL,
purchase_date DATETIME NOT NULL,
..);


CREATE TABLE InvoicesDetails
(invoice_nbr INTEGER NOT NULL,
sku INTEGER NOT NULL
REFERENCES Inventory (sku),
PRIMARY KEY (invoice_nbr, sku),
item_qty INTEGER NOT NULL,
..);


CREATE TABLE Inventory
(sku INTEGER NOT NULL PRIMARY KEY,
item_description CHAR(25) NOT NULL,
unit_cost DECIMAL (12, 2) NOT NULL,
..);


Since we have an RDBMS and not a ^%$*@ punch card or mag tape file
system, we can use a VIEW to get totals! They will always be right,
do not have to be materialized until needed and will not be redundant.