Re: Triggers

Raj Goel wrote: > > I've been reading the chapters on triggers and there's something I don't > quite understand. What I'd like to do is the following: > > For a given table, > if the record exists (table has primary key), > UPDATE the record > else > INSERT a new record > > I wrote a stored proc to do this for a single table. > > I need to perform this operation in 50+ tables and I keep thinking > I need a trigger (or triggers) on each of the tables, but I can't figure out > how to create the trigger statement. You cannot write a trigger to do this. If the record exits and has a unique key an insert will fail without firing any insert triggers. Conversely, if the record does not exist an update will succeed, because it is not an error to update zero rows, but no update trigger will fire. Your stored procedure approach is the best one. By the way there are three approaches to this operation which vary in efficiency depending on the data: 1) Select the row, if it exists update it; if not exist insert it. This can add a some of overhead over the other two methods, but, if the number of rows that already exist is about equal to the number of new rows and you use UPDATE...WHERE CURRENT OF... the overhead is minimal. 2) Always insert the row. If it exists it will fail with a duplicate key, primary key constraint, or unique constraint violation (depending on the table's schema) and you can then update it. This is VERY expensive since the row is actually inserted before indexes are updated and the unique violation is discovered. Then everything has to be rolled back. HOWEVER, if the record will rarely exist this is actually the most efficient approach. 3) Always try to update if that returns zero rows updated then insert. If most rows already exist this is by far the cheapest method. Art S. Kagel