Field triggers · Asqueel

Scene 01

Six column attributes

A table hook sees the whole record. A field trigger is attached to one column. The column element has six attributes for it. On inserting, on updating and on deleting run before the SQL statement. On inserted, on updated and on deleted run after it. The value of the attribute is a method name or a Python callable. A seventh attribute, trigger table, says on which table to look for the method.

Example & screen notes

slide "Field triggers". Two rows of three boxes.

Row "before the SQL": onInserting, onUpdating, onDeleting Row "after the SQL": onInserted, onUpdated, onDeleted

Bullets: - value: a method name, or a callable - trigger_table: take the method from another table

Scene 02

A method name

When the value is a string, the cycle calls a method named trigger, underscore, and that string. Here the string is product price, so the method is trigger product price. The method receives the record and the column name as field name. It also receives the full name of the table as the keyword table name. During an update it also receives the old record. Accept extra keywords with double-star kwargs, so one method serves every event. In this example, a new invoice row without a unit price takes the price of its product. The method writes into the record, and the value reaches the INSERT.

Example & screen notes

code. The onInserting='productPrice' argument and the method name trigger_productPrice are highlighted with the same colour.

class InvoiceRow(SqlTable):
    def trigger_productPrice(self, record, fldname, **kwargs):
        if record.get(fldname) is None:
            row = self.db.table('invc.product').query(
                columns='$unit_price', where='$id=:p', p=record['product_id']).fetchRecord()
            record[fldname] = row['unit_price']

def invoice_row(pkg):
    tbl = pkg.table('invoice_row', pkey='id', name_long='Invoice row',
                    x_table_class=InvoiceRow)
    ...
    tbl.column('unit_price', dtype='money', onInserting='productPrice')

Box: "called as trigger_<name>(record, fldname=..., tblname=...); update adds old_record=..."

Scene 03

A callable

The value can also be a callable. A callable is called with two positional arguments, the record and the column name. It receives no table name and no old record. Use a method name when you need the table object or the old values. Use a callable for a small function that only looks at the record.

Example & screen notes

code.

def stamp(record, fldname):
    print('callable', fldname)

tbl.column('quantity', dtype='I', onInserting=stamp)

Scene 04

trigger_table

Trigger table moves the method to another table. The vat rate column of an invoice row declares on inserting row vat rate, with trigger table invoicing dot vat type. The cycle then calls the method on the vat type table object, not on the invoice row. Inside the method, self is the vat type table. The table name keyword still says invoice row, the table being written. This keeps the VAT rule in the table that owns VAT data.

Example & screen notes

code. trigger_table='invc.vat_type' highlighted.

class VatType(SqlTable):
    def trigger_rowVatRate(self, record, fldname, tblname=None, **kwargs):
        if record.get(fldname) is None:
            product = self.db.table('invc.product').query(
                columns='$vat_type_code', where='$id=:p', p=record['product_id']).fetchRecord()
            vat = self.query(columns='$vat_rate', where='$code=:c',
                             c=product['vat_type_code']).fetchRecord()
            record[fldname] = vat['vat_rate']

def vat_type(pkg):
    tbl = pkg.table('vat_type', pkey='code', name_long='VAT type', x_table_class=VatType)
    ...

# in invoice_row:
tbl.column('vat_rate', dtype='perc', onInserting='rowVatRate',
           trigger_table='invc.vat_type')

Scene 05

When they run

Field triggers run at a fixed place in the cycle of lesson 21. The before-triggers run after the protections and before the table hook. The after-triggers run right after the SQL statement and before the table hook. Among themselves, field triggers run in the order the columns are declared. Only physical columns carry them.

Example & screen notes

two lists side by side.

Left, "position in the cycle": - onInserting → after protect_validate, before trigger_onInserting - onUpdating → after protect_validate, before trigger_onUpdating - onDeleting → after protect_delete, before trigger_onDeleting - on...ed → right after the SQL, before the table hook

Right, "order among columns": column declaration order.

Scene 06

The result

We insert a row with only the invoice, the product and the quantity. The callable on quantity runs first, because quantity is declared first. Then the product price trigger fills the unit price. Then the VAT trigger, on the vat type table, fills the rate. The record returned by insert holds both values. A query on the table shows the stored row, and the line total formula uses the price. The timestamp keys in the record come from field triggers too. The next scene shows where.

Example & screen notes

code and output.

row = invoice_row.insert({'invoice_id': inv['id'], 'product_id': p['id'], 'quantity': 3})

callable quantity
{'invoice_id': '...', 'product_id': '...', 'quantity': 3, 'id': '...',
 '__ins_ts': ..., '__mod_ts': ..., '__ins_user': None, '__mod_user': None,
 'unit_price': Decimal('10.00'), 'vat_rate': Decimal('20.00')}

[[quantity=3, unit_price=10.00, vat_rate=20.00, line_total=30.00]]

Scene 07

System fields use the same mechanism

The table options of asqueel use field triggers too. The system fields option declares its timestamp and user columns with on inserting and on updating set to set TS now and set current user. The hierarchical option declares its triggers on the parent id column. The methods exist on the base table class. Lesson 25 covers these options.

Example & screen notes

the field-trigger attributes of invc.product_group, printed from the model.

__ins_ts    onInserting='setTSNow'
__mod_ts    onInserting='setTSNow', onUpdating='setTSNow'
__ins_user  onInserting='setCurrentUser'
parent_id   onInserting='hierarchical_before', onUpdating='hierarchical_before',
            onUpdated='hierarchical_after'

Scene 08

Recap

A field trigger is a column attribute. Its value names a method trigger underscore name, or it is a callable with the record and the column name. Trigger table takes the method from another table. Field triggers run in column order, after the protections and before the table hook. The next lesson covers what happens to related rows when you delete or update a record.

Example & screen notes

three bullets. - onInserting ... onDeleted on a column: a method name trigger_<name> or a callable f(record, fldname) - trigger_table takes the method from another table - They run in column order, after the protections and before the table hook

Next: "Lesson 23 — Relation actions: Python and SQL".