|
|
Posted by Razvan Socol on 10/02/19 11:51
Razvan Socol wrote:
> What is the primary key of this table ?
On a second thought, I didn't really have to know this information,
although you should have a primary key in your table, and probably the
primary key is (sifrob, sifoj).
If you really want to use a trigger, you can update the sum for each
item, everytime a row is inserted/updated/deleted, regardless if it's
the sixth row or not:
CREATE TRIGGER Podaci_IUD_optzalpakuk ON dbo.PODACI
FOR INSERT, UPDATE, DELETE
AS
IF @@ROWCOUNT>0 BEGIN
SET NOCOUNT ON
UPDATE dbo.PODACI SET optzalpakuk=(
SELECT SUM(optzalpak) FROM dbo.PODACI b
WHERE b.sifrob=a.sifrob
)
FROM dbo.PODACI a WHERE (EXISTS (
SELECT * FROM inserted i
WHERE i.sifrob=a.sifrob
) OR EXISTS (
SELECT * FROM deleted d
WHERE d.sifrob=a.sifrob
)) AND optzalpakuk<>(
SELECT SUM(optzalpak) FROM dbo.PODACI b
WHERE b.sifrob=a.sifrob
)
END
However, there are better ways:
a) do not store the optzalpakuk in this table, but in a separate table,
which has only the sifrob column (as a PK) and the optzalpakuk column.
This would eliminate the violation of the second normal form, that you
have in your current table (read a book or an article on normalization,
for more informations about this).
b) do not store the optzalpakuk column at all, to eliminate an
unnecessary redundancy. Instead, use a view to compute this column:
CREATE VIEW PODACI_with_optzalpakuk AS
SELECT *, (
SELECT SUM(optzalpak) FROM PODACI b
WHERE b.sifrob=a.sifrob
) AS optzalpakuk
FROM PODACI a
Note: do not use * in production code; I have only used it here for the
purpose of brevity.
c) if you have millions of rows in this table, and SELECT performance
is much more important that INSERT/UPDATE/DELETE performance, you can
use an indexed view.
In most cases, I would go with option b).
Razvan
Navigation:
[Reply to this message]
|