Thread: [Fwd: Re: Trigger Procedures]

[Fwd: Re: Trigger Procedures]

From
Steve Tucknott
Date:
Josh,
(Apologies that this reply went to you direct, rather than the list. I tend to
use reply and on this list, that just goes to the sender...)

Thanks for the reply.
I am aware that NEW/OLD are automatically available - hence the opening
paragraph of my mail.
I think my real question is is 'what can be passed as an argument into a
trigger procedure?'. The documentation doesn't really say that much
apart from the argument array is a text array.

Maybe I can explain a bit better.
We have a generic procedure that takes table names, record numbers and
text and then creates an audit. There is a trigger automatically created
for each table that needs to be audited. Under Informix (this is a
legacy procedure that is being converted), I can pass NEW into the
generic function - so I can have different 'named' fields being passed.
In the generic function the names of the passed fields (ie the variable
names) becomes static. IE if the trigger is on table A I pass field A,
if on table B I pass field B (both being same data type). In the generic
procedure this passed parameter is mapped to a variable called
'inputField'. So my procedure doesn't have to worry that in reality
inputField is field A or field B and I can use simple statements like
INSERT INTO auditTable VALUES(inputField.......
Using PostGreSQL (7.4.5) it appear that I cannot pass NEW fields into
the generic function - so it means that the function has to become
'aware' of the source. IE the procedure now has to do 'if the trigger
source table is table A then use NEW.fieldA ...... if the trigger source
table is table B then use NEW.fieldB ...... etc'.

Is that the only way I can go about this or is there something that I've
missed on the arguments passed to a trigger procedure?

On Thu, 2005-05-12 at 20:32, Josh Berkus wrote:
> Steve,
>
> > At the moment, I can't seem to pass the NEW values into trace_actions.
> > Similarly, as trace_actions is a common process, I have no way of
> > knowing whether NEW contains 'sourceDetRecNo' or 'sourceDetProdRecNo'
> > (unless I case the table name)
>
> You don't pass NEW and OLD as parameters.  They are automatically available
> inside the trigger procedure.
>
> --
> --Josh
>
> Josh Berkus
> Aglio Database Solutions
> San Francisco
>
> ---------------------------(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
--


Regards,

Steve Tucknott
ReTSol Ltd

DDI    01903 828769
MOBILE    07736715772
--


Regards,

Steve Tucknott
ReTSol Ltd

DDI    01903 828769
MOBILE    07736715772



Re: [Fwd: Re: Trigger Procedures]

From
Stephan Szabo
Date:
On Thu, 13 May 2005, Steve Tucknott wrote:

> Maybe I can explain a bit better.
> We have a generic procedure that takes table names, record numbers and
> text and then creates an audit. There is a trigger automatically created
> for each table that needs to be audited. Under Informix (this is a
> legacy procedure that is being converted), I can pass NEW into the
> generic function - so I can have different 'named' fields being passed.
> In the generic function the names of the passed fields (ie the variable
> names) becomes static. IE if the trigger is on table A I pass field A,
> if on table B I pass field B (both being same data type). In the generic
> procedure this passed parameter is mapped to a variable called
> 'inputField'. So my procedure doesn't have to worry that in reality
> inputField is field A or field B and I can use simple statements like
> INSERT INTO auditTable VALUES(inputField.......
> Using PostGreSQL (7.4.5) it appear that I cannot pass NEW fields into
> the generic function - so it means that the function has to become
> 'aware' of the source. IE the procedure now has to do 'if the trigger
> source table is table A then use NEW.fieldA ...... if the trigger source
> table is table B then use NEW.fieldB ...... etc'.

Some of the PLs (not plpgsql) allow indirect references via field name
which might allow you to pass the name of field as the trigger argument.
That might be a good fit for what you are doing at the cost of having to
use one of the other languages.