trigger question

[Zvonko]

Hi!

I have a interesting problem. I need to write a trigger that wil go off
after every sixth row is inserted/updated.

CREATE TABLE [dbo].[PODACI] (
[sifrob] varchar(13) COLLATE Croatian_CI_AS NOT NULL,
[sifoj] varchar(2) COLLATE Croatian_CI_AS DEFAULT '',
[katbroj] varchar(15) COLLATE Croatian_CI_AS DEFAULT '',
[minzal] int DEFAULT 0,
[minzalpak] int DEFAULT 0,
[optzal] int DEFAULT 0,
[optzalpak] int DEFAULT 0,
[maxzal] int DEFAULT 0,
[maxzalpak] int DEFAULT 0,
[nazoj] varchar(15) COLLATE Croatian_CI_AS DEFAULT '',
[prodkol] int DEFAULT 0,
[pak] int DEFAULT '',
[pakvel] int DEFAULT '',
[optzaluk] int DEFAULT 0,
[optzalpakuk] int DEFAULT 0,
[ind] varchar(2) COLLATE Croatian_CI_AS DEFAULT ''
)

INSERT INTO [PODACI] VALUES
('30300991', '01', '23.276.00', 1, 1, 1, 1, 1, 1, 'PUSCINE', 1, 1, 0, 4,
4, '')
INSERT INTO [PODACI] VALUES
('30300991', '03', '23.276.00', 1, 1, 1, 1, 1, 1, 'ZAGREB', 1, 1, 0, 4, 4,
'')
INSERT INTO [PODACI]
VALUES
('30300991', '05', '23.276.00', 1, 1, 1, 1, 1, 1, 'SPLIT', 1, 1, 0, 4, 4,
'')
INSERT INTO [PODACI] VALUES
('30300991', '07', '23.276.00', 0, 0, 0, 0, 0, 0, 'CAKOVEC', 1, 1, 0, 4,
4, '')
INSERT INTO [PODACI] VALUES
('30300991', '09', '23.276.00', 1, 1, 1, 1, 1, 1, 'RIJEKA', 1, 1, 0, 4, 4,
'')
INSERT INTO [PODACI] VALUES
('30300991', '11', '23.276.00', 0, 0, 0, 0, 0, 0, 'OSIJEK', 1, 1, 0, 4, 4,
'')

Now, the trigger is triggered ................................

Trigger should get SUM(optzalpak) WHERE sifrob = '30300991' and update the
column optzalpakuk where sifrob = '30300991' with that value.
And insert statements go on .................................
Any ideas?
Thanks,
Zvonko

[Razvan Socol]

Zvonko wrote:

I have a interesting problem. I need to write a trigger that wil go off
after every sixth row is inserted/updated.
Why would you want to do that ?
Trigger should get SUM(optzalpak) WHERE sifrob = '30300991' and update the
column optzalpakuk where sifrob = '30300991' with that value.

You mean "SUM(optzalpak) FROM inserted" or "SUM(optzalpak) FROM PODACI"
?
In other words, you want the sum of the values inserted in that
statement or the sum of all values from the table (for the affected
"sifrob") ?

Razvan

[Zvonko]

"Razvan Socol" <rs****@gmail.com> wrote in message
news:11**********************@r2g2000cwb.googlegro ups.com...

You mean "SUM(optzalpak) FROM inserted" or "SUM(optzalpak) FROM PODACI"
?
In other words, you want the sum of the values inserted in that
statement or the sum of all values from the table (for the affected
"sifrob") ?

Razvan

I mean sum of all values from the table for the affected sifrob.

Zvonko

[Erland Sommarskog]

Zvonko (zv************@velkat.net) writes:

I have a interesting problem. I need to write a trigger that wil go
off after every sixth row is inserted/updated.

That is not really possible. For starters, a trigger fires once per
statement, so what if you insert 35 rows in one go?

I think you need to reformulate the requirement. What do you really
want to achieve?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Zvonko]

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn*********************@127.0.0.1...

Zvonko (zv************@velkat.net) writes:

I have a interesting problem. I need to write a trigger that wil go
off after every sixth row is inserted/updated.

That is not really possible. For starters, a trigger fires once per
statement, so what if you insert 35 rows in one go?

I think you need to reformulate the requirement. What do you really
want to achieve?

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.
Thanks,
Zvonko

[Razvan Socol]

What is the primary key of this table ?

Razvan

[Razvan Socol]

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

[Erland Sommarskog]

Zvonko (zv************@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

Erland Sommarskog (es****@sommarskog.se) writes:

Nevertheless, there is a trigger:

I forgot to mention: I assume that (sifrob, sifoj) is the primary key
of this table.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Razvan Socol]

Hello, Erland

I don't get it... why are you SUM-ming the MAX(optzalpak) for the older
rows, instead of the optzalpak itself ? That wasn't in the OP's
requirements...

Razvan

[Razvan Socol]

Also, why are you storing the result in optzalpak, instead of
optzalpakuk ?

Razvan

Razvan Socol wrote:

Hello, Erland

I don't get it... why are you SUM-ming the MAX(optzalpak) for the older
rows, instead of the optzalpak itself ? That wasn't in the OP's
requirements...

Razvan

[Erland Sommarskog]

Razvan Socol (rs****@gmail.com) writes:

Also, why are you storing the result in optzalpak, instead of
optzalpakuk ?

Because the names are in a foreign language that I don't know, so they were
just a meaningless combination of letters, and when two names are very
similar, I took them to be one and the same.

Believe me, the trigger performs the task as I misread the posting. I did
test it!

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[jim_geissman]

> > This works if you insert one at a time, but you can also insert

six rows at a time:/books.mspx

What if seven rows are inserted at one time? Does that count as one
insertion or seven as far as the requirements are concerned?