TUniScript cannot execute this script. Looks like the issue is related with the trigger script execution.
Here is a test script (using an TUniconnection that has forcecreatedatabase option to true)
Code: Select all
CREATE TABLE articles (
Id_Article integer PRIMARY KEY AUTOINCREMENT,
Libelle_Article varchar(250),
Gencod_Article varchar(250),
Code_Article varchar(250),
Colisage integer,
Stock float(50)
);
CREATE INDEX articles_Index01
ON articles
(Gencod_Article);
CREATE INDEX articles_Index02
ON articles
(Code_Article);
CREATE INDEX articles_Index03
ON articles
(Stock);
CREATE UNIQUE INDEX articles_Index04
ON articles
(Gencod_Article, Code_Article);
CREATE TABLE articles_details (
Id_Article_detail integer PRIMARY KEY AUTOINCREMENT,
Gencod_Article varchar(250),
Code_Article varchar(250),
Gencod_Piece varchar(250),
Couleur varchar(250),
Taille varchar(250),
Nombre_Pieces integer
);
CREATE INDEX articles_details_Index01
ON articles_details
(Gencod_Article);
CREATE INDEX articles_details_Index02
ON articles_details
(Gencod_Piece);
CREATE INDEX articles_details_Index03
ON articles_details
(Code_Article);
CREATE INDEX articles_details_Index04
ON articles_details
(Code_Article, Gencod_Article);
CREATE UNIQUE INDEX articles_details_Index05
ON articles_details
(Gencod_Article, Code_Article, Gencod_Piece);
CREATE TABLE colis_entree_details (
Id_Colis_Entree_Detail integer PRIMARY KEY,
Code_Barre varchar(250) DEFAULT '',
Id_Emplacement_Colis varchar(20) DEFAULT '',
Utilisateur varchar(20) DEFAULT '',
Id_Device varchar(20) DEFAULT '',
Date_Entree datetime,
Version varchar(10) DEFAULT '',
Date_Validation datetime,
Date_Transfert datetime,
Id_Transfert varchar(20)
);
CREATE INDEX Idx_Code_Barre
ON colis_entree_details
(Code_Barre);
CREATE INDEX Idx_Date_Entree
ON colis_entree_details
(Date_Entree);
CREATE INDEX Idx_Date_Transfert
ON colis_entree_details
(Date_Transfert);
CREATE INDEX Idx_Date_Validation
ON colis_entree_details
(Date_Validation);
CREATE TABLE colis_inventaires (
Id_Colis_Inventaire integer PRIMARY KEY AUTOINCREMENT,
Id_Inventaire varchar(50),
Date_Inventaire datetime,
Utilisateur varchar(20),
Id_Entrepot varchar(50),
Date_Validation datetime,
Date_Transfert datetime,
Statut varchar(250)
);
CREATE INDEX colis_inventaires_Index01
ON colis_inventaires
(Id_Inventaire);
CREATE INDEX colis_inventaires_Index02
ON colis_inventaires
(Id_Inventaire, Date_Validation);
CREATE INDEX colis_inventaires_Index04
ON colis_inventaires
(Id_Inventaire, Date_Transfert);
CREATE TRIGGER tgrAferDelete_colis_inventaires
AFTER DELETE
ON colis_inventaires
BEGIN
DELETE FROM
[colis_inventaires_details]
WHERE
[Id_Inventaire] = [OLD].[Id_Inventaire];
END;
CREATE TABLE colis_inventaires_details (
Id_Colis_Inventaire_Detail integer PRIMARY KEY AUTOINCREMENT,
Id_Inventaire varchar(50),
Type varchar(10),
Travee varchar(250),
Gencod_Article varchar(250),
Code_Article varchar(250),
Utilisateur varchar(50),
Date_Inventaire datetime,
Code_Barre varchar(250)
);
CREATE INDEX colis_inventaires_details_Index01
ON colis_inventaires_details
(Id_Inventaire, Gencod_Article);
CREATE INDEX colis_inventaires_details_Index02
ON colis_inventaires_details
(Code_Barre, Id_Inventaire);
CREATE INDEX colis_inventaires_details_Index03
ON colis_inventaires_details
(Id_Inventaire);
CREATE INDEX colis_inventaires_details_Index06
ON colis_inventaires_details
(Type);
CREATE INDEX colis_inventaires_details_Index08
ON colis_inventaires_details
(Gencod_Article);
CREATE INDEX colis_inventaires_details_Index09
ON colis_inventaires_details
(Code_Article);
CREATE INDEX colis_inventaires_details_Index10
ON colis_inventaires_details
(Travee);
CREATE INDEX colis_inventaires_details_Index11
ON colis_inventaires_details
(Id_Inventaire, Gencod_Article, Code_Article, Type);
CREATE TRIGGER colis_inventaires_details_Trigger01
AFTER DELETE
ON colis_inventaires_details
BEGIN
DELETE FROM
[colis_inventaires_details_quantites]
WHERE
[Id_Inventaire] = [OLD].[Id_Inventaire] and
[Code_Barre] = [OLD].[Code_Barre];
END;
CREATE TABLE colis_inventaires_details_quantites (
Id_Colis_Inventaire_Detail_Quantite integer PRIMARY KEY AUTOINCREMENT,
Id_Inventaire varchar(50),
Gencod_Article varchar(250),
Code_Article varchar(250),
Gencod_Piece varchar(250),
Quantite_Pieces integer,
Code_Barre varchar(250)
);
CREATE INDEX colis_inventaires_details_quantites_Index01
ON colis_inventaires_details_quantites
(Id_Inventaire);
CREATE INDEX colis_inventaires_details_quantites_Index02
ON colis_inventaires_details_quantites
(Gencod_Article, Code_Article, Gencod_Piece);
CREATE INDEX colis_inventaires_details_quantites_Index03
ON colis_inventaires_details_quantites
(Id_Inventaire, Code_Barre, Gencod_Article, Code_Article);
CREATE TABLE colis_preparations (
Id_Colis_Preparation integer PRIMARY KEY,
Id_Bon_Preparation varchar(50),
Date_Preparation datetime,
Utilisateur varchar(20),
Id_Entrepot varchar(50),
Id_Commande varchar(50),
Id_Client varchar(50),
Total_Colis float,
Total_Poids float,
Total_Volume float,
Date_Validation datetime,
Date_Transfert datetime,
Statut varchar(250)
);
CREATE INDEX colis_preparations_Index01
ON colis_preparations
(Id_Bon_Preparation);
CREATE INDEX colis_preparations_Index02
ON colis_preparations
(Id_Bon_Preparation, Date_Validation);
CREATE INDEX colis_preparations_Index03
ON colis_preparations
(Id_Bon_Preparation, Date_Transfert);
CREATE INDEX colis_preparations_Index04
ON colis_preparations
(Id_Bon_Preparation, Date_Preparation);
CREATE TRIGGER tgrAferDelete
AFTER DELETE
ON colis_preparations
BEGIN
DELETE FROM
[Colis_Preparations_details]
WHERE
[Id_Bon_Preparation] = [OLD].[Id_Bon_Preparation];
DELETE FROM
[Colis_Preparations_items_scannes]
WHERE
[Id_Bon_Preparation] = [OLD].[Id_Bon_Preparation];
DELETE FROM
[Colis_Preparations_cartons_ouverts]
WHERE
[Id_Bon_Preparation] = [OLD].[Id_Bon_Preparation];
END;
CREATE TABLE colis_preparations_cartons_ouverts (
Id_Colis_Preparation_Carton_Ouvert integer PRIMARY KEY AUTOINCREMENT,
Id_Colis_Preparation_Item_Scanne integer,
Id_Bon_Preparation varchar(50),
Gencod_Article varchar(250),
Gencod_Piece varchar(250),
Code_Barre varchar(250),
Colisage integer,
Prepare integer,
Original boolean,
Utilisateur varchar(50)
);
CREATE INDEX colis_preparations_cartons_ouverts_Index01
ON colis_preparations_cartons_ouverts
(Id_Bon_Preparation, Gencod_Article, Gencod_Piece, Code_Barre);
CREATE INDEX colis_preparations_cartons_ouverts_Index02
ON colis_preparations_cartons_ouverts
(Id_Bon_Preparation);
CREATE INDEX colis_preparations_cartons_ouverts_Index03
ON colis_preparations_cartons_ouverts
(Gencod_Article);
CREATE INDEX colis_preparations_cartons_ouverts_Index04
ON colis_preparations_cartons_ouverts
(Code_Barre);
CREATE INDEX colis_preparations_cartons_ouverts_Index05
ON colis_preparations_cartons_ouverts
(Gencod_Piece);
CREATE INDEX colis_preparations_cartons_ouverts_Index06
ON colis_preparations_cartons_ouverts
(Original);
CREATE INDEX colis_preparations_cartons_ouverts_Index07
ON colis_preparations_cartons_ouverts
(Prepare);
CREATE INDEX colis_preparations_cartons_ouverts_Index08
ON colis_preparations_cartons_ouverts
(Id_Bon_Preparation, Original);
CREATE TABLE colis_preparations_details (
Id_Colis_Preparation_Detail integer PRIMARY KEY AUTOINCREMENT,
Id_Bon_Preparation varchar(50),
Id_Preparation_Sequence integer,
Id_Preparation_Detail_Piece integer,
Type varchar(10),
Travee varchar(250),
Gencod_Article varchar(250),
Code_Article varchar(250),
Libelle_Article varchar(250),
Libelle_Piece varchar(250),
Gencod_Piece varchar(250),
Nombre_Cartons integer,
Nombre_Pieces integer,
Colisage integer,
Total_Pieces integer
);
CREATE INDEX colis_preparations_details_Index01
ON colis_preparations_details
(Id_Bon_Preparation, Gencod_Article);
CREATE INDEX colis_preparations_details_Index02
ON colis_preparations_details
(Id_Bon_Preparation, Gencod_Piece);
CREATE INDEX colis_preparations_details_Index03
ON colis_preparations_details
(Id_Bon_Preparation);
CREATE INDEX colis_preparations_details_Index04
ON colis_preparations_details
(Nombre_Cartons);
CREATE INDEX colis_preparations_details_Index05
ON colis_preparations_details
(Nombre_Pieces);
CREATE INDEX colis_preparations_details_Index06
ON colis_preparations_details
(Type);
CREATE INDEX colis_preparations_details_Index07
ON colis_preparations_details
(Gencod_Piece);
CREATE INDEX colis_preparations_details_Index08
ON colis_preparations_details
(Gencod_Article);
CREATE INDEX colis_preparations_details_Index09
ON colis_preparations_details
(Code_Article);
CREATE INDEX colis_preparations_details_Index10
ON colis_preparations_details
(Travee);
CREATE INDEX colis_preparations_details_Index11
ON colis_preparations_details
(Id_Bon_Preparation, Gencod_Article, Code_Article, Type);
CREATE TABLE colis_preparations_items_scannes (
Id_Colis_Preparation_Item_Scanne integer PRIMARY KEY AUTOINCREMENT,
Id_Bon_Preparation varchar(50),
Type varchar(10),
Gencod_Article varchar(250),
Code_Article varchar(250),
Gencod_Piece varchar(250),
Code_Barre varchar(250),
Quantite_Pieces integer,
Utilisateur varchar,
Date_Preparation datetime,
Original boolean
);
CREATE INDEX colis_preparations_cartons_scannes_Index01
ON colis_preparations_items_scannes
(Id_Bon_Preparation, Gencod_Article);
CREATE INDEX colis_preparations_cartons_scannes_Index02
ON colis_preparations_items_scannes
(Id_Bon_Preparation);
CREATE INDEX colis_preparations_cartons_scannes_Index03
ON colis_preparations_items_scannes
(Id_Bon_Preparation, Quantite_Pieces);
CREATE INDEX colis_preparations_cartons_scannes_Index04
ON colis_preparations_items_scannes
(Quantite_Pieces);
CREATE INDEX colis_preparations_cartons_scannes_Index05
ON colis_preparations_items_scannes
(Type);
CREATE INDEX colis_preparations_cartons_scannes_Index06
ON colis_preparations_items_scannes
(Id_Bon_Preparation, Type, Gencod_Article, Code_Article);
CREATE INDEX colis_preparations_cartons_scannes_Index07
ON colis_preparations_items_scannes
(Id_Bon_Preparation, Type, Gencod_Article, Gencod_Piece);
CREATE INDEX colis_preparations_cartons_scannes_Index08
ON colis_preparations_items_scannes
(Gencod_Article);
CREATE INDEX colis_preparations_cartons_scannes_Index09
ON colis_preparations_items_scannes
(Gencod_Piece);
CREATE INDEX colis_preparations_cartons_scannes_Index10
ON colis_preparations_items_scannes
(Code_Barre);
CREATE INDEX colis_preparations_items_scannes_Index01
ON colis_preparations_items_scannes
(Id_Bon_Preparation, Type, Gencod_Article, Gencod_Piece, Code_Barre);
CREATE INDEX colis_preparations_items_scannes_Index02
ON colis_preparations_items_scannes
(Original);
CREATE INDEX colis_preparations_items_scannes_Index03
ON colis_preparations_items_scannes
(Code_Article);
CREATE TRIGGER colis_preparations_items_scannes_Trigger01
AFTER DELETE
ON colis_preparations_items_scannes
BEGIN
DELETE FROM
[Colis_Preparations_cartons_ouverts]
WHERE
[Id_Colis_Preparation_Item_Scanne] = [OLD].[Id_Colis_Preparation_Item_Scanne];
END;
CREATE TABLE preferences (
Id_Preference varchar(50),
Value varchar(250),
/* Keys */
CONSTRAINT sqlite_autoindex_preferences_1
PRIMARY KEY (Id_Preference)
);
CREATE TABLE utilisateurs (
Identifiant varchar(250),
Nom_Complet varchar(250),
Mot_De_Passe varchar(250)
);
CREATE INDEX utilisateurs_Index01
ON utilisateurs
(Identifiant);
CREATE VIEW colis_preparations_cartons_ouverts_ws
AS
SELECT
Id_Bon_Preparation,
Gencod_Article,
Gencod_Piece,
Code_Barre,
Colisage,
SUM(Prepare) AS Prepare
FROM
colis_preparations_cartons_ouverts
GROUP BY
Id_Bon_Preparation,
Gencod_Article,
Gencod_Piece,
Code_Barre;
CREATE VIEW colis_preparations_cartons_total
AS
SELECT
Id_Bon_Preparation,
type,
Gencod_Article,
Code_article,
COUNT(Gencod_Article) AS Total_Cartons
FROM
colis_preparations_items_scannes
WHERE
Type in ('C','R')
GROUP BY
Id_Bon_Preparation,
type,
Gencod_Article,
code_article;
CREATE VIEW colis_preparations_items_scannes_ws
AS
SELECT
Id_Bon_Preparation,
type,
Gencod_Article,
Code_Article,
Gencod_Piece,
Code_Barre,
Sum(Quantite_Pieces) as Quantite_Pieces,
Date_Preparation,
utilisateur
FROM
colis_preparations_items_scannes
GROUP BY
Id_Bon_Preparation,
type,
Gencod_Article,
Code_Article,
Gencod_Piece,
Code_Barre;
CREATE VIEW colis_preparations_pieces_total
AS
SELECT
Id_Bon_Preparation,
Type,
Gencod_Article,
Gencod_Piece,
SUM(Quantite_Pieces) AS Total_Pieces
FROM
colis_preparations_items_scannes
WHERE
Type = 'P'
GROUP BY
Id_Bon_Preparation,
Type,
Gencod_Article,
Gencod_Piece;
CREATE VIEW colis_preparations_details_restants
AS
SELECT
colis_preparations_details.*,
(colis_preparations_details.Nombre_Cartons - IfNull(colis_preparations_cartons_total.Total_Cartons,0)) as Nombre_Cartons_Restants,
(colis_preparations_details.Nombre_Pieces - IfNull(colis_preparations_pieces_total.Total_Pieces,0)) as Nombre_Pieces_Restantes,
colis_preparations_details.Total_Pieces - ((IfNull(colis_preparations_cartons_total.Total_Cartons,0)* colis_preparations_details.Colisage) + IfNull(colis_preparations_pieces_total.Total_Pieces,0)) as Total_Pieces_Restantes
FROM
colis_preparations_details
LEFT OUTER JOIN colis_preparations_cartons_total ON (colis_preparations_details.Id_Bon_Preparation = colis_preparations_cartons_total.Id_Bon_Preparation
and colis_preparations_details.Gencod_Article = colis_preparations_cartons_total.Gencod_Article
and colis_preparations_details.Code_Article = colis_preparations_cartons_total.Code_Article
and colis_preparations_details.Type = colis_preparations_cartons_total.Type )
LEFT OUTER JOIN colis_preparations_pieces_total ON (colis_preparations_details.Id_Bon_Preparation = colis_preparations_pieces_total.Id_Bon_Preparation
and colis_preparations_details.Gencod_Article = colis_preparations_pieces_total.Gencod_Article
and colis_preparations_details.Gencod_Piece = colis_preparations_pieces_total.Gencod_Piece
and colis_preparations_details.Type = colis_preparations_pieces_total.Type
)
WHERE not((colis_preparations_details.Nombre_Cartons = 0) and (colis_preparations_details.Nombre_Pieces = 0))
ORDER BY
colis_preparations_details.ID_Colis_Preparation_Detail;