469,313 Members | 2,550 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,313 developers. It's quick & easy.

trigger question

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


Jun 26 '06 #1
12 1290
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

Jun 26 '06 #2

"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
Jun 26 '06 #3
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
Jun 26 '06 #4

"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
Jun 27 '06 #5
What is the primary key of this table ?

Razvan

Jun 27 '06 #6
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

Jun 27 '06 #7
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
Jun 27 '06 #8
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
Jun 27 '06 #9
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

Jun 28 '06 #10
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


Jun 28 '06 #11
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
Jun 28 '06 #12
> > 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?

Jun 28 '06 #13

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Matik | last post: by
6 posts views Thread by Scott CM | last post: by
2 posts views Thread by robert | last post: by
5 posts views Thread by Bob Stearns | last post: by
3 posts views Thread by ChrisN | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.