trigger question

[Thomas Braad Toft]

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [email]majordomo@postgresql.org[/email]

[Alex Turner]

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: [email]terry@esc1.com[/email]
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [email]majordomo@postgresql.org[/email]

[Terry Lee Tucker]

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: [email]terry@esc1.com[/email]
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 8: explain analyze is your friend

>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to [email]majordomo@postgresql.org[/email]

--
Work: 1-336-372-6812
Cell: 1-336-363-4719
email: [email]terry@esc1.com[/email]

---------------------------(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

[Michael Fuhr]

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.

[url]http://www.catb.org/~esr/faqs/smart-questions.html#goal[/url]
[url]http://www.catb.org/~esr/faqs/smart-questions.html#beprecise[/url]

--
Michael Fuhr
[url]http://www.fuhr.org/~mfuhr/[/url]

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