Update trigger

The second edition of our book has again found a favourable reception worldwide, triggering even a reprint of that edition some time ago. We are therefore pleased to present the third edition, again as a comprehensively reviewed and updated version. Due to the friendly acceptance of the former editions, principle and basic concepts of the book have not been changed. 

Since the report is in itself a snapshot of comphance at a given period of time, it should be updated periodically. A complete revahdation is not necessary, but many sites find that an annual review of the vahdation report is helpful. Occasional specific events, such as upgrades of programs or replacement of hardware, may trigger partial or complete retesting. Finally, complex systems tend to evolve, so a review to confirm that version control procedures are appropriately followed is recommended on a regular (at least annual) basis. 

For example, audit trails may be implemented by using database triggers. A trigger is a feature of the Oracle database, which allows code written by the user to be executed based on an event. The concept implies that the edition of a record will produce an event. The event may be defined as an insert, update, or deletion of data from a database table. 

Serhan CN (2002) Lipoxins and aspirin-triggered 15-epi-Upoxin biosynthesis an update and role in antiinflammation and pro-resolution. Prostaglandins Other Lipid Medial 68-69 433-455... 

Create Trigger standardize Before Insert Or Update On atable For Each Row Execute Procedure standardize() ... 

The cansmiles function can also be used to enforce an SQL constraint that the cansmi column must contain valid canonical SMILES. SQL constrains like this are commonly used to maintain data integrity. For example, the SQL clause check (cansmi = cansmiles (cansmi)) can be used in the initial creation of the table. One might also consider using an SQL trigger to handle an insert or update to a column that is required to contain canonical SMILES. 

This canonicalize function uses NEW to refer to the row being inserted or updated. NEW.cansmi refers to the value under question. The canonical SMILES is computed and compared to NEW. cansmi. If they are not the same, the NEW.smi value is replaced by the canonical SMILES value and the NEW row is returned. This NEW row is used by the RDBMS in place of the original row. The create trigger command causes this operation to be put into effect in the RDBMS. 

In summary, the domain check occurs first, then the trigger and finally the column constraint check. So, whenever a trigger is used to attempt to correct a value being inserted or updated, a domain check constraint should not be used. Instead a column check constraint should be used. 

A better way to ensure chemical integrity of the SMILES is to use the SQL trigger mechanism. An SQL trigger allows a function to intercept data before it is inserted or updated and modify it if necessary. 

The standardize function checks whether any smirks in the std smirks table, when used in the xform function, results in a modification of the input SMILES stored in NEW.smiles. If the xform function does return a transformed SMILES, then that transformed value is used in place of the value the user attempted to insert. Finally, the trigger is created using the standardized function to possibly modify any SMILE before inserting or updating a table. 

The next statement defines a t r igger function that will be used whenever data is inserted or updated in this table. This function performs three important functions. First, it modifies the SMILES to be inserted into the smi column so that it contains the result of the isosmiles function. The isosmiles function is similar to the cansmiles function, except that it retains any stereochemistry that might be contained within the SMILES. If two stereoisomers are entered into this table, each will have a unique isosmiles value, but the same cansmiles value. In this way, they can be kept distinct, but their identical canonical SMILES shows them to be stereoisomers. The trigger function also computes the fingerprint and inserts it into the table when the SMILES is inserted or updated. 

There is some overhead in the use of indexes, constraints, triggers, etc. as discussed here. The overhead is incurred when rows are inserted or updated in the table. However, the value of this approach is that the data in the table are well validated and can be searched more reliably and efficiently. Direct lookups of canonical or stereo SMILES is simple and quick because of the index on these columns. Using the fingerprint column speeds up substructure search. Tautomers can be readily selected using the column of simple graphs. 

---