Re: Database Trigger Confusion
On Thursday 24 May 2001 10:51, Yexley Robert D SSgt AFIT/SCA wrote: > CREATE OR REPLACE TRIGGER endb.budget_total_amt_hist_trg > BEFORE UPDATE of total_amt_auth, expiration on endb.budget ^ Time to Read the Fine Manual. You can't use columns on the BEFORE UPDATE clause unless they are part of a nested table. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Database Trigger Confusion
Brian is right, why not compare old and new values and handle this programmatically? HTH Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art ! *1 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *1 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Database Trigger Confusion
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" <[EMAIL PROTECTED]> on 05/24/2001 01:51:16 PM Please respond to [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> 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 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 <)))>< [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Database Trigger Confusion
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 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 <)))>< [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).