473,898 Members | 4,202 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Update inside (Insert) Trigger and Unique constraint...

I'm trying to build a table that will store a history of records
by enumerating the records. I want the newest record to always
be number ZERO, so I created a trigger on my table to handle the
assignment of version numbers:

CREATE TRIGGER "trg_audio_file _insert" BEFORE INSERT
ON "public"."audio _file" FOR EACH ROW
EXECUTE PROCEDURE "public"."trg_a udio_file_inser t"();

My trigger function looks like this...

CREATE FUNCTION "public"."trg_a udio_file_inser t" () RETURNS trigger AS'
BEGIN
...
/* rollback the version number of previous versions of this
audio_id */
UPDATE audio_file SET
afile_version = afile_version + 1
WHERE acct_id = NEW.acct_id
AND audio_id = NEW.audio_id;

/* newly inserted row is always the latest version ''0'' */
NEW.afile_versi on := 0;

...
/* yeah, that worked */
RETURN NEW;
END;
'LANGUAGE 'plpgsql';

There exists a unique constraint on the 'the audio_id / audio_version'
columns. However, when I insert records into this table, I'm getting an
error like:

duplicate key violates unique constraint "idx_audio_file _id_version"
CONTEXT: PL/pgSQL function "trg_audio_file _insert" line 18 at SQL
statement

I don't understand WHY there could be a violation of the constraint when
I clearly asked for the update to be performed prior to the assigning of
NEW.afile_versi on := 0;. Yes, there exist two records with my acct_id and
audio_id with versions 0 and 1 already. The update should roll them to
1 and 2 then the insert at 0 should be unique still.

Why isn't this working? What's the deal with ordering when it comes to
triggers? Is the update not performed when I tell it to?

Dante


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 22 '05 #1
2 6571
NEVERMIND... This is not a trigger problem. It's a unique
constraint problem... If I have a unique constraint on
a column like 'afile_version' , and want to do an update on
that column to add one to each number, is there a way to
add an 'ORDER BY' to the update?

UPDATE audio_file SET
afile_version = afile_version + 1
ORDER BY afile_version DESC;

???

The problem is that with

0 --> 1
1 --> 2
2 --> 3
insert 0

The update would update 0 to 1 and hit a constraint violation.
I needed to start from the bottom and work my way up...

2 --> 3
1 --> 2
0 --> 1
insert 0

So, I wrote a FOR LOOP like this:

FOR my_rec IN
SELECT afile_id
FROM audio_file
ORDER BY afile_version DESC
LOOP
/* roll back the version... */
UPDATE audio_file SET
afile_version = afile_version + 1
WHERE afile_id = my_rec.afile_id ;
END LOOP;

And that does the trick, but I guess I might also be able
to do something like this?:

UPDATE audio_file SET
afile_version = afile_version + 1
WHERE afile_id IN (
SELECT afile_id
FROM audio_file
ORDER BY afile_version DESC
);

Yeah, so I guess I figured this out on my own, but from a
performance viewpoint, would the second method be better
.... or the first? Does it matter?

Dante


D. Dante Lorenso wrote:
I'm trying to build a table that will store a history of records
by enumerating the records. I want the newest record to always
be number ZERO, so I created a trigger on my table to handle the
assignment of version numbers:

CREATE TRIGGER "trg_audio_file _insert" BEFORE INSERT
ON "public"."audio _file" FOR EACH ROW
EXECUTE PROCEDURE "public"."trg_a udio_file_inser t"();

My trigger function looks like this...

CREATE FUNCTION "public"."trg_a udio_file_inser t" () RETURNS trigger
AS'
BEGIN
...
/* rollback the version number of previous versions of this
audio_id */
UPDATE audio_file SET
afile_version = afile_version + 1
WHERE acct_id = NEW.acct_id
AND audio_id = NEW.audio_id;
/* newly inserted row is always the latest version ''0'' */
NEW.afile_versi on := 0;

...
/* yeah, that worked */
RETURN NEW;
END;
'LANGUAGE 'plpgsql';

There exists a unique constraint on the 'the audio_id / audio_version'
columns. However, when I insert records into this table, I'm getting an
error like:

duplicate key violates unique constraint "idx_audio_file _id_version"
CONTEXT: PL/pgSQL function "trg_audio_file _insert" line 18 at SQL
statement

I don't understand WHY there could be a violation of the constraint when
I clearly asked for the update to be performed prior to the assigning of
NEW.afile_versi on := 0;. Yes, there exist two records with my acct_id
and
audio_id with versions 0 and 1 already. The update should roll them to
1 and 2 then the insert at 0 should be unique still.

