The following rules and recommendations apply to consuming rows from a queue table (see
Teradata Vantage™ - SQL Data Manipulation Language, B035-1146 for a complete description of the rules and limits for consuming queue table rows).
- To consume rows from a queue table, specify the AND CONSUME TOP 1 keywords in your SELECT statement. The AND CONSUME keywords indicate that the request is a consume mode request, while TOP 1 indicates that the oldest row from the queue table is to be retrieved.
The following example consumes a row from a queue table named shopping_cart.
SELECT AND CONSUME TOP 1 * FROM shopping_cart;
- You can also consume rows from stored procedures and embedded SQL applications that use the INTO clause to assign the values from a row to host or local variables.
- You should place any action taken based on consuming a row from a queue table in the same transaction as the consume mode SELECT operation on that same queue table. This ensures that both the row consumption and the action taken on that queue table are committed together, so no row or action for that queue table is lost.
If no action is to be taken, then you should isolate any SELECT AND CONSUME statement as the only request in a transaction. See Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142.
- A multistatement request should contain only 1 consume mode SELECT statement.
- A trigger can call a stored procedure that consumes a row from a queue table.