Sql trigger for updating one table from another

Triggers are automatically dropped when the table that they are associated with (the table-name table) is dropped.

However if the trigger actions reference other tables, the trigger is not dropped or modified if those other tables are dropped or modified.

Both the WHEN clause and the trigger actions may access elements of the row being inserted, deleted or updated using references of the form "NEW.column-name" and "OLD.column-name", where column-name is the name of a column from the table that the trigger is associated with.

Note how the values assigned to the view are made available as field in the special "NEW" table within the trigger body.

If a BEFORE UPDATE or BEFORE DELETE trigger modifies or deletes a row that was to have been updated or deleted, then the result of the subsequent update or delete operation is undefined.

Note that the sqlite3_changes() and sqlite3_total_changes() interfaces do not count INSTEAD OF trigger firings, but the count_changes pragma does count INSTEAD OF trigger firing.

Assuming that customer records are stored in the "customers" table, and that order records are stored in the "orders" table, the following UPDATE trigger ensures that all associated orders are redirected when a customer changes his or her address: CREATE TABLE customer( cust_id INTEGER PRIMARY KEY, cust_name TEXT, cust_addr TEXT ); CREATE VIEW customer_address AS SELECT cust_id, cust_addr FROM customer; CREATE TRIGGER cust_addr_chng INSTEAD OF UPDATE OF cust_addr ON customer_address BEGIN UPDATE customer SET cust_addr=NEW.cust_addr WHERE cust_id=NEW.cust_id; END; Causes the customer.cust_addr field to be updated for a specific customer entry that has customer.cust_id equal to the $cust_id parameter.

Hence explicitly specifying FOR EACH ROW is optional.

FOR EACH ROW implies that the SQL statements specified in the trigger may be executed (depending on the WHEN clause) for each database row being inserted, updated or deleted by the statement causing the trigger to fire.

An error code of SQLITE_CONSTRAINT is returned to the application, along with the specified error message.

When RAISE(IGNORE) is called, the remainder of the current trigger program, the statement that caused the trigger program to execute and any subsequent trigger programs that would have been executed are abandoned. If the statement that caused the trigger program to execute is itself part of a trigger program, then that trigger program resumes execution at the beginning of the next step.

COLUMNS_UPDATED is used anywhere inside the body of a Transact-SQL INSERT or UPDATE trigger to test whether the trigger should execute certain actions.

Tags: , ,