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

Reply via email to