A little fun with global temporary tables

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.