On Sun, 9 May 2004, Raymond Irving wrote:
>
>How about being able to add a trigger to the Database?
>
>CREATE TRIGGER db_inserts INSERT ON
>DATABASE|{database_name}
>BEGIN
> ....
>END
You'd want seperate update, create and insert triggers, as I believe you
can only trigger on a table/view, not a generic database event.
Docs:
http://www.sqlite.org/lang.html#createtrigger
More below.
>
>--- Chris Waters <[EMAIL PROTECTED]>
>wrote:
>> Hi,
>>
>> I need the ability to tell if changes have been made
>> in the database. Kind
>> of like the LISTEN capability in Postgresql. In
>> particular I need to know
>> if:
>>
>> * A row was inserted.
>> * A row was deleted.
>> * A row was modified.
>>
>> I would like to implement this through a callback
>> that an application could
>> register. The callback would specify the operation
>> that was performed, the
>> affected table and maybe the rowid affected.
Create a function using sqlite_create_function() (Section 4.0 of
http://www.sqlite.org/c_interface.html) and in the trigger body, call the
function using select. To use a modified version of the docs example:
CREATE TRIGGER update_customer_address UPDATE OF address ON customers
BEGIN
SELECT my_c_callback( old.name )
END;
The my_c_callback function set up with sqlite_create_function() would be
called with a single argument (the old name column, in this case) for each
row updated.
You'd want something similar for INSERT and DELETE.
>>
>> If the rowid is reported then for operations that
>> affect a large number of
>> rows there would be a performance penalty to call
>> the callback per row. For
>> my application this is not a problem, but perhaps
>> when the callback is
>> specified the application could indicate whether it
>> wants per row, or per
>> table notification.
The is no trigger mechanism for per statement, only per row. So you'll
have no choice.
>>
>> I am looking for ideas on where I should hook into
>> the source code to add
>> this functionality. From what I have seen so far it
>> looks like I could
>> change the implementation of the Delete and
>> MakeRecord VM instructions to do
>> the callback.
Don't. Use sqlite_create_function().
>>
>> I haven't thought through the timing implications
>> yet. Ideally when a row is
>> inserted, code in the callback could query the newly
>> inserted row. I am not
>> 100% sure at what point the row becomes visible for
>> query.
If you have the trigger as a pre-trigger, the database will appear to be
before any modifications. Conversely, a post-trigger will have the updates
applied in the current transaction.
>>
>> I would welcome any insight since this is my first
>> attempt (of many I have
>> lots of features I want :-) to modify the sqlite
>> code.
<recommendation>
Don't make custom modifications unless they're are sufficiently generic
enough for other uses. Else, you'll probably have to maintain a patch set
against any future SQLite updates.
>From your query, nothing you want to do cannot be done already using
the existing generic mechanisms.
</recommendation>
>>
>> Thanks,
>>
>> Chris.
>>
Christian
--
/"\
\ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
