473,698 Members | 2,082 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 1413
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
8088
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 call takes 30 seconds, that nobody else can insert to this table until my xp_cmdshell and rest of the...
1
2001
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
6551
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
2738
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
7143
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 manually call the stored proc, and the external trigger is created without any errors. However, when I do...
5
3294
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 interpreted as: An unexpected token "CREATE TRIGGER IS3.date_later_001i NO C" was found
3
4947
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 transaction went through either way (no matter what the trigger did). What I need is for the object...
3
3725
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
9308
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 this - by iterating through the collection of tables and passing the tablename to something that...
10
3565
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
8671
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9152
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. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9016
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8887
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8856
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 choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7709
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 launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
4360
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 the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3037
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2321
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.