sqLite triggers

This is the place to post technical queries about SQL Yoga

Moderators: FourthWorld, heatherlaine, Klaus, robinmiller, trevordevore

Post Reply
trevordevore
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 1005
Joined: Sat Apr 08, 2006 3:06 pm
Location: Overland Park, Kansas
Contact:

sqLite triggers

Post by trevordevore » Fri Oct 11, 2013 2:34 pm

[This post is an archive from the old SQL Yoga forums]

In the manual it says:

"While some databases will take care of deleting the records for you, SQLite will not. This is where Triggers come into play. A Trigger is a way of performing operations in a database when certain events occur. If you look at the Triggers for the to-do application database you will see that there are three of them. This triggers delete to-dos when a project is deleted and remove the link between people/to-dos when either one is deleted."

I can;t find the triggers in the db behavior script (which is where I thought I'd find some code), and it would be great to have a lesson on how these work. I'm not clear when deleting records, whether it is necessary to remove linked keywords etc.
Trevor DeVore
ScreenSteps - https://www.screensteps.com

LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder

trevordevore
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 1005
Joined: Sat Apr 08, 2006 3:06 pm
Location: Overland Park, Kansas
Contact:

Re: sqLite triggers

Post by trevordevore » Fri Oct 11, 2013 2:35 pm

Ultimately it is up to you to make sure that linked records get removed one way or the other. SQL Yoga will not do this for you automatically.

A trigger is one approach but a trigger is a database feature, not a SQL Yoga feature. Here are the docs for CREATE TRIGGER in SQLite. If you are just using SQLite then setting up triggers is the way to go. Whenever you delete a record in the database SQLite will automatically call the trigger that executes SQL to delete related records.

Another approach is to use "after delete listeners" property of a table object. I looked and I have listed this property in the docs but not defined how to set it or what it does. I will update that when I post the next version.

You set a listener by passing in a message to be sent followed by a comma followed by the target to send the message to. For example, you could put this in your table objects behavior script:

Code: Select all

tableobj_set "steps", "after delete listeners", "AfterDeleteMessage," & the long ID of me

Whenever you call sqlrecord_delete this message will be triggered. Parameter 1 will be the name of the table. Parameter 2 will be the SQL Record object that was deleted. You can use the id in this record to delete any related records in other tables. This approach has the benefit of being database agnostic but it does move the logic that maintains the integrity of the data out of the database which some people frown upon.

IMPORTANT: the message will NOT be sent if you use sqlquery_delete. It is only sent when using the object interface in SQL Yoga.
Trevor DeVore
ScreenSteps - https://www.screensteps.com

LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder

trevordevore
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 1005
Joined: Sat Apr 08, 2006 3:06 pm
Location: Overland Park, Kansas
Contact:

Re: sqLite triggers

Post by trevordevore » Fri Oct 11, 2013 2:35 pm

Great - thanks. I'll look into sqlite CREATE TRIGGER

It may be a good idea to make a minor addition to p94 of the TO-Do manual / pdf - and change "If you look at the Triggers for the to-do application database" to something that points the reader to look at the database itself and not code in the app?
Trevor DeVore
ScreenSteps - https://www.screensteps.com

LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder

trevordevore
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 1005
Joined: Sat Apr 08, 2006 3:06 pm
Location: Overland Park, Kansas
Contact:

Re: sqLite triggers

Post by trevordevore » Fri Oct 11, 2013 2:35 pm

Doesn't "look at the database" mean look at the database itself? :-)

Also, the manual has a picture of the triggers as they appear in a SQLite database editor. See this page in the online manual: http://revolution.screenstepslive.com/s ... o-Do-Items
Trevor DeVore
ScreenSteps - https://www.screensteps.com

LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder

trevordevore
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 1005
Joined: Sat Apr 08, 2006 3:06 pm
Location: Overland Park, Kansas
Contact:

Re: sqLite triggers

Post by trevordevore » Fri Oct 11, 2013 2:35 pm

Oh my word - how did I miss that :) not being on the same page of the pdf is not really an excuse - think I will have another coffee!
Trevor DeVore
ScreenSteps - https://www.screensteps.com

LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder

Post Reply

Return to “SQL Yoga”