Caché SQL Reference
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   

A scalar function that returns the identity of the last row inserted, updated, deleted, or fetched.
The LAST_IDENTITY function returns the %ROWID local variable value. The %ROWID local variable is set to a value in Embedded SQL or ODBC. The %ROWID local variable is not set to a value by Dynamic SQL, the SQL Shell, or the Management Portal SQL interface. Dynamic SQL instead sets a %ROWID object property.
The LAST_IDENTITY function takes no arguments. Note that the argument parentheses are required.
LAST_IDENTITY returns the IDENTITY field value of the last row affected by the current process. If the table has no IDENTITY field, it returns the row ID (%ROWID) of the last row affected by the current process. The returned value is data type INTEGER.
At process initiation, LAST_IDENTITY returns NULL. Following a NEW %RowID, LAST_IDENTITY returns NULL.
If no rows were affected by an operation, LAST_IDENTITY is not changed; LAST_IDENTITY returns its prior value (if any). Following a NEW %RowID, invoking LAST_IDENTITY returns NULL, but invoking %ROWID generates an <UNDEFINED> error.
For further details on IDENTITY fields, see CREATE TABLE. For further details on %ROWID, see the “Embedded SQL” chapter of Using Caché SQL.
The following example uses two Embedded SQL programs to return LAST_IDENTITY. The first example creates a new table Sample.Students, The second example populates this table with data, then performs a cursor-based SELECT on the data, returning LAST_IDENTITY for each operation.
Please run the two Embedded SQL programs in the order shown. (It is necessary to use two embedded SQL programs here because embedded SQL cannot compile an INSERT statement unless the referenced table already exists.)
   WRITE !,"Creating table"
  &sql(CREATE TABLE Sample.Students (
    StudentName VARCHAR(30),
    StudentAge INTEGER,
    StudentID IDENTITY))
    WRITE !,"Created table, SQLCODE=",SQLCODE }
    WRITE !,"Table already exists, SQLCODE=",SQLCODE }
  WRITE !,"Populating table"
  &sql(INSERT INTO Sample.Students (StudentName,StudentAge)
     SELECT Name,Age FROM Sample.Person WHERE Age <= '21')
    WRITE !,%ROWCOUNT," records added, last RowID is ",%ROWID,! }
  ELSE {
    WRITE !,"Insert failed, SQLCODE=",SQLCODE }
       INTO :insertID
       FROM Sample.Students)
    WRITE !,"INSERT Last Identity is: ",insertID,!!
   /* Cursor-based SELECT Query */
       &sql(DECLARE C1 CURSOR FOR 
       SELECT StudentName INTO :name FROM  Sample.Students
       WHERE StudentAge = '17')
  &sql(OPEN C1)
  &sql(FETCH C1)
     WRITE name," is seventeen",!
    &sql(FETCH C1) }
  &sql(CLOSE C1)
   WRITE !,%ROWCOUNT," records queried, last RowID is ",%ROWID,!
       INTO :qId)
    WRITE !,"SELECT Last Identity is: ",qId,!
  &sql(DROP TABLE Sample.Students)
See Also