Solved

trigger : update function

Posted on 2001-09-18
6
999 Views
Last Modified: 2011-10-03
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
Comment
Question by:msl22
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 1

Accepted Solution

by:
Bashar earned 25 total points
ID: 6490800
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
 

Author Comment

by:msl22
ID: 6490828
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
 

Expert Comment

by:frankzh
ID: 6491519
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Expert Comment

by:frankzh
ID: 6491563
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
 
LVL 49

Expert Comment

by:DanRollins
ID: 7064045
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
 
LVL 6

Expert Comment

by:Mindphaser
ID: 7091443
Force accepted

** Mindphaser - Community Support Moderator **
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Checking for column width 8 49
UTL_FILE invalid file operation 5 58
Oracle function return value when null 2 34
Oracle SQL Developer equivalent MS SQL 6 34
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question