Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: After row level triggers

Re: After row level triggers

From: Peter H. Larsen <plarsen_at_dc.dynares.com>
Date: Wed, 10 Jun 1998 17:25:56 -0400
Message-ID: <357EF9E4.4F009511@dc.dynares.com>


Hi Mike,
Don't use AFTER triggers to alter data in your table which the trigger is based on. Use BEFORE triggers, and just assign the new values to the :NEW.<column>.

As of the syntax of making triggers, refer to the manual. It's pretty simple, just anonymous PL/SQL blocks. Actually they are all stored as procedures in the end, but who minds that :) A good hint, for larger logic triggers, place the logic in packages, not in the trigger. Let the trigger call the package and transfer it's fields etc. to the package.

mike_andrew_at_usiva.com wrote:

> Having difficulty writing a trigger. I have a successful before row level
> trigger that accomplishes what I want to do, but on large imports of data I
> get the mutating table error.
>
> Anyway, I would like the trigger to fire after each row is inserted.
> Basically what I want the trigger to do is after inserting of SSN, FNAME,
> LNAME on EMP take a concatination of the NAME fields (FNAME, LNAME) and
> update the USERID field for the new SSN (as well as) insert into another
> table the same USERID.
>
> The thing that makes everything complicated is that the userid has to be
> unique. For example (LNAME = 'SMITH' FNAME = 'JOHN') and (LNAME = 'SMITH'
> FNAME = 'JOE') would return USERID's of SMITHJ. I have to select max(USERID)
> from EMP where USERID like 'LNAME||SUBSTR(FNAME, 1, 1)||%' to return highest
> similar value and add an increasing value at the end. Results would end up
> like SMITHJ, SMITHJ1 for the two previously mentioned.
>
> The problem I face is in the tigger syntax and logic.
>
> If anyone can help or send a similar sample trigger script I would greatly
> appreciate this.
>
> Thanks in advance.
> Mike
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/ Now offering spam-free web-based newsreading
Received on Wed Jun 10 1998 - 16:25:56 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US