Tutorials Oracle 9i Tutorial 14: Oracle 9i : Database Triggers Tutorial 14: Oracle 9i : Database Triggers - Page 2
Handling multiple situations
A trigger can be used to handle multiple situations as shown in the following example. By using conditional predicates UPDATING, INSERTING, or DELETING we can handle each situation.
CORRELATION NAMES
While using row triggers, the trigger action statement can access column values of the row that is being processed currently. This is done using correlation names. There exist two correlation names for every column of the table, one for the column old value and the other for its new value. We use qualifier NEW with column name for new values and qualifier OLD is used to refer old value of the column.
Example:
IF :new.sal < :old.sal THEN
……
The REFERENCING option is used to avoid name conflicts between correlation names and table names. For example if you are using a table by name new or old with field names say SNO, NAME (though it is a very rare situation) then the ambiguity arises. To avoid this we use REFERENCING option.
Mutating Table: is a table that is presently under modification by INSERT, UPDATE, or DELETE statement, or a table that has referential integrity constraint with DELETE CASCADE OPTION.
Constraining Table: is a table, which is to be read by triggering statement either directly or indirectly.
In the above example when we tried to delete a row using SQL statement (Mutating Table) , which fires AFTER DELETE trigger. The body of this trigger is having a select statement that tries to read the table. This operation is not allowed by oracle. Hence we received a runtime error and total action is rolled back by Oracle. (The row is not deleted)
First Page: Tutorial 14: Oracle 9i : Database Triggers
|
Yes. This is very good site to beginers. Thanx a lot |
|
It is really good place for a beginner to learn. This article would be very fruitful, if you could give a strategy to avoid the mutating issue. Thanks for a wonderful basics. |
| Good and sufficient content |
|
Hi, Its a good place to begin with triggers. It provides solid funda. Please add the advanced info on triggers. Thanks! Sami |
| Nice, short and excellent description of triggers. Good for beginners. Writtern in easy and lucid language and good illustrations too. |
| Nice description and good illustrations. Excellent for beginners. |
| I find it very useful for beginners. THX |
| hi dis is the good way of learning course |
| hi trigger concepts r very good also easy and simple |
|
It is really helpfull in refreshing the topic Thanks |
| This Document is rally good. |
| I was looking for some 1 minute intro to Triggers.....and I got it here the way I wanted. |
|
hi. this document is really good and excellent for beginners..... |
| Excellent Information for starters... Really easy to Understand |
|
very nice, but need to add more examples.. |
|
These is very Nice, but i need some more Examples. |
| Really its too good...Simple but descriptive |
| hey this is good one for getting the conecpt as a beginner level |
| Very easy to Understand instead of reading a chapeter in book |
|
Excellent Information for starters... Really easy to Understand |
| It's Really Good Site for Begginer's to learn.... |
| The info present above is very easy to understand ....! thanks.....! |
| This site is really good for understand.........thanks....... |
| Its good but you should give more examples in detailed manner |
| how many triggers we can make once please tell me somebody |
|
Hi Amit, Below are the trigger types, 1) Statement Level 2) System Level 3) Instead of Trigger 4) Row level Trigger For row and statement level we will create triggers as, Before and After statement and Row level Regards, Mahendra |
|
We can create any no. of trigger but the combination is 12 only. EX. INSERT/UPDATE/DELETE ROW/STATEMENT AFTER/BEFORE |
|
Can you have multiple conditions in the WHEN statement? WHEN (x=x && y=y) |
| How to create a trigger for a column in particular table which accepts only capital letters? |
| How to create trigger for a row which when deleted it's any one field (e.g. emp_no ) which is primary key, get inserted into another table ? |
