Here’s a little bit of fun I thought up as part of a SQL game I was working on at Oracle. It’s a simple use of a trigger and a global temporary table to create a “combination lock” that any logged-in session can try to “open”.
To use it:
SQL> INSERT INTO combinationlock (combination) VALUES ('Test');
1 ROW created.
SQL> SELECT * FROM combinationlock;
COMBI LOCK_STATE
----- --------------------------------------------------
Test Closed
SQL> UPDATE combinationlock SET combination='99999';
1 ROW updated.
SQL> SELECT * FROM combinationlock;
COMBI LOCK_STATE
----- --------------------------------------------------
99999 Closed
SQL> SELECT * FROM combinationlock;
COMBI LOCK_STATE
----- --------------------------------------------------
12345 OPEN Sesame
To create:
DROP TABLE combinationlock;
CREATE global TEMPORARY TABLE combinationlock (combination varchar2(5), lock_state varchar2(50))
ON commit preserve ROWS;
DROP TRIGGER combinationlock_trig ;
CREATE TRIGGER combinationlock_trig
BEFORE INSERT OR UPDATE ON combinationlock
FOR each ROW
BEGIN
IF :NEW.combination = '12345'
THEN :NEW.lock_state := 'Open Sesame';
ELSE :NEW.lock_state := 'Closed';
END IF;
exception
WHEN others THEN
:NEW.lock_state := 'Closed';
raise;
END;
/
The nice thing about this is that, since it’s using a global temporary table, any user can try their guesses at the combination and it affects only their session.