The OLD() Function

Previous Next

See Also

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".

EXAMPLE 1:

To check that a column value does not increase by more than 10 at a time, you can write a domain constraint. In the Do Not Allow That field of the constraint, you would enter:

DOMAIN > OLD(DOMAIN) + 10

EXAMPLE 2:

To check if a cancel date is left empty when a reservation is inserted, it is recommended you set transition properties. However, you can also express the rule simply by writing the following constraint SQL:

SELECT    ' ' violated

FROM    reservation

WHERE    cancel_date is not null

AND    OLD(res_id) is null

where res_id is the primary key column and, therefore, the last WHERE clause implies that the record is being inserted.

Because the OLD() function appears in your statement, the constraint checker automatically sets transition properties when you have the constraint checked. You are free to reset these properties later.