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.