Lianja Native DB Table Triggers

[robertjacobs]

Hi

I am playing with the trial version. I imported a VFP DB to a Lianja DB and now I want to duplicate VFP functionality for the database. The Lianja tables and indexes look fine however there is no code imported for the VFP Referential Integrity Builder which is where insert/update/delete triggers are defined.

My question is how would you code an OnBeforeDelete Table trigger to prevent deleting a parent record which is related to multiple child tables? Would you need to USE each child table and then SEEK the parent key?

For a test... From the Data workspace when I browse a parent table I do not want to be able to delete a record if child records exist.

[barrymavin]

Hi Robert

There are various ways to handle that in lianja.

You can do it in the UI using before/after delegates.

You can also have it handled by the database engine itself.

The GUI provides the ability for you to define triggers (the triggers tab for a table) or alternatively you can set them up in the Console using ALTER TABLE.

There are a lot of CONSTRAINTS which you can specify for tables and/or columns to handle referential integrity.
https://www.lianja.com/doc/index.php/Category:SQL

You also have the luxury of being able to attach METADATA to databases, tables and columns using the MetaData Editor in the App Builder. You can then reference the metadata in your triggers.
https://www.lianja.com/doc/index.php/MetaData_Editor

[robertjacobs]

Hi Barry

If I wrote this code in the OnBeforeDelete table trigger is this most efficient way to check if a child record exists for a parent key. Of course this code would be repeated for each child table.

USE childtable1 ORDER TAG parentkey
SEEK parentkey
IF FOUND("childtable1")
RETURN .F.
ENDIF
USE childtable2 ORDER TAG parentkey
...

I have no idea how VFP does this internally so maybe there is a better way. Also I don't see the function INDEXSEEK which allows searching a record without moving the record pointer.

[josipradnik]

Hi robertjacobs,

My two cents:

RLOOKUP() function is especially useful when used in the Applications Data Dictionary (ADD) for maintaining referential integrity rules

link: http://www.lianja.com/doc/index.php/RLOOKUP()

[robertjacobs]

Hi josipradnik (sorry for not knowing your exact name)

We have a primary table which has many child tables. In this case we want to prevent deleting the primary record if there are historical records in any of the child tables. Without a table trigger if you were to Browse the primary table and delete a record you will be left with orphan records in the child tables.

I am not familiar with RLOOKUP since there is no help topic.

[barrymavin]

Hi Robert

For the purposes of a delete trigger you can use RLOOKUP() or KEYMATCH() or REFERENCES(). Lianja does not have INDEXSEEK() but it seems to do the same as these other functions. If you want to see that function implemented please submit an ER (Enhancement Reqest) as a ticket.

If you go to the doc wiki. Type rlookup in the search box on the top right.
https://www.lianja.com/doc/index.php?search=Rlookup

You will then get a wide range of varying functions and ways to perform referential integrity.

Also, FYI just to make you aware, if you type in the editor or console :

rlookup(

then press F1 the doc wiki will be searched from within Lianja and the doc displayed in a browser window.

The same goes for a command.

e.g.

gather<F1>

[barrymavin]

Hi Robert

See my post below and also look at sqleval()

[barrymavin]

Hi Robert,

After investigating this it seems that INDEXSEEK() behaves just like KEYMATCH() for the scenario that you want to use it for.

[robertjacobs]

Hi Barry

I have looked further into VFP and I now see that VFP uses Table Relationships and the Referential Integrity Builder to generate internal .prg code for Insert/Update/Delete trigger constraints. Likewise SQL Server uses ALTER TABLE to define either CASCADE or NO ACTION for Update and Delete constraints.

I thought I saw an example somewhere in Lianja (but maybe I am wrong) using ALTER TABLE to relate primary key and foreign key however I am not sure if this allowed you to set constraints for Delete and Update Triggers.

[josipradnik]

Hi Robert,

As I understand, you are not concerned about cascading delete, but a "restrict" delete RI

RI rules for deletes based on "restrict" (don't allow a delete where a cascade would delete a child based on a given relationship)

You will need a code in trigger.

To be of some help here, is this the thread you are talking about (post #10)?
https://www.lianja.com/community/sho...ll=1#post14899

Josip