473,583 Members | 2,858 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 1404
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(optzal pak) FROM inserted" or "SUM(optzal pak) 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.c om> wrote in message
news:11******** **************@ r2g2000cwb.goog legroups.com...
You mean "SUM(optzal pak) FROM inserted" or "SUM(optzal pak) 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****@sommarsk og.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****@sommars kog.se> wrote in message
news:Xn******** *************@1 27.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_optz alpakuk 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.sifr ob
)
FROM dbo.PODACI a WHERE (EXISTS (
SELECT * FROM inserted i
WHERE i.sifrob=a.sifr ob
) OR EXISTS (
SELECT * FROM deleted d
WHERE d.sifrob=a.sifr ob
)) AND optzalpakuk<>(
SELECT SUM(optzalpak) FROM dbo.PODACI b
WHERE b.sifrob=a.sifr ob
)
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_opt zalpakuk AS
SELECT *, (
SELECT SUM(optzalpak) FROM PODACI b
WHERE b.sifrob=a.sifr ob
) 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****@sommarsk og.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****@sommars kog.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****@sommarsk og.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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
8081
by: Joel Thornton | last post by:
Whenever something is inserted to a given table, I want to run some shell commands using xp_cmdshell. Would it be a bad idea to put this xp_cmdshell in the INSERT trigger of this table? I understand that when using xp_cmdshell, the sql thread in question waits until xp_cmdshell finishes what it's doing. Does this mean if my xp_cmdshell...
1
1995
by: Matik | last post by:
Hello to all, I have a small question. I call the SP outer the DB. The procedure deletes some record in table T1. The table T1 has a trigger after delete. This is very importand for me, that the SP will be finished ASAP, that's why, I do not want, and I do not need to wait for a trigger.
6
6543
by: Scott CM | last post by:
I have a multi-part question regarding trigger performance. First of all, is there performance gain from issuing the following within a trigger: SELECT PrimaryKeyColumn FROM INSERTED opposed to: SELECT * FROM INSERTED
2
2734
by: robert | last post by:
typed this into the ibm.com search window, but didn't get anything that looked like it would answer a question: +trigger +faster +db2 +cobol the question: are DB2 (390/v6, at the moment) triggers better or worse (and how much so, of course) than the same functionality coded in COBOL? assuming, of course, equal competence in the code.
0
7132
by: Dave Sisk | last post by:
I've created a system or external trigger on an AS/400 file a.k.a DB2 table. (Note this is an external trigger defined with the ADDPFTRG CL command, not a SQL trigger defined with the CREATE TRIGGER statement.) I've also defined a SQL stored proc, and the trigger is set to call this SP. I've posted the simplified source below. I can...
5
3287
by: Bob Stearns | last post by:
I have two (actually many) dates in a table I want to validate on insertion. The following works in the case of only one WHEN clause but fails with two (or more), with the (improper? inappropriate?) error message: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: CREATE TRIGGER IS3.date_later_001i NO C;BEGIN-OF-STATEMENT;<space> which is...
3
4939
by: ChrisN | last post by:
Hello all, I have a quick question. I'm using a C# object to commit new rows to a database. In the database I have an INSERT Trigger watching values come in. If the record to be committed fails the trigger's test, the trigger rolls back the INSERT command and no changes are made to the database. As far as my object is concerned, the...
3
3717
by: teddysnips | last post by:
I need a trigger (well, I don't *need* one, but it would be optimal!) but I can't get it to work because it references ntext fields. Is there any alternative? I could write it in laborious code in the application, but I'd rather not! DDL for table and trigger below. TIA
9
9301
by: Ots | last post by:
I'm using SQL 2000, which is integrated with a VB.NET 2003 app. I have an Audit trigger that logs changes to tables. I want to apply this trigger to many different tables. It's the same trigger, with the exception of the table name. I could manually change the table name in the trigger and create it, over and over, but I'd like to automate...
10
3559
by: JohnO | last post by:
Hi All, This question is related to iSeries V5R4 and db2. I want to implement an AFTER DELETE trigger to save the deleted rows to an archive table, I initially defined it as a FOR EACH STATEMENT trigger that would insert all the deleted rows in one operation like this: CREATE TRIGGER MyTable_TD
0
7811
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8159
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8185
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6571
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5689
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5366
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3811
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
1416
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1147
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.