Here's a snippet of code from the SQL Anywhere database setup script for a small application. The settings table is a place to store global configuration parameters separate from the application code:Captain Ramius: Re-verify our range to target... one ping only.
Capt. Vasili Borodin: Captain, I - I - I just...
Captain Ramius: Give me a ping, Vasili. One ping only, please.
Capt. Vasili Borodin: Aye, Captain.
CREATE TABLE settings (Once this code's been run, the settings table is available for SELECT and UPDATE but the following operations are impossible:
one_row_only INTEGER NOT NULL DEFAULT 1
CHECK ( one_row_only = 1 )
no_picture_yet_image_folder LONG VARCHAR NOT NULL,
no_picture_yet_image_file_name LONG VARCHAR NOT NULL );
CREATE TRIGGER one_row_only BEFORE DELETE ON settings
FOR EACH ROW
ROLLBACK TRIGGER WITH RAISERROR 99999
'Do not attempt to delete from settings';
INSERT settings VALUES (
- inserting another row (the CHECK and PRIMARY KEY constraints),
- changing the primary key value (the CHECK constraint), or
- deleting the row (the TRIGGER).
INSERT settings VALUES ( 1, 'Hello', 'World' ); -- primary key violationIn other words, there will always be exactly one row in this table, and that fact is guaranteed by the the database itself... perfect for a global configuration settings table.
INSERT settings VALUES ( 2, 'Hello', 'World' ); -- check condition is false
UPDATE settings SET one_row_only = 3; -- check condition is false
DELETE settings; -- trigger catches it
SELECT * FROM settings;
UPDATE settings SET no_picture_yet_image_file_name = 'future.jpg';