Oracle Pro Tips, Trends & Technology eNewsletter Pinnacle Publishing http://www.pinnaclepublishing.com Issue 2.18 September 6, 2001 TABLE OF CONTENTS 1) Pop Quiz 2) This Issue's URLs 3) SQL Tip: INSTEAD OF Triggers 4) Your Feedback 5) News 6) Answer to the Pop Quiz --------------------------------------------------------- 1) POP QUIZ (answer at the end) --------------------------------------------------------- How do you share the NET8 configuration profile (tnsnames.ora) across multiple-client PCs? --------------------------------------------------------- 2) THIS ISSUE'S URLs --------------------------------------------------------- There's an interesting discussion on a scripting language TCL for Oracle. Check it out at: http://www.unixreview.com/articles/2001/0108/0108h/0108h.htm --------------------------------------------------------- 3) SQL TIP: INSTEAD OF Triggers --------------------------------------------------------- Oracle provides INSTEAD OF database triggers for managing data operations on views. (Note that you cannot specify INSTEAD OF triggers on tables.) Once defined, Oracle fires the trigger "instead of" performing the default data operation (insert, update, delete) on the view. The advantage of INSTEAD OF triggers is that you have complete control over the data operation of your application. For instance, in a Web application, you can create a database view for a particular Web page that may contain data from multiple tables -- via table joins. When a Web user clicks on the "update" button, you can write an INSTEAD OF trigger to manage how the change is to be propagated to the database. We'll discuss the disadvantage in a bit. First, let's take a look at an example. In this example, addresses are stored in two tables, one for employees and one for customers. We've created a view, v_addresses, that "encapsulates" the address information for both employees and customers. This view can be used to display data on a Web page. A new record inserted through the view is processed by the INSTEAD OF trigger, trg_addresses. It decodes the data source for the address and inserts the record into the employee_addresses and the customer_addresses tables, respectively. CREATE TABLE employee_addresses ( employee_id number, street varchar2(20), city varchar2(20)); CREATE TABLE customer_addresses ( customer_id number, street varchar2(20), city varchar2(20), country varchar2(20)); CREATE VIEW v_addresses ( party_id, street, city, country, party_type ) AS SELECT employee_id, street, city, null, 'EMP' from employee_addresses UNION SELECT customer_id, street, city, country, 'CUST' from customer_addresses; CREATE TRIGGER trg_addresses INSTEAD OF INSERT ON v_addresses FOR EACH ROW BEGIN IF :new.party_type = 'EMP' THEN INSERT INTO employee_addresses ( employee_id, street, city ) VALUES ( :new.party_id, :new.street, :new.city ); ELSE INSERT INTO customer_addresses ( customer_id, street, city, country ) VALUES ( :new.party_id, :new.street, :new.city, :new.country ); END IF; END; / Can you think of other uses for INSTEAD OF triggers? --------------------------------------------------------- 4) YOUR FEEDBACK --------------------------------------------------------- Our discussion on mutating triggers generated quite a bit of feedback from the subscribers. Here's a summary of some of your views. Jordan Janisse wrote: "Your explanation and solution for the mutating trigger problem is excellent. We use a very similar process, but have found a slight shortcut. "In the package, initialize the counter variable to have a default of 0. In the after-statement trigger, reset the counter variable to 0. This eliminates the need to have a before-statement trigger. There is a potential risk that the counter will become out-of-sync if an error occurs. If that is the case, then include a counter reset statement in any exception section of either the row trigger or the after-statement trigger. "The benefits of this approach are that it is easy to implement, and we have one less object to manage. If I review the after-statement trigger and don't see the cleanup section and exception section, I know that it was not completed. In the QA process, I find that easier than looking for a before-statement trigger. "(Note: I have later read that global variables are rolled back if modified in a trigger that fails (that is, if rollback occurs). Now, I never believe what I read without testing. I have not tested that theory, so I still include exception handling that resets the counter.)" Mark Geerlings had a different solution to the mutating table error: "...I use a global temporary table instead. That allows me to use the more familiar 'insert' logic in the row- level triggers, and a cursor loop in the after-statement trigger. I haven't done thorough performance testing using both your approach (which I suspect may be faster), but there is no noticeable performance penalty with the global temporary table approach -- at least for tables with low to moderate levels of activity." Simon Brock had another idea: "Regarding the question of alternative ways of overcoming the 'mutating tables' problem, another technique, which can sometimes be used (with 8i and above) is Autonomous Transactions. "For example, I tried to create a before-update trigger on the EMP table, which would set the salary of the row being updated to $100 more than the fattest salary present in the EMP table. Naturally, I received the mutating table error message. However, using the following trigger code, the mutating table message disappeared, and the result was achieved. CREATE OR REPLACE TRIGGER scott.emp_bu BEFORE UPDATE ON scott.emp REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE -- Autonomous function that can read EMP with impunity FUNCTION get_max_sal RETURN emp.sal%TYPE IS PRAGMA AUTONOMOUS_TRANSACTION; v_max_sal emp.sal%TYPE; BEGIN SELECT MAX(SAL) INTO v_max_sal FROM EMP; RETURN v_max_sal; END; BEGIN :new.sal := get_max_sal + 100; END; / "Note that the autonomous function (get_max_sal) will not be able to see any uncommitted changes made by the current transaction, which may or may not be what one wants." Thank you all for your helpful suggestions. --------------------------------------------------------- 5) NEWS --------------------------------------------------------- CIBC World Markets produced a research report in August regarding Oracle's aggressive push into the CRM (Customer Relationship Management) market. Here's a high-level summary of its findings: - Oracle was determined to gain ground against segment leader, Siebel Systems - The data collected by the CIBC analysts suggested that "...Oracle's CRM business was about $400M in calendar 2000, which is about 1/3 of Siebel's size based on licenses sales." - "In the current climate, we think customers are more likely to purchase software from known vendors than to work with a new vendor altogether. For now, this favors Siebel; however, if IT spending recovers next year, Oracle could gain momentum." For more information on the research report, please contact: Melissa Eisenstat (212) 667-4665 Scott A. Daniels (212) 667-4896 --------------------------------------------------------- 6) ANSWER TO THE POP QUIZ --------------------------------------------------------- It's often a pain to maintain the NET8 connection file across multiple-client PCs. Anytime the database host name or database SID changes, we have to apply it across all PCs. One option is to email the new version of the tnsnames.ora file to the users and ask them to put it on their $ORACLE_HOME/network/admin (or $ORACLE_HOME\network\admin for Windows) directory. A better option is to define an environment variable (or registry value in Windows) called TNS_ADMIN on each client PC. This contains the directory location for the tnsnames.ora file. So you can put the configuration file on a network drive visible to all, so that new changes to the file may be transparent to the end users. Try it out. Let me know if you've other options. --------------------------------------------------------- Well, that's it for this week. I welcome your feedback, input, tips, suggestions, Web sites, and other Oracle- related news. If you send me something, please let me know whether I can use your name with your comments. I apologize in advance if I don't respond personally to each of your questions or suggestions, but I'll get to as many as I can in the eNewsletter if not personally. Garry Chan, Editor Database Architect mailto:GChan@ProcaseConsulting.com This eNewsletter is brought to you compliments of Pinnacle Publishing, Inc. Copyright(c) 2001 http://www.pinnaclepublishing.com All rights reserved.