Обсуждение: trigger is holding up the data
I have a database with multiple tables. I have a trigger set on the event table. When events occur a tuple is inserted into event and the trigger is activated. In the trigger program I also want to look up data in other tables based on the data inserted into the event table. Usually the data I want to see from the other tables does not exist until the trigger program has exited. How can I make the trigger activate only after all tables in the database have been changed. It would still trigger on new events, but it wouldn't run the program till all the tables that needed to be updated were updated. Thanks Nate
Why not make the trigger that performs the insert into event an AFTER trigger? Assuming that you don't need to modify the values, that should allow you to read any values that were just inserted/updated. If not, you should be able to split the triggers into both BEFORE and AFTER to achieve the results you want. Greg ----- Original Message ----- From: "Nate Haggard" <nate@wordplace.com> To: <pgsql-general@postgresql.org> Sent: Friday, December 28, 2001 6:13 PM Subject: [GENERAL] trigger is holding up the data > I have a database with multiple tables. I have a trigger set on the event > table. When events occur a tuple is inserted into event and the trigger is > activated. In the trigger program I also want to look up data in other > tables based on the data inserted into the event table. Usually the data I > want to see from the other tables does not exist until the trigger program > has exited. How can I make the trigger activate only after all tables in > the database have been changed. It would still trigger on new events, but > it wouldn't run the program till all the tables that needed to be updated > were updated. > > Thanks > Nate > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html >
I think I need to clarify. The trigger is set as AFTER. The trouble is that other tables are changed and the trigger occurs only after the update on event and not after the update on all the tables. If event was the last table updated by this program then a trigger AFTER event would be fine. I want the trigger to trigger after updates on the event table but not before data is written to all other tables in the database. Nate At 11:14 AM 12/31/2001 -0500, you wrote: >Why not make the trigger that performs the insert into event an AFTER >trigger? Assuming that you don't need to modify the values, that should >allow you to read any values that were just inserted/updated. If not, you >should be able to split the triggers into both BEFORE and AFTER to achieve >the results you want. > >Greg > >----- Original Message ----- >From: "Nate Haggard" <nate@wordplace.com> >To: <pgsql-general@postgresql.org> >Sent: Friday, December 28, 2001 6:13 PM >Subject: [GENERAL] trigger is holding up the data > > > > I have a database with multiple tables. I have a trigger set on the event > > table. When events occur a tuple is inserted into event and the trigger >is > > activated. In the trigger program I also want to look up data in other > > tables based on the data inserted into the event table. Usually the data >I > > want to see from the other tables does not exist until the trigger program > > has exited. How can I make the trigger activate only after all tables in > > the database have been changed. It would still trigger on new events, but > > it wouldn't run the program till all the tables that needed to be updated > > were updated. > > > > Thanks > > Nate > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/users-lounge/docs/faq.html > > > > >---------------------------(end of broadcast)--------------------------- >TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
On Wed, 2 Jan 2002, Nate Haggard wrote: > I think I need to clarify. The trigger is set as AFTER. The trouble is > that other tables are changed and the trigger occurs only after the update > on event and not after the update on all the tables. If event was the last > table updated by this program then a trigger AFTER event would be fine. I > want the trigger to trigger after updates on the event table but not before > data is written to all other tables in the database. The system has no way of understanding "data is written to all other tables in the database" in the above without help. Depending on your schema, you might be able to make a system that would do this (if you know that there must be rows in <n> other tables, you have some holding table that you keep track of the write information, and each table has a trigger that checks that until it sees that all the info is there and does something), but that depends on your app. You might also be able to get something like the effect you want from a deferred trigger. If you use create constraint trigger and set the it to initially deferred, the trigger will run at end of transaction. However, this isn't meant to be something users use and may change or disappear in the future.