Re: Database Trigger Confusion

From: <Chaim.Katz_at_Completions.Bombardier.com>
Date: Thu, 24 May 2001 12:05:18 -0700
Message-ID: <F001.0030DF87.20010524115241@fatcity.com>

I think your trigger is fine; the problem might be that some front-end tools (forms by default?) generate an UPDATE statement that affects all the columns of the table - even if only a one field was changed. Think of it this way, UPDATE table SET col1=col1 doesn't change any values but it does update the column and it does fire the trigger.
hth
Chaim

"Yexley Robert D SSgt AFIT/SCA" <Robert.Yexley_at_afit.edu> on 05/24/2001 01:51:16 PM

Please respond to ORACLE-L_at_fatcity.com

To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> cc: (bcc: Chaim Katz/Completions/Bombardier)

I have created a trigger on a table to write information to a history table, and I've run into some behavior that's confusing me...some clarification would be greatly appreciated.

My trigger code is as follows:

CREATE OR REPLACE TRIGGER endb.budget_total_amt_hist_trg

     BEFORE UPDATE of total_amt_auth, expiration on endb.budget
     FOR EACH ROW
     BEGIN
          <code_to_rollover_data_to_history_table>
     END;

Now my confusion lies in the fact that the trigger is supposed to fire ONLY when either the total_amt_auth column OR the expiration column gets updated. Now, of course it works fine when we update either of those columns. But the problem/confusion is why is it also firing when we update other fields in the table also?? If we update any column other than those two the trigger still fires. What am I doing wrong or missing/forgetting???

-::YEX::- Robert D. Yexley, SSgt
Air Force Institute of Technology
Applications Services Division (SCA)
Oracle DBA & Programmer
<)))><

robert.yexley_at_afit.edu
DSN - 785-6565 x-4268
Commercial - (937) 255-6565 x-4268

---

"God who gave us life gave us liberty.
Can the liberties of a nation be secure
when we have removed a conviction
that these liberties are a gift of God?
Indeed I tremble for my country when
I reflect that God is just, that his
justice cannot sleep forever."

• Thomas Jefferson, 3rd US President

  ---

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Yexley Robert D SSgt AFIT/SCA
  INET: Robert.Yexley_at_afit.edu

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).






-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Chaim.Katz_at_Completions.Bombardier.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Thu May 24 2001 - 14:05:18 CDT