The OLD() Function

The OLD() function is a USoft-specific function in the sense that it is not part of the SQL standard. You can use the OLD() function to refer to the previous value of a column. This is the value the column had the previous time the record was stored. In constraint SQL, OLD(<column name>) refers to the old value of the column, and <column name> to the new value.

·	When a record is inserted, all old column values are taken to be NULL.

·	When a record is deleted, all new column values are taken to be NULL.

As an example, consider the following sequence of statements:

INSERT INTO person(first_name, name)

VALUES('JOHN','DOE')

UPDATE person

SET name = 'DOUGH'

WHERE first_name = 'JOHN'

AND name = 'DOE'

DELETE FROM person

WHERE first_name = 'JOHN'

AND name = 'DOUGH'

This table shows what happens to record values when you execute these statements:

Old and New Column Values In INSERT, UPDATE, and DELETE

	Person ID	First Name	Name
	NULL	NULL	NULL	Old Value
INSERT	1	JOHN	DOE	New Value
	1	JOHN	DOE	Old Value
UPDATE	1	JOHN	DOUGH	New Value
	1	JOHN	DOUGH	Old Value
DELETE	NULL	NULL	NULL	New Value

The sequence of events whereby a record is first inserted, then possibly updated one or more times, and finally deleted, is sometimes referred to as the record's life-cycle.

NOTE:

Within a multi-record constraint, you can refer to the old values of the columns of ONE table only. This is called the "transition table".