Solved

trigger : update function

Posted on 2001-09-18
6
995 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Force accepted

** Mindphaser - Community Support Moderator **
0

Featured Post

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.

Join & Write a Comment

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

762 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now