Sean, there are many times when to have a context variable with the name of
the table would be good. For example when you have a stored procedure that
is called from a trigger.
Trigger1 (table PRODUCTS)
-----------------------------------------
EXECUTE PROCEDURE MyStoredProcedure('PRODUCTS');
Trigger2 (table CLIENTS)
--------------------------------------
EXECUTE PROCEDURE MyStoredProcedure('CLIENTS');
Trigger3 (table COUNTRIES)
-----------------------------------------
EXECUTE PROCEDURE MyStoredProcedure('COUNTRIES');
The problem with it is that if you write wrong the name of the table then
you will have wrong results (i.e.: writing 'PRODUCTS' but the table is
'CLIENTS'). Therefore it would be very better having something like it:
EXECUTE PROCEDURE MyStoredProcedure(TABLENAME);
where TABLENAME is a context variable that has the name of the table at
which the trigger belongs. Never you will have wrong writings so.
If the stored procedure MyStoredProcedure is called from one or two tables
then it is easy and fast to verify their correctness but if there are many
tables with many triggers is another story.
Greetings.
Walter.
On Sat, Nov 9, 2013 at 12:29 AM, Leyne, Sean <[email protected]>wrote:
>
>
> Walter,
>
>
>
> A trigger can only apply to a single table, so why would you need a
> variable which would contain the table name?
>
>
>
> You already have the explicit context -- you define the trigger. So, if
> you want a variable, declare it and assign the value you want…
>
>
>
>
>
> Sean
>
>
>
> *From:* [email protected] [mailto:
> [email protected]] *On Behalf Of *W O
> *Sent:* Friday, November 08, 2013 9:00 PM
> *To:* [email protected]
> *Subject:* Re: [firebird-support] Table and Sequence names by reference
>
>
>
>
>
>
> Really it would be nice to have the table's name when you are inside a
> trigger.
>
>
>
> Greetings.
>
>
>
> Walter.
>
>
>
>
>
> On Fri, Nov 8, 2013 at 9:18 PM, Daniel L. Miller <[email protected]>
> wrote:
>
>
>
> On 11/7/2013 3:45 PM, Ann Harrison wrote:
>
> On Thu, Nov 7, 2013 at 2:09 PM, Daniel L. Miller <[email protected]>
> wrote:
>
> Is it possible to refer to internal structures by reference in PSQL?
>
>
>
> Err, reference, usually yes. Change? Probably not. Basically, PSQL is
>
> limited to DML - not DDL.
>
>
>
> Ok - can you please clarify what I CAN do? In particular:
>
> 1. From within an insert/update trigger, is there a variable that
> provides the table name? Or, if I'm trying to use a common stored
> procedure, would I call it from the trigger using the table name as a
> literal string parameter?
>
> 2. Even if the DDL statement I used was wrong (and I admit that - I
> pasted it without going through it first) can you show me a valid DML?
> Something like "SELECT * FROM :tablename"?
>
> 3. My overly paranoid belt-and-suspenders isn't going to remain in
> production code (I hope). I had that both as a safeguard and as an example
> to another programmer I'm working with. The production code would just
> have the "standard" check to see if an id was passed, and otherwise perform
> the assignment of "new.id = NEXT VALUE FOR sequence". But I'd like to
> have that code structure in a stored procedure so my triggers are basically
> just one line. Is that wrong?
>
> --
>
> Daniel
>
>
>
>
>
>
>
>
