Re: Trigger problems with CONCAT

what you probably want is and BEFORE INSERT OR UPDATE FOR EACH ROW trigger that simply resets the ':NEW' value for the current row, similar to:

CREATE OR REPLACE TRIGGER STUDENT_FULLNAME BEFORE INSERT OR UPDATE ON student
FOR EACH ROW
BEGIN
   :new.name := :new.lname || ', ' || :new.fname; END STUDENT_FULLNAME; What your sample triggers appear to be doing is updating every row in the table for each insert statement that is executed against the table -- are you used to writing SQL Server triggers and issuing updates against its 'new' and 'old' tables (or whatever it calls them)? In Oracle, you typically just use simple PL/SQL assignments to manipulate the new record values.

:: Mark C. Stock
:: mcstock_at_enquery.com
:: www.enquery.com

"Sawt" <burger2001_at_yahoo.com> wrote in message news:3586c847.0203131005.5930f3bd_at_posting.google.com...
> I have a trigger that runs when records are inserted in my table.
> Currently, it works fine. The table INKTOMI_CATALOG has the following
> rows CT_PRODNUM, CT_FORM, CT_DOCTYPE, CT_TITLE, CT_X, CT_Y, CT_DATE,
> CT_SUFFIX
> Here is the trigger:
>
> TRIGGER VIGNETTE.TGR_INS_INKTOMI_CATALOG AFTER
> INSERT
> ON "INKTOMI_CATALOG"
> BEGIN
> UPDATE INKTOMI_CATALOG
> SET CT_SUFFIX = SUBSTR(CT_PRODNUM, LENGTH(CT_PRODNUM), 1),
> CT_DOCTYPE = SUBSTR(CT_DOCTYPE, 1, 4),
> CT_DATE = SYSDATE
> WHERE CT_PRODNUM = CT_PRODNUM;
> END;
>
> What I want to to is concatenate the CT_DOCTYPE value at the beginning
> of the value inserted in CT_TITLE. Here is what I have the trigger
> doing:
>
> /create or replace TRIGGER VIGNETTE.TGR_INS_INKTOMI_CATALOG AFTER
> INSERT
> ON "INKTOMI_CATALOG"
> BEGIN
> UPDATE INKTOMI_CATALOG
> SET CT_SUFFIX = SUBSTR(CT_PRODNUM, LENGTH(CT_PRODNUM), 1),
> CT_DOCTYPE = SUBSTR(CT_DOCTYPE, 1, 4),
> CT_TITLE = SUBSTR(CT_DOCTYPE, 1, 4) || ' ' || CT_TITLE,
> CT_DATE = SYSDATE
> WHERE CT_PRODNUM = CT_PRODNUM;
> END;
> /
>
> Thie problem is that it seems to get stuck in sort of look when it
> runs. In the first record, the value for CT_DOCTYPE is "Form" and the
> value for CT_TITLE is "Form Form Form Form Form Form Form Form Form
> Form Form Form Form Form U.S. Individual ....." (the original value of
> CT_TITLE is "U.S. Individual ....."
>
> I am stuck, I can't figure out what the problem is. All help is
> appreciated.
>
Received on Thu Mar 14 2002 - 00:37:06 CET