Posted in RDBMS, SQL, Sybase

MySQL Triggers weak support for referential integrity

Even though my workplace and reduced costs by migrating from Sybase on Solaris to Sybase on redhat. We are now in a move from Sybase to MySQL to further reduce the license costs of the applications. This has brought to forefront one of the reasons to use Sybase. Triggers and stored procedures. And while MySQL now provides both, and transactions in InnoDB engines, Sybase triggers and Procedure are far more advanced. This forms a type of trap, they are very useful, but create serious obstacles to convert triggers that perform Referential Integrity. Just to highlight two types of these issues, take the following;

You can not use ‘select’ criteria in a trigger that returns a value. This prevents executing a select on a table to check for the existence of record prior to an insert/update. This was a major issue with a trigger used to produce a history file via a ‘update’ that had to both insert and update the history table. My workaround was to create a MySQL procedure that had no such restriction, and to call the procedure from the trigger. This worked very well, but of course meant that there was additional overhead and more coding involved.

The second example was, in my opinion, was a far more severe omission of Referential Integrity, ‘Rollback’! You cannot perform a rollback in a trigger. What! Further more, you have use ‘set autocommit=0’ either as a database default, which it is not, by default, set. Or set it during the batch or client operation that you are performing. Twice bad! Again, my solution was to call a procedure that could perform a ‘rollback’ and to ensure that either the developer set autocommit off or to insure that it was set as a database wide option.

As easy as these solutions were, they highlight an omission of functionality that requires addressing in the future development of MySQL.

Advertisements

Author:

With 40 years experience in software development, systems design and engineering and IT operations, and Infrastructure Architecture issues. I am versed in multiple programming languages, Operating Systems and RDBMS, I have work experience ranging from microcomputers and PC’s to multiprocessor mid range Unix systems and clusters. I have experience with both wireless and wired network protocols and mediums. And I've help migrate systems into the Amazon EC2 Cloud from self hosted configurations. I collect old working computers, I'm a published Astro-photographer, I tutor, and teach almost every subject I am knowledgeable in. I have had one internet email or another since 1991. I developed Gopher sites prior to the formation of HTTP/HTML and a few websites since then. I wrote my first 'database' on a DEC PDP-11 for the DECUS Library in 1984. Specialties I specialize in Database systems, and am familiar with almost all types of RDBMS and ISAM systems short of Mainframes. I habitually reverse engineer and document everything I touch.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s