Rob Richardson <[email protected]> wrote:
> The example of a CREATE TRIGGER statement from the help page is:
>
> CREATE TRIGGER update_customer_address UPDATE OF address ON customers
> BEGIN
> UPDATE orders SET address = new.address WHERE customer_name = old.name;
> END;
>
> The use of BEGIN and END to wrap the statement leads me to believe that it's
> possible to have more than one statement between
> them.
Yes it is.
> And is it possible to have a conditional statement?
There are no conditional statements in SQL. But there are WHERE clauses on DML
statements, and WHEN clause on CREATE TRIGGER statement
> CREATE TRIGGER record_big_order AFTER INSERT ON orders
> BEGIN
> IF new.value > 1000000 THEN
> INSERT INTO big_orders (customer_name, salesman_id, value)
> VALUES (new.customer_name, new.salesman_id, new.value)
> END IF;
> END;
You can make it either
CREATE TRIGGER record_big_order AFTER INSERT ON orders
WHEN new.value > 1000000
BEGIN
INSERT INTO big_orders (customer_name, salesman_id, value)
VALUES (new.customer_name, new.salesman_id, new.value);
END;
or
CREATE TRIGGER record_big_order AFTER INSERT ON orders
BEGIN
INSERT INTO big_orders (customer_name, salesman_id, value)
SELECT new.customer_name, new.salesman_id, new.value
WHERE new.value > 1000000;
END;
The former should be a bit more efficient, but you can only have one condition
for the whole trigger. With the latter technique, you could place conditions on
each individual statement inside the trigger.
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
