SQL PROGRAMMING STYLE- P20:Im mot trying to teach you to program in SQL in this book. You might want to read that again. If that is what you wanted, there are better books. This ought to be the second book you buy, not the first. I assume that you already write SQL at some level and want to get better at it. If you want to learn SQL programming tricks, get a copy of my other book, SQL for Smarties (3rd edition, 2005). | 122 CHAPTER 6 CODING CHOICES procedure has to be deliberately executed which puts it completely in your control. Furthermore the syntax for triggers is proprietary despite the standards so they do not port well. Use SQL Stored Procedures Every SQL product has some kind of 4GL language that allows you to write stored procedures that reside in the database and that can be invoked from a host program. Although there is a SQL PSM standard in the real world only Mimer and IBM have implemented it at the time of this writing. Instead each vendor has a proprietary 4GL such as T-SQL for the Sybase SQL Server family PL SQL from Oracle Informix-4GL from Informix and so forth. For more details on these languages I recommend that you get a copy of Jim Melton s excellent book Understanding SQL s Stored Procedures ISBN 1-55860461-8 out of print on the subject. The advantages they have are considerable including the following Security. The users can only do what the stored procedure allows them to do whereas dynamic SQL or other ad hoc access to the database allows them to do anything to the database. The safety and security issues ought to be obvious. Maintenance. The stored procedure can be easily replaced and recompiled with an improved version. All of the host language programs that call it will benefit from the improvements that were made and not be aware of the change. Network traffic. Because only parameters are passed network traffic is lower than passing SQL code to the database across the network. Consistency. If a task is always done with a stored procedure then it will be done the same way each time. Otherwise you have to depend on all programmers present and future getting it right. Programmers are not evil but they are human. When you tell someone that a customer has to be at least 18 years of age one programmer will code age 18 and another will code age 18 without any evil intent. You cannot expect everyone to remember all of the business rules and write .
