Thanks Igor - that's very helpful.
--- On Sun, 10/10/10, Igor Tandetnik <itandet...@mvps.org> wrote:
From: Igor Tandetnik <itandet...@mvps.org>
Subject: Re: [sqlite] Confitional IF in triggers
To: sqlite-users@sqlite.org
Received: Sunday, 10 October, 2010, 2:46 AM
Russell A <slurc...@yahoo.com.au> 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
