Selecting deleted values

You can retrieve the values for rows that are deleted by specifying the DELETE statement in the FROM clause of a SELECT statement.

When you delete one or more rows from a table, column values for the deleted rows can be returned.

To see all the column values of the deleted row for employee '00200', use the following statement:

SELECT *
  FROM OLD TABLE (DELETE FROM EMPLOYEE
                          WHERE EMPNO = '000200'));

You can return additional information related to the delete statement. To return the timestamp of when a delete occurred, define and set a column using the INCLUDE and SET clauses.

SELECT DELETE_TS, EMPNO, WORKDEPT, LASTNAME, FIRSTNME
  FROM OLD TABLE (DELETE FROM EMPLOYEE
                         INCLUDE (DELETE_TS TIMESTAMP) 
                         SET DELETE_TS = CURRENT TIMESTAMP
                         WHERE EMPNO = '000280');