SQL: using the newly inserted index as a value for insertion

Dr. Hawkins dochawk at gmail.com
Sun Nov 24 12:33:10 EST 2013


On Sat, Nov 23, 2013 at 10:26 PM, Peter Haworth <pete at lcsql.com> wrote:

> You want the word key to be replaced by the value of the primary key from
> the row from the INSERT statement?
>

Yes; the primary key assigned on the previous command.


> If so,  that's how the trigger can help.  You can refer to any column from
> the INSERT using the syntax "new.colname" where colname is the name of any
> column in master table.
>

What I'm missing (and is probably fundamental) is *why* I can't use the
value of the column in my UPDATE, and why postgres answers that the column
doesn't exist.

If I run

BEGIN TRANSACTION;
  UPDATE theTable SET price=100 WHERE key=7;
  UPDATE theTable SET profit=price/3 WHERE key=7;
END TRANSACTION;

I should get profit set to 33 in that record (subject to rollback).

I'm not seeing the difference between setting that value in an insert and
using it the next line in an update on the one hadnd, and changing in an
update then using that changed value on the next line.

Sure, I'm using it as part of a string operation, but postgres is supposed
to support those.

And won't a trigger be expensive, taking time every time I UPDATE over a
relatively uncommon event?


> Still not 100% sure I understand but maybe that helps.
>

I think so, but I'm not there yet :)

And isn't




-- 
Dr. Richard E. Hawkins, Esq.
(702) 508-8462



More information about the use-livecode mailing list