19.5. Referring to Old and New Column Values


Within a trigger definition, you can refer to columns of the row being inserted, updated, or deleted. This enables you to examine column values, or to change values before they are used for an insert or update.

To refer to a given column, prefix the column name with a qualifier of OLD to refer to a value from the original row or NEW to refer to a value in the new row. OLD and NEW must be used appropriately, because the triggering event determines which of them are allowable:

  • In an INSERT TRigger, NEW.col_name indicates a column value to be inserted into a new row. OLD is not allowable.

  • In a DELETE trigger, OLD.col_name indicates the value of a column in a row to be deleted. NEW is not allowable.

  • In an UPDATE trigger, OLD.col_name and NEW.col_name refer to the value of the column in a row before and after the row is updated, respectively.

OLD must be used in read-only fashion. NEW can be used to read or change column values.

The Capital_bi and Capital_bu TRiggers shown earlier in the chapter demonstrate the use of OLD and NEW.



MySQL 5 Certification Study Guide
MySQL 5.0 Certification Study Guide
ISBN: 0672328127
EAN: 2147483647
Year: 2006
Pages: 312

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net