I’ve have two event queues(table mapped queues) based on two different states of same data stored in two different tables. The events are generated on create/update/delete on both the tables.
Constrain is that both the tables have to be in sync. The create/update/delete in one table has to be reflected into other table.
So the question is that should i use trigger to queue events in table or application/object to queue events in table? and Why?
Note:
- Update on either is capable of generating 3 different types of events. So on application layer extra diff logic would be required to generate correct event.
- The Negative thing in trigger is that it will introduce duplicate events from both sides. i.e. if some event is processed on one table it will create an event for processing on other table.
I wrote this stuff a long time ago for a C++ application and made a post on my blog. Here’s a quick summary (since the original article is quite long and contains file attachments):
In SQLite you write callbacks in raw C++:
Header file (file.h)
#include <iostream>
#include <sqlite3.h>
#include <cstdlib>
#include <assert.h>
void memberfunction(const unsigned char* text1,
const unsigned char* text2,
const unsigned char* text3);
void mcallback(sqlite3_context* context, int argc, sqlite3_value** argv);
It defines the two additional functions we’ll need for the next step (actually our C++ side function and a SQLite stored procedure).
Implementation file (file.cpp)
This function will be called from our trigger from the DB.
void memeberfunction(const unsigned char* text1,
const unsigned char* text2,
const unsigned char* text3)
{
cout << "inserted: " << text1 << " " << text2 << " " << text3 << "n";
}
And now a SQLite stored procedure to call our function; a SQLite trigger will invoke this.
void mcallback(sqlite3_context* context, int argc, sqlite3_value** argv)
{
assert(argc == 3);
//SQLite types must be converted to C++ types
const unsigned char* text1 = sqlite3_value_text(argv[0]);
const unsigned char* text2 = sqlite3_value_text(argv[1]);
const unsigned char* text3 = sqlite3_value_text(argv[2]);
memeberfunction( text1, text2, text3);
}
We don’t have to limit the function to just a simple print; this function could have (for example) moved data to another table.
Register the stored procedure to the DB
error = sqlite3_create_function(conn, "mcallback", 3, SQLITE_UTF8, NULL,
&mcallback, NULL, NULL);
Create the SQLite trigger
We need the stored procedure since triggers are built on top of them.
error = sqlite3_exec(conn, "create temp trigger callbacktrigger "
"after insert on hello for each row "
"begin "
"select mcallback(new.first_column, "
"new.second_column, new.exclamation_pt); "
"end;", 0, 0, 0);
The trigger is only temporary. This way it won’t stick around in the database after execution terminates (of our whole program). Our callback won’t be around after then anyway and the trigger could (and will, given it’s nature) cause major problems if we use the database outside this application.
4