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. Raj, first I don't think this can be done via a trigger. If you went that route, it would be an INSERT trigger working in the BEFORE clause. Since it must make an intelligent decision, it MUST call a procedure, wherin the engine will determine if a row already exists in this table with the given primary key. If it does not yet exist, the trigger code need do nothing - the engine will proceed with the insert as soon as the trigger code finishes. On the other hand, if the row does exist, you want the proc to execute an update and then skip the insert. How do you do that? An ABORT will roll back the the whole operation! Since you need a procedure anyway, you might as well go that route - doo all inserts and updates via this procedure that decides if it will do the insert or the update. You can use GRANT to enforce that nobody uses naked SQL to do inserts or updates - only the procedure. Details of this are another story. HTH -- -- Jake (Pondering the color of an asphyxiated smurf) +------------------------------------------------------------+ | The expedient performance of a task with excessive concern | | regarding its duration-to-completion engenders a virtual | | certainty of diminished benefit therefrom. | | -- Benjamin Franklin (but he said it in 3 words) | +------------------------------------------------------------+