Why isn't this working? What's the deal with ordering when it comes to
triggers? Is the update not performed when I tell it to?

Dante


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 22 '05 #2
On Wednesday 21 January 2004 20:12, D. Dante Lorenso wrote:
NEVERMIND... This is not a trigger problem. It's a unique
constraint problem... If I have a unique constraint on
a column like 'afile_version' , and want to do an update on
that column to add one to each number, is there a way to
add an 'ORDER BY' to the update? [snip] FOR my_rec IN
SELECT afile_id
FROM audio_file
ORDER BY afile_version DESC
LOOP
/* roll back the version... */
UPDATE audio_file SET
afile_version = afile_version + 1
WHERE afile_id = my_rec.afile_id ;
END LOOP;
This was mentioned in the last couple of weeks on one of the lists - don't
know which. Someone suggested doing UPDATE ...version=-version followed by
UPDATE ...version=(-version)+1
And that does the trick, but I guess I might also be able
to do something like this?:
Nope - or rather, if it does work I think it's down to chance.
UPDATE audio_file SET
afile_version = afile_version + 1
WHERE afile_id IN (
SELECT afile_id
FROM audio_file
ORDER BY afile_version DESC
);


PS - this is really a bug, but it doesn't seem to bite very often, and there
are work-arounds, so it hasn't reached the top of any developer's list yet.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 22 '05 #3

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

Similar topics

8
8623
by: Jason | last post by:
I have a table that matches up Securities and Exchanges. Individual securities can belong on multiple exchanges. One of the columns, named PrimaryExchangeFlag, indicates if a particular exchange is the primary exchange for that symbol. Each symbol can only have one primary exchange. I am trying to write a insert/update/delete trigger that enforces this rule. The rules I have thought of are as follows: Insert If new row has flag...
2
8804
by: MAS | last post by:
Below is a simple UPDATE that I have to perform on a table that has about 2.5 million rows (about 4 million in production) This query runs for an enourmous amount of time (over 1 hour). Both the ChangerRoleID and the ChangerID are indexed (not unique). Is there any way to performance tune this? Controlling the physical drive of the log file isn't possible at our client sites (we don't have control) and the recovery model needs to be...
8
3358
by: Jan van Veldhuizen | last post by:
The UPDATE table FROM syntax is not supported by Oracle. I am looking for a syntax that is understood by both Oracle and SqlServer. Example: Table1: id name city city_id 1 john newyork null
3
7292
by: takilroy | last post by:
Hi, Does anyone know of a simple way to do this? I want to create an insert trigger for a table and if the record already exists based on some criteria, I want to update the table with the values that are passed in via the insert trigger without having to use all the 'set' statements for each field (so if we add fields in the future I won't have to update the trigger). In other words, I want the trigger code to look something like...
1
6308
by: Derek Erb | last post by:
SQL Server 2000 : I have a series of tables which all have the same structure. When any of these tables are modified I need to syncrhonise all of those modifications with one other table wich is a sort of merge of the individual tables with one extra column. For most of these tables this is not a problem. The problem arrives when one of the tables has an ntext column which obviously can not be used in an update or insert trigger. ...
8
6151
by: Paul Reddin | last post by:
Hi, We have been having many issues with unique constraints and we seem to have boiled it down to the equivalent of the following very simple scenario... given a table with a unqiue constraint on col1 and populated as follows col1 col2
5
5369
by: wpellett | last post by:
I can not get the SQL compiler to rewrite my SQL UPDATE statement to include columns being SET in a Stored Procedure being called from a BEFORE UPDATE trigger. Example: create table schema1.emp ( fname varchar(15) not null, lname varchar(15) not null, dob date,
13
6587
by: dennis | last post by:
Hello, I'm having trouble solving the following problem with DB2 UDB 8.2. I need to create a trigger that performs certain extra constraint validations (temporal uniqueness). One of the tables has no primary key. I'm having trouble expressing an update trigger that checks for existing rows, contemporary to the updated one, but exclusing the
3
5976
by: Wojto | last post by:
Hi there! I need to write a trigger that will check referential integrity of my data. I have few FOREIGN KEY constraints but, as You probably konow, the cannot be deferred (in the meaning of SQL 92 standard). So I decided to add NOCHECK CONSTRAINT ALL to the modified table and then run a trigger (after secon altertion of my table). But I cannot write a trigger for ALTER. I found something on msdn, byt their example doesn't work. To show...
0
9994
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
9842
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 synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11265
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
10859
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
10956
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,...
1
8036
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 instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5882
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
4712
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
3
3309
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 effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.