• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1006
  • Last Modified:

trigger : update function

Hi,

I'm working with Oracle 8i.
And I have to create a trigger for an Update action.
I need to do some code everytime, and
some other only when the update is on the field "Name".

On SQL 7.0, I used the function Update(Field Name) like this : IF UPDATE(Name)THEN...
I'd like to know if there's a function, in Oracle, to do same verification...

Thank

Mary.

P.S. Someone know a good internet address to find information about database creation (table, trigger, ...)
Thank you.
0
msl22
Asked:
msl22
1 Solution
 
BasharCommented:
You can create two triggers, one of them handles the update of the field Name, and the other for every update action.
The syntax for the first is as follows:

create trigger trig_on_updt_of_name
before update of name
on table_name
for each row
is
...
Your PL/SQL code goes here...
...
end;


The second trigger would be similar to the preceding one:

create trigger trig_on_updt_on_tbl
before update
on table_name
for each row
is
...
Your PL/SQL code goes here...
...
end;



Hope this helps.

Regards,
Bashar.
0
 
msl22Author Commented:
Hi Bashar,

If, in the trigger on Update, the verification failed... does the trigger on Update of Name will be done ?

If yes, I will need to do my verification in both trigger. So I will do it twice (performance).
And if the verification failed, I will have two error messages ?

There's a way to do the trigger on Update of Name only if the first trigger succed (no error message) ?

Thanks!



create trigger trig_on_updt_of_name
before update of name
on table_name
for each row
is
...
Verification
if failed
   error message
...
Your PL/SQL code goes here...
...
end;


The second trigger would be similar to the preceding one:

create trigger trig_on_updt_on_tbl
before update
on table_name
for each row
is
...
Verification
if failed
   error message
...
end;
0
 
frankzhCommented:
Two way of doing so:
1. For oracle version 8, AUTONOMOUS_TRANSACTION
in the declaration part of ur trigger add following line
:
PRAGMA AUTONOMOUS_TRANSACTION;

so ur trigger will have a seperated transaction other than the transaction which fires the trigger, therefore the COMMIT statement in ur trigger will not affect the parent transaction. For more detail, ref : oracle8i PL/SQL guide, can be downloaded from OTN

2. For oracle version is prior to 8, use oracle pipe mechanism, the basic idea is the same as previous one, u need two transaction. For more detail ref. oracle8i plsql package reference, DBMS_PIPI
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
frankzhCommented:
sorry for mistakenly posting a answer which should not correspond to this question. Bashar's answer above is the good solution to ur problem.
0
 
DanRollinsCommented:
Hi msl22,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days.  I will suggest to:

    Accept Bashar's comment(s) as an answer.

msl22, if you think your question was not answered at all or if you need help, you can simply post a new comment here.  Community Support moderators will followup.

Please do not accept this comment as an answer!

EXPERTS: Your input for closing recommendations are REQUESTED.
==========
DanRollins -- EE database cleanup volunteer
0
 
MindphaserCommented:
Force accepted

** Mindphaser - Community Support Moderator **
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now