Re: Conditionally executing multiple statements in series as single SQL statement

On Fri, Dec 18, 2009 at 8:01 AM, Nathaniel Trellice <naptrel@yahoo.co.uk> wrote:
> Hi all,
>
> In C, and many other programming languages, statements like the following are popular:
>
> int status = (do_first_thing() && do_second_thing() && do_third_thing() && do_fourth_thing());
>
> With this kind of expression, the program calls the function 'do_first_thing'. If, and only if, that returns
non-zero,'do_second_thing' will be executed. Again, if and only if that returns non-zero, 'do_third_thing' is executed.
Etc.
>
> In other words, later statements will only be executed if all before them have 'gone well'. When a statement 'fails',
nofurther expressions are executed.. The variable 'status' is non-zero if, and only if, all four things were
successfullyexecuted. 
>
> For convenience, I'd really like to be able to achieve similar behaviour within an SQL statement, i.e. present
multiplestatements (such as INSERT statements) and only execute the later ones if the earlier ones have been executed
withouterror. And I'd like to be able to present all the statements within a single, compound SQL statement to the
database.
>
> Is such a thing possible, using any fancy SQL syntactic tricks?

No tricks necessary.  What you are describing is called a transaction.

CREATE TABLE testing (
  id integer,
  name text unique
);

BEGIN;
INSERT INTO testing(id,name) values (1,'Bob');
INSERT INTO testing(id,name) values (2,'Joe');
INSERT INTO testing(id,name) values (3,'Sally');
COMMIT;

BEGIN;
INSERT INTO testing(id,name) values (4,'Ann');
-- the next statement will cause an error
-- due to violation of the unique constraint
INSERT INTO testing(id,name) values (5,'Bob');
-- We do a rollback, which will put the database
-- back into the state it was in just before the
-- second BEGIN statement
ROLLBACK;

SELECT * FROM TESTING;

See the documentation and Google about transactions.

Sean