Convert Oracle Trigger to MS SQL Server 2000

I am new to MS SQL and trying to get the syntax for the following trigger
correct. The goal is to sum all ProjectSpec.ProjectSpecValues with a not
null ProjectSpec.ProjectValueFlag and update this sum to
ProjectInfo.SpecValueTotal. The Trigger I have written is for Oracle.
CREATE TRIGGER [ProjectSpecValueTotal] ON [dbo].[ProjectSpec]
FOR INSERT, UPDATE, DELETE
AS
UPDATE ProjectInfo SET ProjectInfo.SpecValueTotal =
(SELECT SUM(ProjectSpec.ProjectSpecValue)
FROM ProjectSpec
WHERE ProjectValueFlag IS NOT NULL
AND ProjectSpec.ProjectID = ProjectInfo.ProjectID
AND ProjectSpec.ProjectID = :NEW.ProjectID)
WHERE ProjectInfo.ProjectID = :NEW.ProjectID--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Use the inserted dataset. See the BOL article "Using the inserted and
deleted Tables" for more info.
Note: Most relational DB designers frown on putting calculated values
in tables, 'cuz it can cost so/too much to maintain (data layer). The
calculated value is, usually, only of interest when a query is run
(display layer). There are exceptions - accounting dbs: "closed"
months/years - it would be faster to read the monthly total from a pre
computed column than to read & calculate the column's data. Storage of
calculated values depends on your db usage (and willingness to violate
Normalization).
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQje7cIechKqOuFEgEQLJXgCfUlbviSdhvBND
2nMVBVCyxOiq+gAAoLzn
IpSs8fhllDl6qE91JzvF8DD3
=bizF
--END PGP SIGNATURE--
Jason wrote:
> I am new to MS SQL and trying to get the syntax for the following trigger
> correct. The goal is to sum all ProjectSpec.ProjectSpecValues with a not
> null ProjectSpec.ProjectValueFlag and update this sum to
> ProjectInfo.SpecValueTotal. The Trigger I have written is for Oracle.
> CREATE TRIGGER [ProjectSpecValueTotal] ON [dbo].[ProjectSpec]
> FOR INSERT, UPDATE, DELETE
> AS
> UPDATE ProjectInfo SET ProjectInfo.SpecValueTotal =
> (SELECT SUM(ProjectSpec.ProjectSpecValue)
> FROM ProjectSpec
> WHERE ProjectValueFlag IS NOT NULL
> AND ProjectSpec.ProjectID = ProjectInfo.ProjectID
> AND ProjectSpec.ProjectID = :NEW.ProjectID)
> WHERE ProjectInfo.ProjectID = :NEW.ProjectID|||Like the MGFoster said, I would frown upon such things, but you could
probably do something like:
CREATE TRIGGER [ProjectSpecValueTotal] ON [dbo].[ProjectSpec]
FOR INSERT, UPDATE, DELETE
AS
UPDATE ProjectInfo
SET ProjectInfo.SpecValueTotal =
(SELECT SUM(ProjectSpec.ProjectSpecValue)
FROM ProjectSpec
WHERE ProjectValueFlag IS NOT NULL
AND ProjectSpec.ProjectID = ProjectInfo.ProjectID
--I don't think you need join to the new value here
-- AND ProjectSpec.ProjectID = :NEW.ProjectID
)
WHERE ProjectInfo.ProjectID in (select inserted.projectId
from inserted
union all
select
deleted.projectId
from deleted)
This will recalculate the sum for any rows that have been either deleted,
updated, or inserted, since the key will show up in the inserted table for
insert and update, and in deleted for update or delete. You want to include
all rows, not just new ones, so I commented out the one row.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Jason" <Jason@.discussions.microsoft.com> wrote in message
news:534AA117-3A7C-49C9-9F7D-1FAACD4C2A9C@.microsoft.com...
>I am new to MS SQL and trying to get the syntax for the following trigger
> correct. The goal is to sum all ProjectSpec.ProjectSpecValues with a not
> null ProjectSpec.ProjectValueFlag and update this sum to
> ProjectInfo.SpecValueTotal. The Trigger I have written is for Oracle.
> CREATE TRIGGER [ProjectSpecValueTotal] ON [dbo].[ProjectSpec]
> FOR INSERT, UPDATE, DELETE
> AS
> UPDATE ProjectInfo SET ProjectInfo.SpecValueTotal =
> (SELECT SUM(ProjectSpec.ProjectSpecValue)
> FROM ProjectSpec
> WHERE ProjectValueFlag IS NOT NULL
> AND ProjectSpec.ProjectID = ProjectInfo.ProjectID
> AND ProjectSpec.ProjectID = :NEW.ProjectID)
> WHERE ProjectInfo.ProjectID = :NEW.ProjectID