?
Solved

trigger : update function

Posted on 2001-09-18
6
Medium Priority
?
1,006 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
6 Comments
 
LVL 1

Accepted Solution

by:
Bashar earned 100 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

601 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