SQLite Expert Forum - SQLite trigger usage

Welcome, Guest. Please Login.

	05/19/13 at 17:24:05 News: Welcome to the SQLite Expert support forum!

SQLite Expert Forum › SQLite Expert Support › General support questions
(Moderators: Bogdan Ureche, george)

‹ Previous topic | Next topic ›

Pages: 1

SQLite trigger usage (Read 2262 times)

Jen

Newbie

Posts: 3

SQLite trigger usage
09/14/11 at 23:36:52

Consider the following dataset is present in a table(t) in the database test.db.
column1 column2 column3
1 abc 3
2 cdf 4
3 fgh 3
4 hij 7
5 jkl 4
6 lmn 6
7 nop 4
8 pqr 8
9 rst 7
10 tuv 9

This database, test.db, has another table(trigger_test) which is empty. The values imported into this table is automatically done and may or may not be present in the table t of the database. I want to set up a TRIGGER on this table(i.e. trigger_test) such that when a value is imported into the table, and if it is present in the t, then it should return to me the whole line of information from the table t.

For example: if the value imported into the table trigger_test is

column2 column3
rst 7

then it should return to me
9 rst 7
4 hij 7

Preferably in a descending order of column1, i.e the line with a higher value in column1 should be shown first.
Could you please enlighten me as to how I can apply this, using SQLite, in the TRIGGER command.

IP Logged

Bogdan Ureche Administrator Posts: 30 Gender:	Re: SQLite trigger usage Reply #1 - 09/15/11 at 00:02:43 Can you please elaborate on "it should return to me the whole line of information..."? How do you expect the trigger to return this line? It could insert it into another table if that works for you.
Back to top	Bogdan Ureche IP Logged

Jen Newbie Posts: 3	Re: SQLite trigger usage Reply #2 - 09/15/11 at 00:20:06 Ya i think puttin the whole line from the table t into another table will work fine. can that be done using trigger command???
Back to top	IP Logged

Bogdan Ureche

Administrator

Posts: 30
Gender: male

Re: SQLite trigger usage
Reply #3 - 09/15/11 at 00:40:03

Something like this should work.

CREATE TABLE [t] (
  [column1] CHAR,
  [column2] CHAR,
  [column3] CHAR);

CREATE TABLE [t2] (
  [column1] CHAR,
  [column2] CHAR,
  [column3] CHAR);

CREATE TABLE [trigger_test] (
  [column2] CHAR,
  [column3] CHAR);

CREATE TRIGGER [trigger_test_after_insert]
AFTER INSERT
ON [trigger_test]
FOR EACH ROW
BEGIN
  insert into t2 select * from t where t.column3 = new.column3 order by column3 desc;
END;

Another solution:
If you want the trigger to return the values to the application that uses the sqlite library, you can try a more complex approach:

1. Install a custom function, something like record_added(column1, column2, column3). This will act like an event handler for an event triggered by the library.
2. Call the function from inside the trigger.

Bogdan Ureche

IP Logged

Jen Newbie Posts: 3	Re: SQLite trigger usage Reply #4 - 09/15/11 at 03:29:25 Another thing, if i have another table(eg. t1) with a dataset related to the data in table t in the same database, then can i make the same trigger check for the information from both these tables and give me the result in descending order if it is present in both the tables but with a different column1??
Back to top	IP Logged

Bogdan Ureche

Administrator

Posts: 30
Gender: male

Re: SQLite trigger usage
Reply #5 - 09/15/11 at 09:34:52

If I understood correctly, you want something like this:

CREATE TABLE [t] (
[column1] CHAR,
[column2] CHAR,
[column3] CHAR);

CREATE TABLE [t1] (
[column1] CHAR,
[column2] CHAR,
[column3] CHAR);

CREATE TABLE [t2] (
[column1] CHAR,
[column2] CHAR,
[column3] CHAR);

CREATE TABLE [trigger_test] (
[column2] CHAR,
[column3] CHAR);

CREATE TRIGGER [trigger_test_after_insert]
AFTER INSERT
ON [trigger_test]
FOR EACH ROW
BEGIN
insert into t2
select * from t
where new.column3 = t.column3
and new.column3 in (select column3 from t1)
order by column1 desc;
END;

unless you want to insert the rows from both tables in which case you should change the trigger like this:

CREATE TRIGGER [trigger_test_after_insert]
AFTER INSERT
ON trigger_test
FOR EACH ROW
BEGIN
insert into t2
select * from t
where new.column3 = t.column3
and new.column3 in (select column3 from t1)
union
select * from t1
where new.column3 = t1.column3
and new.column3 in (select column3 from t)
order by column1 desc;
END;

Bogdan Ureche

IP Logged

Pages: 1

SQLite Expert Forum › SQLite Expert Support › General support questions
(Moderators: Bogdan Ureche, george)

‹ Previous topic | Next topic ›