Skip to content

Instantly share code, notes, and snippets.

@eskil
Created March 19, 2012 00:51
Show Gist options
  • Save eskil/2088075 to your computer and use it in GitHub Desktop.
Save eskil/2088075 to your computer and use it in GitHub Desktop.
"CREATE TRIGGER Dive_Update AFTER "
"UPDATE OF dive_datetime ON Dive BEGIN "
"UPDATE Dive "
"SET dive_number= "
" (SELECT CASE "
" WHEN dive_id = NEW.dive_id THEN "
" /* The updated dive, set the number to the number of older dives */ "
" 1 + (SELECT COUNT(*) FROM Dive WHERE dive_datetime < NEW.dive_datetime) "
" WHEN NEW.dive_datetime > OLD.dive_datetime "
" /* All other dive, move by +/- 1 depending on whether we've moved the datetime "
" forwards or backwards. "
" */ "
" AND dive_datetime <= NEW.dive_datetime "
" AND dive_datetime >= OLD.dive_datetime THEN "
" (SELECT CASE "
" WHEN dive_number-1 < OLD.dive_number THEN "
" dive_number "
" ELSE "
" dive_number-1 "
" END) "
" WHEN NEW.dive_datetime < OLD.dive_datetime "
" AND dive_datetime <= OLD.dive_datetime "
" AND dive_datetime >= NEW.dive_datetime THEN "
" (SELECT CASE "
" WHEN dive_number+1 > OLD.dive_number THEN "
" dive_number "
" ELSE "
" dive_number+1 "
" END) "
" ELSE "
" dive_number "
" END) "
"WHERE "
" /* Only for dives with datetime between old and new */ "
" (SELECT CASE "
" WHEN NEW.dive_datetime > OLD.dive_datetime THEN "
" dive_datetime >= OLD.dive_datetime "
" AND dive_datetime <= NEW.dive_datetime "
" WHEN NEW.dive_datetime < OLD.dive_datetime THEN "
" dive_datetime <= OLD.dive_datetime "
" AND dive_datetime >= NEW.dive_datetime "
" END); "
"END; "
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment