The OLD() function

Previous Next

The OLD() function is a USoft-specific extension of the SQL language that it is not part of the ANSI-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( person_id, first_name, name)
VALUES      ( 1,'JOHN', 'DOE' )
 
UPDATE      person
SET         name = 'DOUGH'
WHERE       person_id = 1
 
DELETE FROM person
WHERE       person_id = 1

This table shows old and new values when you execute these statements:

 

Person ID

First Name

Name

 

INSERT

NULL

NULL

NULL

Old Value

 

1

JOHN

DOE

New Value

UPDATE

1

JOHN

DOE

Old Value

 

1

JOHN

DOUGH

New Value

DELETE

1

JOHN

DOUGH

Old Value

 

NULL

NULL

NULL

New Value

Old and new column values In INSERT, UPDATE, and DELETE

 

In 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, enter:

DOMAIN > OLD(DOMAIN) + 10

Example 2

To check that a cancel date is initially (= when a reservation is inserted) left empty, it is recommended you set transition properties. But 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 set these properties to different values.

 

See also

Transitional constraints

Transition properties

Differences between transitional and non-transitional constraints