Created
August 14, 2010 23:15
-
-
Save mallain/524828 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* Destruction des tables si elles sont presentes */ | |
DROP TABLE IF EXISTS tfa_data; | |
DROP TABLE IF EXISTS tdi_temps; | |
DROP TABLE IF EXISTS tmp_data; | |
/* Creation des tables */ | |
CREATE TABLE tfa_data(ID INTEGER PRIMARY KEY AUTOINCREMENT, ADCO TEXT, OPTARIF TEXT, ISOUSC TEXT, HCHC TEXT, HCHP TEXT, PTEC TEXT, IINST TEXT, IMAX TEXT, PAPP TEXT, HHPHC TEXT, MOTDETAT TEXT, ID_TEMPS INTEGER); | |
CREATE TABLE tdi_temps(ID INTEGER PRIMARY KEY AUTOINCREMENT, ANNEE INTEGER, MOIS INTEGER, JOUR INTEGER, HEURE TEXT); | |
CREATE TABLE tmp_data(ADCO TEXT, OPTARIF TEXT, ISOUSC TEXT, HCHC TEXT, HCHP TEXT, PTEC TEXT, IINST TEXT, IMAX TEXT, PAPP TEXT, HHPHC TEXT, MOTDETAT TEXT); | |
/* Implementation d un declencheur sur la table tmp_data */ | |
CREATE TRIGGER insert_tfa_data AFTER INSERT ON tmp_data | |
BEGIN | |
/* Creation d un evenement sur l axe des temps */ | |
INSERT INTO tdi_temps ( | |
ANNEE, | |
MOIS, | |
JOUR, | |
HEURE | |
) | |
VALUES ( | |
strftime('%Y', 'now', 'localtime'), | |
strftime('%m', 'now', 'localtime'), | |
strftime('%d', 'now', 'localtime'), | |
strftime('%H:%M:%S', 'now', 'localtime') | |
); | |
/* Insertion des donnees */ | |
INSERT INTO tfa_data ( | |
ADCO, | |
OPTARIF, | |
ISOUSC, | |
HCHC, | |
HCHP, | |
PTEC, | |
IINST, | |
IMAX, | |
PAPP, | |
HHPHC, | |
MOTDETAT, | |
ID_TEMPS | |
) | |
VALUES ( | |
new.ADCO, | |
new.OPTARIF, | |
new.ISOUSC, | |
new.HCHC, | |
new.HCHP, | |
new.PTEC, | |
new.IINST, | |
new.IMAX, | |
new.PAPP, | |
new.HHPHC, | |
new.MOTDETAT, | |
last_insert_rowid() | |
); | |
/* Purge de la table tmp_data */ | |
DELETE FROM tmp_data; | |
END; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
INSERT INTO tmp_data VALUES('a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k'); | |
SELECT ADCO, OPTARIF, ISOUSC, HCHC | |
FROM tfa_data, tdi_temps | |
WHERE tfa_data.id_temps = tdi_temps.id; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment