|
|
Posted by Erland Sommarskog on 06/20/74 11:51
Zvonko (zvonko_NOSPAM_@velkat.net) writes:
> I realised that this can not be done with trigger. Here is the deal. For
> one item you have six rows with different values for column optzalpak. I
> have to insert all six rows and then get SUM(optzalpak) for that
> particular item and update all six rows with it. And then another six
> rows for second item.
>
> I did it programatically (Java) but it takes too long to be done, so I am
> wondering if I can let the SQL server do the dirty work.
First of all the data model is dubious. It's certainly not normalised.
Pre-storing aggregated sums is sometimes necessary if there are large
volumes, but then you usually store them in a separate table. Or you
build an indexed view. Storing them in the same row, overwriting the
inserted value looks very funny.
Nevertheless, there is a trigger:
CREATE TRIGGER PODCAI_tri ON PODACI AFTER INSERT AS
DECLARE @sums TABLE (sifrob varchar(13) PRIMARY KEY,
optzalpak int)
INSERT @sums (sifrob, optzalpak)
SELECT sifrob, SUM(optzalpak)
FROM (SELECT sifrob, optzalpak = MAX(optzalpak)
FROM PODACI P
WHERE EXISTS (SELECT *
FROM inserted i
WHERE P.sifrob = i.sifrob)
AND NOT EXISTS (SELECT *
FROM inserted i
WHERE P.sifrob = i.sifrob
AND P.sifoj = i.sifoj)
GROUP BY sifrob
UNION ALL
SELECT sifrob, optzalpak
FROM inserted) AS x
GROUP BY sifrob
UPDATE PODACI
SET optzalpak = s.optzalpak
FROM PODACI P
JOIN @sums s ON P.sifrob = s.sifrob
This works if you insert one at a time, but you can also insert
six rows at a time:
INSERT INTO [PODACI]
SELECT '30300991', '01', '23.276.00', 1, 1, 1, 1, 1, 1,
'PUSCINE', 1, 1, 0, 4, 4, ''
UNION ALL
SELECT '30300991', '03', '23.276.00', 1, 1, 1, 2, 1, 1,
'ZAGREB', 1, 1, 0, 4, 4, ''
UNION ALL
SELECT '30300991', '05', '23.276.00', 1, 1, 1, 3, 1, 1,
'SPLIT', 1, 1, 0, 4, 4, ''
UNION ALL
SELECT '30300991', '07', '23.276.00', 0, 0, 0, 4, 0, 0,
'CAKOVEC', 1, 1, 0, 4, 4, ''
UNION ALL
SELECT '30300991', '09', '23.276.00', 1, 1, 1, 5, 1, 1,
'RIJEKA', 1, 1, 0, 4, 4, ''
UNION ALL
SELECT '30300991', '11', '23.276.00', 0, 0, 0, 6, 0, 0,
'OSIJEK', 1, 1, 0, 4, 4, ''
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|