/* ============================================================
Schema di test: CLIENTI + ORDINI
Dialetto: Firebird 3.0+
Contiene: 2 tabelle, 1 FK, 3 trigger, 2 SP, 2 funzioni, 2 viste
============================================================ */
/* ─── TABELLA: CLIENTI (MASTER TABLE) ──────────────────────── */
CREATE TABLE CLIENTI ( -- MASTER TABLE
ID BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
CODICE VARCHAR(20) NOT NULL,
NOME VARCHAR(100) NOT NULL,
EMAIL VARCHAR(200),
ATTIVO BOOLEAN NOT NULL DEFAULT TRUE,
CREATO_IL TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
AGGIORNATO TIMESTAMP,
CONSTRAINT UQ_CLIENTI_CODICE UNIQUE (CODICE),
CONSTRAINT UQ_CLIENTI_EMAIL UNIQUE (EMAIL)
);
/* ─── TABELLA: ORDINI (TRANSACTION TABLE) ───────────────────── */
CREATE TABLE ORDINI ( -- TRANSACTION TABLE
ID BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
CLIENTE_ID BIGINT NOT NULL,
NUMERO_ORDINE VARCHAR(30) NOT NULL,
STATO VARCHAR(20) NOT NULL DEFAULT 'BOZZA'
CHECK (STATO IN ('BOZZA','CONFERMATO','SPEDITO','CONSEGNATO','ANNULLATO')),
IMPORTO_TOTALE DECIMAL(12,2) NOT NULL DEFAULT 0 CHECK (IMPORTO_TOTALE >= 0),
DATA_ORDINE DATE NOT NULL DEFAULT CURRENT_DATE,
DATA_CONSEGNA DATE,
NOTE BLOB SUB_TYPE TEXT,
CREATO_IL TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT FK_ORDINI_CLIENTE FOREIGN KEY (CLIENTE_ID) REFERENCES CLIENTI (ID),
CONSTRAINT UQ_ORDINI_NUMERO UNIQUE (NUMERO_ORDINE)
);
/* ─── TRIGGER: CLIENTI ──────────────────────────────────────── */
SET TERM ^ ;
CREATE OR ALTER TRIGGER TRG_CLIENTI_BI FOR CLIENTI
BEFORE INSERT ACTIVE
AS
BEGIN
IF (NEW.CODICE IS NULL OR NEW.CODICE = '') THEN
NEW.CODICE = 'CLI-' || LPAD(CAST(NEW.ID AS VARCHAR(6)), 6, '0');
NEW.CREATO_IL = CURRENT_TIMESTAMP;
END^
CREATE OR ALTER TRIGGER TRG_CLIENTI_BU FOR CLIENTI
BEFORE UPDATE ACTIVE
AS
BEGIN
NEW.AGGIORNATO = CURRENT_TIMESTAMP;
END^
/* ─── TRIGGER: ORDINI ───────────────────────────────────────── */
CREATE OR ALTER TRIGGER TRG_ORDINI_BI FOR ORDINI
BEFORE INSERT ACTIVE
AS
BEGIN
NEW.NUMERO_ORDINE = 'ORD-' || EXTRACT(YEAR FROM CURRENT_DATE)
|| '-' || LPAD(CAST(NEW.ID AS VARCHAR(5)), 5, '0');
NEW.CREATO_IL = CURRENT_TIMESTAMP;
END^
/* ─── STORED PROCEDURE: SP_CONFERMA_ORDINE ──────────────────── */
CREATE OR ALTER PROCEDURE SP_CONFERMA_ORDINE (
P_ORDINE_ID BIGINT
) RETURNS (
P_RISULTATO VARCHAR(20)
) AS
BEGIN
UPDATE ORDINI
SET STATO = 'CONFERMATO'
WHERE ID = :P_ORDINE_ID
AND STATO = 'BOZZA';
IF (ROW_COUNT = 0) THEN
EXCEPTION EX_ORDINE_NON_VALIDO;
:P_RISULTATO = 'OK';
END^
/* ─── STORED PROCEDURE: SP_ANNULLA_ORDINE ───────────────────── */
CREATE OR ALTER PROCEDURE SP_ANNULLA_ORDINE (
P_ORDINE_ID BIGINT
) AS
BEGIN
UPDATE ORDINI
SET STATO = 'ANNULLATO'
WHERE ID = :P_ORDINE_ID
AND STATO IN ('BOZZA', 'CONFERMATO');
IF (ROW_COUNT = 0) THEN
EXCEPTION EX_STATO_NON_VALIDO;
END^
/* ─── FUNCTION: FN_TOTALE_CLIENTE ───────────────────────────── */
CREATE OR ALTER FUNCTION FN_TOTALE_CLIENTE (
P_CLIENTE_ID BIGINT
) RETURNS DECIMAL(14,2) AS
DECLARE VARIABLE V_TOT DECIMAL(14,2);
BEGIN
SELECT COALESCE(SUM(O.IMPORTO_TOTALE), 0)
INTO :V_TOT
FROM ORDINI O
WHERE O.CLIENTE_ID = :P_CLIENTE_ID
AND O.STATO NOT IN ('BOZZA','ANNULLATO');
RETURN :V_TOT;
END^
/* ─── FUNCTION: FN_STATO_LABEL ──────────────────────────────── */
CREATE OR ALTER FUNCTION FN_STATO_LABEL (
P_STATO VARCHAR(20)
) RETURNS VARCHAR(50) AS
BEGIN
IF (:P_STATO = 'BOZZA') THEN RETURN 'In bozza';
IF (:P_STATO = 'CONFERMATO') THEN RETURN 'Confermato';
IF (:P_STATO = 'SPEDITO') THEN RETURN 'In spedizione';
IF (:P_STATO = 'CONSEGNATO') THEN RETURN 'Consegnato';
IF (:P_STATO = 'ANNULLATO') THEN RETURN 'Annullato';
RETURN 'Sconosciuto';
END^
SET TERM ; ^
/* ─── VISTE ─────────────────────────────────────────────────── */
CREATE OR ALTER VIEW V_ORDINI_CLIENTI AS
SELECT
O.ID AS ORDINE_ID,
O.NUMERO_ORDINE,
C.CODICE AS CLIENTE_CODICE,
C.NOME AS CLIENTE_NOME,
O.STATO,
O.IMPORTO_TOTALE,
O.DATA_ORDINE,
O.DATA_CONSEGNA
FROM ORDINI O
JOIN CLIENTI C ON C.ID = O.CLIENTE_ID;
CREATE OR ALTER VIEW V_CLIENTI_ATTIVI AS
SELECT
ID,
CODICE,
NOME,
EMAIL,
CREATO_IL
FROM CLIENTI
WHERE ATTIVO = TRUE;
