Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Lianja Native DB Table Triggers

  1. #1
    Member
    Join Date
    Feb 2012
    Location
    Ontario, Canada
    Posts
    98

    Lianja Native DB Table Triggers

    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.

  2. #2
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    6,719
    Blog Entries
    17
    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
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

  3. #3
    Member
    Join Date
    Feb 2012
    Location
    Ontario, Canada
    Posts
    98
    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.

  4. #4
    Lianja MVP
    Join Date
    Dec 2012
    Location
    Croatia, Zagreb
    Posts
    1,129
    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()
    Last edited by josipradnik; 2017-06-15 at 14:12.

  5. #5
    Member
    Join Date
    Feb 2012
    Location
    Ontario, Canada
    Posts
    98
    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.

  6. #6
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    6,719
    Blog Entries
    17
    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>
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

  7. #7
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    6,719
    Blog Entries
    17
    Hi Robert

    See my post below and also look at sqleval()
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

  8. #8
    Lianja Development Team barrymavin's Avatar
    Join Date
    Feb 2012
    Location
    UK, USA, Thailand
    Posts
    6,719
    Blog Entries
    17
    Hi Robert,

    After investigating this it seems that INDEXSEEK() behaves just like KEYMATCH() for the scenario that you want to use it for.
    Principal developer of Lianja, Recital and other products

    Follow me on:

    Twitter: http://twitter.com/lianjaInc
    Facebook: http://www.facebook.com/LianjaInc
    LinkedIn: http://www.linkedin.com/in/barrymavin

  9. #9
    Member
    Join Date
    Feb 2012
    Location
    Ontario, Canada
    Posts
    98
    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.

  10. #10
    Lianja MVP
    Join Date
    Dec 2012
    Location
    Croatia, Zagreb
    Posts
    1,129
    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
    Last edited by josipradnik; 2017-06-16 at 11:58.

Bookmarks

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Journey into the Cloud
Join us