473,287 Members | 1,419 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Triggers per transaction, workaround? prospects?

Hi,

I don't suppose that the todo item:

Referential Integrity
o Add deferred trigger queue file (Jan)

Means that there will be a statement like:

CREATE TRIGGER ... FOR EACH TRANSACTION

?

I frequently encounter situations where the
database is only 'good' when all the the statements
in the transaction have completed. (Duh, isn't this
the _point_ of transactions?) The latest is I want
a per foreign key sequence number column, 1, 2, 3, etc., say,
a per-person counter, which must not contain any 'gaps'. I can do this
so long as nobody every makes any mistakes
in sequencing, but once the sequence numbers are in place
there's no way to re-order the rows in a sequence
(the rows belonging to one person) without deleting
and re-creating all the rows with sequence numbers
= the first mis-placed sequence number. Given the

existance of other rows which reference the sequenced rows,
this is not a pretty picture. It'd be nice to be able to
put a series of UPDATE statements in a transaction
and have a trigger check the state of the database when the
transaction commits. (And be able to raise an exception
if the rules are violated.)

Has anybody else encountered problems like this and come up
with any solutions?

Stupid question:
If triggers automatically created to support REFERENCES
constraints can be deferred to execute on transaction commit,
and see the results of the statements executed prior to
the COMMIT, then why can't this be done for regular
triggers?

Regards,

Karl <ko*@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #1
2 2273
Karl O. Pinc wrote:
a per-person counter, which must not contain any 'gaps'. I can do this
so long as nobody every makes any mistakes
in sequencing, but once the sequence numbers are in place
there's no way to re-order the rows in a sequence
(the rows belonging to one person) without deleting
and re-creating all the rows with sequence numbers


I can't answer your particular question, but you can always put an ON
UPDATE CASCADE into your foreign keys... then if you *shudder* had to
change those values, it should cascade out through all the attached rows
in other tables.

Greg

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #2

On 2004.02.29 08:11 Gregory Wood wrote:
Karl O. Pinc wrote:
a per-person counter, which must not contain any 'gaps'. I can do
this
so long as nobody every makes any mistakes
in sequencing, but once the sequence numbers are in place
there's no way to re-order the rows in a sequence
(the rows belonging to one person) without deleting
and re-creating all the rows with sequence numbers


I can't answer your particular question, but you can always put an ON
UPDATE CASCADE into your foreign keys... then if you *shudder* had to
change those values, it should cascade out through all the attached
rows in other tables.


Thanks for the reply, but that doesn't really help. It's not the
key I want to change, but the sequence number sequenced on a foreign
key. I want to write a trigger to ensure the sequence numbers
are per-foreign-key sequential, Paulovie Michal wants to have the system
generate the sequential (e.g. per-person) numbers.

Assume fkey is a foreign key (say person id) and seq is the sequence
column always starting with 1. Then the trigger code

PERFORM SELECT larger.fkey, larger.id
FROM table AS smaller, table AS larger
WHERE larger.id > 1
AND larger.fkey = smaller.fkey
AND larger.seq = smaller.seq + 1
IF FOUND THEN
...

_would_ check for sequentiality, if only it could be run when the
transaction completes. Otherwise, you can't update the seqeunce
numbers to fix mistakes. :-(

Of course, you'd also want the above code to run against those
rows that have changed, not the whole table. But I don't care
(much) as my database isn't that large. (A virtual NEW table would be
nice, with new rows. And maybe a corresponding OLD table. Ah-lah
sybase triggers.)

Would Paulovie Michal be able to store the next available sequence
on the (for example) person row and then use serializeable transactions
and a little BEFORE insert trigger function of his own that updates the
person
row in question and uses the value to alter the sequence number
on the inserted row? Or is postfix's serialization not up to this?

What I realy want to know is if per-transaction triggers are
anywhere in my future. (O'Reilly's SQL In A Nutshell says
that that's how PostgreSQL triggers works, but they're wrong.
:-( )

Thanks,

Karl <ko*@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein

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

Nov 23 '05 #3

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

Similar topics

4
by: Hank | last post by:
I have two SQL Server 2000 machines (server_A and server_B). I've used sp_addlinkedserver to link them both, the link seems to behave fine. I can execute remote queries and do all types of neat...
11
by: raulgz | last post by:
I need audit triggers that change columns value in the same record that fire trigger. I need how to do.. Thanks..
2
by: M Wells | last post by:
Hi All, This seems like a tricky question to me. I have a Stored Procedure that encapsulates a number of updates to various tables within a transaction. However, at a later part of the...
3
by: Jonathan Bishop | last post by:
Hi. We are using MSDE2000 on a Point of Sale application. We need to keep a copy of a few key tables as up to date as possible for backup purposes. We are looking at using triggers over the...
2
by: JA | last post by:
Hi, Newbie here. I have a mailing list program that I really like. I also have a new membership program. The membership program has mailing list signups built-in, but it isn't nearly as robust...
4
by: Re_endian_miss | last post by:
I know even less about databases than I thought... I always thought that the idea behind a transaction was that the actual table in question does NOT get updated until a commit is issued (either...
0
by: r0cboff | last post by:
Hi there, This is my first time posting in here and I'm hoping somebody can point out where I am going wrong? I am currently trying to use Bitemporal tables. By this I mean a table with a valid...
0
debasisdas
by: debasisdas | last post by:
This thread contains some useful tips/sample codes regarding TRIGGERS in oracle, that the forum members may find useful. TRIGGERS: =============== Database trigger is a PL/SQL block that is...
5
by: Ben | last post by:
Hi! I have a trigger created for Customer table. My front-end is access. What is the best approach to handle a trigger result when adding a new customer record? Below is the trigger script:...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

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.