You need to insert the deleted row of a table into a history table for audit purposes. What is the most efficient method to achieve this?

A database trigger is the most efficient method for this requirement. A trigger is a specialized stored procedure that executes automatically in response to a specific data modification event (e.g., DELETE) on a table. By using an AFTER DELETE trigger, the deleted row's data can be captured and inserted into a history table within the same database transaction. This approach is highly efficient as it occurs at the database level, minimizing network latency and application server overhead, and ensuring the audit action is atomic with the deletion.

A. Materialized view: A materialized view is a physical copy of query results used for performance, not an event-driven mechanism to capture real-time row deletions for auditing.

B. Process model: Using an Appian process model would involve reading the data, writing it to the history table, and then deleting it. This adds significant application-level overhead and is less performant than a native database trigger.

C. View: A standard view is a virtual table based on a query. It does not store data and cannot be used to capture a row after it has been deleted from the base table.

1. Appian Documentation - Database Performance Best Practices: While not explicitly naming triggers

the documentation emphasizes the principle of efficiency. It states

"When possible

perform data-intensive operations in the database rather than in process memory." A trigger perfectly aligns with this principle by handling the audit logging directly within the database

which is more efficient than pulling data into an Appian process model to perform the same logic. (See the section on "Use the Database to Handle Large Data Sets").

2. Appian Documentation - Write to Data Store Entity Smart Service: This documentation outlines the Appian-based method for deleting data. To achieve the audit requirement using this

a developer would need to chain multiple smart services (e.g.

query

write to history

delete from primary). This multi-step

application-layer process is inherently less efficient than a single

atomic database-level trigger.

3. Silberschatz

A.

Korth

H. F.

& Sudarshan

S. (2020). Database System Concepts (7th ed.). McGraw-Hill. Chapter 9

"Triggers

" explains that a primary use case for triggers is to maintain audit trails and log records. It states

"Triggers can be used to log modifications to a relation. The log can be stored in a separate relation... When a tuple is deleted

a trigger can insert a copy of the tuple into the log relation." This foundational database text confirms that triggers are the standard

intended mechanism for this task.