Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: After row level triggers
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