Re: [sqlite] Confitional IF in triggers
Thanks Igor - that's very helpful.
--- On Sun, 10/10/10, Igor Tandetnik wrote:
From: Igor Tandetnik
Subject: Re: [sqlite] Confitional IF in triggers
To: sqlite-users@sqlite.org
Received: Sunday, 10 October, 2010, 2:46 AM
Russell A wrote:
> Hi Igor
> I'm converting an Interbase DB for use in a smaller application, so there are
> many different examples.
>
> I've included one particular example below, where I have converted a single
> Interbase trigger (which used IF statements), into 4
> separate SQLite triggers.
> Any advice appreciated.Russell.
> /* Trigger: TEXTLISTS_B4DEL */
> CREATE TRIGGER TEXTLISTS_B4DEL_genericsinfo BEFORE DELETE ON TEXTLISTS
> when (old.listtype in ('W', 'C')) and
> (select TextList_id from genericsinfo where TextList_id = old.textlist_id
> Limit 1) is not null
> begin
> Select Raise(Fail, 'DEPENDANCYERROR');
> end;
Something like this, perhaps:
CREATE TRIGGER TEXTLISTS_B4DEL_genericsinfo BEFORE DELETE ON TEXTLISTS
begin
select raise(FAIL, 'DEPENDANCYERROR')
where old.listtype in ('W', 'C') and exists
(select 1 from genericsinfo where TextList_id = old.textlist_id);
select raise(FAIL, 'DEPENDANCYERROR')
where old.listtype = 'L' and exists
(select 1 from PRODUCTS where LOCATION = old.identifier);
select raise(FAIL, 'DEPENDANCYERROR')
where old.listtype = 'PT' and exists
(select 1 from PRODUCTS where PRODTYPE = old.identifier);
select raise(FAIL, 'DEPENDANCYERROR')
where old.listtype = 'PF' and exists
(select 1 from PRODUCTS where PROD_FORM = old.identifier);
end;
You can also combine the four select statements into one statement, by ORing
all WHERE conditions, or by doing something like this:
select raise(FAIL, 'DEPENDANCYERROR')
where case
when old.listtype in ('W', 'C')
then exists (select 1 from genericsinfo where TextList_id =
old.textlist_id)
when old.listtype = 'L'
then exists (select 1 from PRODUCTS where LOCATION = old.identifier)
when old.listtype = 'PT'
then exists (select 1 from PRODUCTS where PRODTYPE = old.identifier)
when old.listtype = 'PF'
then exists (select 1 from PRODUCTS where PROD_FORM = old.identifier)
end;
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Confitional IF in triggers
Hi Igor
I'm converting an Interbase DB for use in a smaller application, so there are
many different examples.
I've included one particular example below, where I have converted a single
Interbase trigger (which used IF statements), into 4 separate SQLite triggers.
Any advice appreciated.Russell.
/* Trigger: TEXTLISTS_B4DEL */
CREATE TRIGGER TEXTLISTS_B4DEL_genericsinfo BEFORE DELETE ON TEXTLISTS
when (old.listtype in ('W', 'C')) and
(select TextList_id from genericsinfo where TextList_id = old.textlist_id Limit
1) is not null
begin
Select Raise(Fail, 'DEPENDANCYERROR');
end;
/* Trigger: TEXTLISTS_B4DEL */
CREATE TRIGGER TEXTLISTS_B4DEL_PRODLOCATION BEFORE DELETE ON TEXTLISTS
when (old.listtype = 'L') and
(select PRODUCT_ID from PRODUCTS where LOCATION = old.identifier Limit 1) is
not null
begin
Select Raise(Fail, 'DEPENDANCYERROR');
end;
/* Trigger: TEXTLISTS_B4DEL */
CREATE TRIGGER TEXTLISTS_B4DEL_PRODTYPE BEFORE DELETE ON TEXTLISTS
when (old.listtype = 'PT') and
(select PRODUCT_ID from PRODUCTS where PRODTYPE = old.identifier Limit 1) is
not null
begin
Select Raise(Fail, 'DEPENDANCYERROR');
end;
/* Trigger: TEXTLISTS_B4DEL */
CREATE TRIGGER TEXTLISTS_B4DEL_PRODFORM BEFORE DELETE ON TEXTLISTS
when (old.listtype = 'PF') and
(select PRODUCT_ID from PRODUCTS where PROD_FORM = old.identifier Limit 1) is
not null
begin
Select Raise(Fail, 'DEPENDANCYERROR');
end;
--- On Sat, 9/10/10, Igor Tandetnik wrote:
From: Igor Tandetnik
Subject: Re: [sqlite] Confitional IF in triggers
To: sqlite-users@sqlite.org
Received: Saturday, 9 October, 2010, 12:21 AM
Russell A wrote:
> This may be a really dumb question, but I've searched and can't find an
> answer.
> Do SQLite triggers support any conditional expressions, like IF, or is there
> only the WHEN statement? If the latter, does that
> mean that multiple conditions must be in separate triggers? Any help
> appreciated.Stopgap.
What kind of conditions do you have in mind? Could you give an example? There
is no IF statement in SQLite, but one can get creative with the WHEN clause on
the trigger itself, and the WHERE clauses of individual statements within the
trigger.
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Confitional IF in triggers
This may be a really dumb question, but I've searched and can't find an answer. Do SQLite triggers support any conditional expressions, like IF, or is there only the WHEN statement? If the latter, does that mean that multiple conditions must be in separate triggers? Any help appreciated.Stopgap. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Trigger Variables
I'm porting an Interbase DB to SQLIte and wondered if there is any way to store temporary values within a trigger? Any help appreciated.Slurcher. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
