Solved

trigger : update function

Posted on 2001-09-18
6
997 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 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
report returning null 21 94
Wrap Oraccle SQL*Plus executable Command 4 84
data lookup in Oracle - need suggestions 55 113
Component is listed with a Protocol more than once 3 29
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  â€¦
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.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

776 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