Lianja Native DB Table Triggers

[robertjacobs]

Hi J

The more I think about it RI rules should be handled through the DB independent of the application interface. For example, if I open a primary table and then Browse and then delete a record RI rules should be enforced. If Delete is allowed because no "restrict" then it is cascading for all sub tables as defined through its relationships. How does Lianja native DB know what these are? What if a record is deleted via ODBC? Does Lianja SQL Server work the same way?

[barrymavin]

Hi Robert

Lianja tables have an associated Active Data Dictionary (ADD). Constraints and triggers that you specify on a table are in the ADD. Whenever a table is opened it's ADD is also read and all the business rules are associated with that table automatically for you. If you create your triggers through the data workspace in the App Builder they will be stored in the database container. Any triggers called are looked for in the database container. So no matter where you use the database table, whether it be desktop, cloud, sql server, ODBC or whatever the business rules are applied.

As I mentioned in a previous post you can attach metadata to a database, table or colum. This is also stored in the ADD.

What this means is that you can associate meaningful information with a table which can be read in your triggers and stored procedures,

For example let's say you have a customer table and you want to handle cascading deletes of orders and order details. (By the way cascading deletes are handled in the UI if you specify "Cascading deletes" attribute on a section.)

you could have a generic delete trigger that when called reads the metadata string and using a CHILDREN keypair and CHILDKEYS keypair your trigger can cascade the deletes.

If you access this database across different operating systems possibly even copying the database everything will still work as the tables, data, ADD and METADATA are all stored in the database container.

[robertjacobs]

Hi Barry

Just so I am clear on this - I will need to code an OnBeforeDelete trigger for primary table to...

1) If there are child tables which retrict parent deletion then check each of these tables to ensure there are no records related to parent key.
2) If no restrictions found in 1) then delete records in each child table.

[HankFay]

Hi Robert,

I think you are referring to the Foreign Key column constraint (specify the parent table, index gets automatically built).

This does not allow specification of the type of relational update or delete. If you have that information available, you could store it in the column metadata, however. It's more practical, however, to put that information in metadata in the parent table metadata.

Hank