Thread: Trigger Question
Greetings: Is there any way to pass dynamic data into a trigger function? I think the answer is no, but just in case... TIA -- Work: 1-336-372-6812 Cell: 1-336-363-4719 email: terry@esc1.com
On Wed, Jan 05, 2005 at 02:56:27PM -0500, Terry Lee Tucker wrote: > > Is there any way to pass dynamic data into a trigger function? I think the > answer is no, but just in case... What do you mean by "dynamic data"? What problem are you trying to solve? -- Michael Fuhr http://www.fuhr.org/~mfuhr/
When I assign a value to a certain column in a table A, I want to be able to assign a value in table A to table B and then to assign a value in table B to table C. I was wanting to do this from the trigger level in an AFTER UPDATE trigger to ensure it gets done. I would like to be able to pass the serial keys of the associated records in table B and C to the AFTER UPDATE trigger so I could find the records in tables B and C and perform the updates. Does that make sense? Thanks for the reply... On Wednesday 05 January 2005 03:18 pm, Michael Fuhr saith: > On Wed, Jan 05, 2005 at 02:56:27PM -0500, Terry Lee Tucker wrote: > > Is there any way to pass dynamic data into a trigger function? I think > > the answer is no, but just in case... > > What do you mean by "dynamic data"? What problem are you trying > to solve? > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ -- Work: 1-336-372-6812 Cell: 1-336-363-4719 email: terry@esc1.com
On Wed, Jan 05, 2005 at 03:25:08PM -0500, Terry Lee Tucker wrote: > When I assign a value to a certain column in a table A, I want to be able to > assign a value in table A to table B and then to assign a value in table B to > table C. What do you mean by "assign a value to a certain column"? Are you updating an existing record in A? Inserting a new record into A? Either? What do you mean by "assign a value in table A to table B"? Do you want to update an existing record in B with some value from the new record in A? Or do you want to insert a new record into B based on the new record in A? Or something else? Perhaps an example would clarify the requirement: please show what A, B, and C look like in their "before" state; then show the statement that should execute the trigger (INSERT, UPDATE, etc.); then describe what should happen in A, B and C; then show A, B, and C in their "after" state. > I was wanting to do this from the trigger level in an AFTER UPDATE > trigger to ensure it gets done. I would like to be able to pass the serial > keys of the associated records in table B and C to the AFTER UPDATE trigger > so I could find the records in tables B and C and perform the updates. This part isn't clear either. Where are the "serial keys of the associated records in table B and C" being passed from? How are those records identified? What updates are supposed to be peformed in the AFTER UPDATE trigger? As I mentioned above, an example might explain things better. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
1) INSERT INTO logs (carr_code, ..., ..., ...) VALUES('ABCDEFG', ..., ...);
logs is table A in my question
2) logs_insert fires (This is a AFTER INSERT trigger)
3) in this trigger, I need to do the following:
update avlds set carr_code = new.carr_code where avlds.recid = ??;
avlds is table B in my question
The questions marks indicate the first piece of data that I want to
dynamically pass to the trigger.
4) in the same trigger:
update tract set order_num = avlds.order_num where tract.recid = ??;
tract is table C in my question
The question marks refer to the second piece of data that I want to pass
dynamically into the trigger.
In other terms, when the user creates a logs record that assigns a truck to a
load, I need to update specific rows in two other tables, avlds, and tract. I
was wanting to do this from the trigger level, but I will need the unique
serial key of each of the existing records in avlds and tract. These two keys
comprise the "dynamic" part of the question. I was just wondering if there is
some way of passing dynamic data into a trigger. I don't believe there is,
but I thought I would ask.
Thanks for the input.
Work: 1-336-372-6812
Cell: 1-336-363-4719
email: terry@esc1.com
Terry Lee Tucker wrote:
> 1) INSERT INTO logs (carr_code, ..., ..., ...) VALUES('ABCDEFG', ..., ...);
> logs is table A in my question
>
> 2) logs_insert fires (This is a AFTER INSERT trigger)
>
> 3) in this trigger, I need to do the following:
> update avlds set carr_code = new.carr_code where avlds.recid = ??;
> avlds is table B in my question
> The questions marks indicate the first piece of data that I want to
> dynamically pass to the trigger.
>
> 4) in the same trigger:
> update tract set order_num = avlds.order_num where tract.recid = ??;
> tract is table C in my question
> The question marks refer to the second piece of data that I want to pass
> dynamically into the trigger.
>
> In other terms, when the user creates a logs record that assigns a truck to a
> load, I need to update specific rows in two other tables, avlds, and tract. I
> was wanting to do this from the trigger level, but I will need the unique
> serial key of each of the existing records in avlds and tract. These two keys
> comprise the "dynamic" part of the question. I was just wondering if there is
> some way of passing dynamic data into a trigger. I don't believe there is,
> but I thought I would ask.
I cannot figure out if you are supposed to use the same value as ??, but
I believe I would just create a function (which takes parameters) and
then call that from the logs_insert trigger. That way I believe you have
a much better control of the flow of your updates.
Hope that helps.
--
Thomas Braad Toft
Maybe what you want is a stored procedure, not a trigger. A trigger
will only have the data that is available from the insert operation,
and the rest of the row that was modified (It really can't have
anything else if you think about it)
Stored procedures are an exellent way to guarantee atomic access to
data in an application.
Alex Turner
NetEconomist
On Wed, 5 Jan 2005 16:35:42 -0500, Terry Lee Tucker <terry@esc1.com> wrote:
> 1) INSERT INTO logs (carr_code, ..., ..., ...) VALUES('ABCDEFG', ..., ...);
> logs is table A in my question
>
> 2) logs_insert fires (This is a AFTER INSERT trigger)
>
> 3) in this trigger, I need to do the following:
> update avlds set carr_code = new.carr_code where avlds.recid = ??;
> avlds is table B in my question
> The questions marks indicate the first piece of data that I want to
> dynamically pass to the trigger.
>
> 4) in the same trigger:
> update tract set order_num = avlds.order_num where tract.recid = ??;
> tract is table C in my question
> The question marks refer to the second piece of data that I want to pass
> dynamically into the trigger.
>
> In other terms, when the user creates a logs record that assigns a truck to a
> load, I need to update specific rows in two other tables, avlds, and tract. I
> was wanting to do this from the trigger level, but I will need the unique
> serial key of each of the existing records in avlds and tract. These two keys
> comprise the "dynamic" part of the question. I was just wondering if there is
> some way of passing dynamic data into a trigger. I don't believe there is,
> but I thought I would ask.
>
> Thanks for the input.
> Work: 1-336-372-6812
> Cell: 1-336-363-4719
> email: terry@esc1.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
Now why didn't I think of that? That's exactly what I need to do. Thanks to
all who responded ;o)
On Wednesday 05 January 2005 05:09 pm, Alex Turner saith:
> Maybe what you want is a stored procedure, not a trigger. A trigger
> will only have the data that is available from the insert operation,
> and the rest of the row that was modified (It really can't have
> anything else if you think about it)
>
> Stored procedures are an exellent way to guarantee atomic access to
> data in an application.
>
> Alex Turner
> NetEconomist
>
> On Wed, 5 Jan 2005 16:35:42 -0500, Terry Lee Tucker <terry@esc1.com> wrote:
> > 1) INSERT INTO logs (carr_code, ..., ..., ...) VALUES('ABCDEFG', ...,
> > ...); logs is table A in my question
> >
> > 2) logs_insert fires (This is a AFTER INSERT trigger)
> >
> > 3) in this trigger, I need to do the following:
> > update avlds set carr_code = new.carr_code where avlds.recid =
> > ??; avlds is table B in my question
> > The questions marks indicate the first piece of data that I want
> > to dynamically pass to the trigger.
> >
> > 4) in the same trigger:
> > update tract set order_num = avlds.order_num where tract.recid =
> > ??; tract is table C in my question
> > The question marks refer to the second piece of data that I want
> > to pass dynamically into the trigger.
> >
> > In other terms, when the user creates a logs record that assigns a truck
> > to a load, I need to update specific rows in two other tables, avlds, and
> > tract. I was wanting to do this from the trigger level, but I will need
> > the unique serial key of each of the existing records in avlds and tract.
> > These two keys comprise the "dynamic" part of the question. I was just
> > wondering if there is some way of passing dynamic data into a trigger. I
> > don't believe there is, but I thought I would ask.
> >
> > Thanks for the input.
> > Work: 1-336-372-6812
> > Cell: 1-336-363-4719
> > email: terry@esc1.com
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 8: explain analyze is your friend
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
--
Work: 1-336-372-6812
Cell: 1-336-363-4719
email: terry@esc1.com
On Wed, Jan 05, 2005 at 05:35:18PM -0500, Terry Lee Tucker wrote: > On Wednesday 05 January 2005 05:09 pm, Alex Turner saith: > > > > Maybe what you want is a stored procedure, not a trigger. > > Now why didn't I think of that? That's exactly what I need to do. This is a good example of why it's better to describe the problem you're trying to solve instead of asking about a partcular (and possibly suboptimal or wrong) way to solve it, and why it's important to provide enough information so that others can understand what you're trying to do. http://www.catb.org/~esr/faqs/smart-questions.html#goal http://www.catb.org/~esr/faqs/smart-questions.html#beprecise -- Michael Fuhr http://www.fuhr.org/~mfuhr/