Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: fire the trigger selectively when a column is updated

RE: fire the trigger selectively when a column is updated

From: Richard Huntley <rhuntley_at_mindleaders.com>
Date: Mon, 27 Aug 2001 10:41:34 -0700
Message-ID: <F001.00378848.20010827104131@fatcity.com>

You
can do something like this:
<SPAN

class=495004017-27082001> 
CREATE OR REPLACE
TRIGGER your_trigger<A
name=9905>AFTER UPDATE O<SPAN
class=495004017-27082001>F ename ON <SPAN class=495004017-27082001>Emp_Tab
FOR EACH ROW<A
name=9907>BEGIN    <SPAN
class=495004017-27082001>NULL;  --do whatever you have to do...call procedure, etc...
<SPAN

class=495004017-27082001>END;
 
<SPAN

class=495004017-27082001>HTH
<SPAN

class=495004017-27082001> 
<FONT face=Tahoma

size=2>-----Original Message-----From: novicedba [mailto:novicedba_at_hotmail.com]Sent: Saturday, August 25, 2001 8:55 AMTo: Multiple recipients of list ORACLE-LSubject: fire the trigger selectively when a column is updated Hi everyone,
    One of my developers wanted to
know if it is possible to write a trigger only if a particular column is changed, essentially making it a column level trigger.I gathered from the article below that it is possible in SQL server. <FONT face=Arial size=2>   Just wanted to know if this is possible in oracle.
Yes I can think of the :old and :new
but any other way ??
 
The code that is included inside an
UPDATE trigger runs every time its related table is updated. In most UPDATE triggers, the code in the trigger affects only certain columns, not all of them. Because of this, it would be pointless (and a waste of SQL Server resources) to run all of the code in the trigger if the column or columns you are interested in have not been updated. In other words, even if a column you are not interested in is updated, the UPDATE trigger will fire and run its code.

To help reduce the unnecessary running
of code in an UPDATE trigger, you can take advantage one of two different functions: UPDATE() (available in SQL Server 2000) or COLUMNS_UPDATED() (available in SQL Server 7.0 and 2000). Both functions can be used to test to
see if a particular column you are interested in has changed or not. Because of this, you can write code in your trigger to only run if the column you are interested in has changed, otherwise you can prevent the code from running if the column you are interested in has not changed. This can reduce the amount of work the trigger needs to do, boosting overall performance of your database.
The UPDATE() function is used to check
only one column at a time. The COLUMNS_UPDATED() function can be used to check multiple columns at a time. [ 7.0, 2000] Added 5-29-2001

cozI am anoviceOracle Certifiable
DBBS Received on Mon Aug 27 2001 - 12:41:34 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US