Corso MariaDB / Usare procedure, funzioni memorizzate e trigger

Una procedura memorizzata si richiama esplicitamente con CALL, una funzione memorizzata restituisce un valore in un'espressione SQL e un trigger si attiva automaticamente per una modifica di tabella. Tutti conservano logica SQL sul server, ma differiscono per invocazione, privilegi e responsabilità transazionali.

Scegliere l'oggetto secondo il compito

Oggetto Invocazione Compito tipico
Procedura memorizzata CALL nome(argomenti) Operazione esplicita con più istruzioni SQL
Funzione memorizzata nome(argomenti) in un'espressione SQL Regola riutilizzabile che restituisce un valore
Trigger Automatico su INSERT, UPDATE o DELETE Breve azione per riga legata alla modifica della tabella

Una procedura non deve necessariamente modificare dati e una funzione memorizzata non è limitata all'aritmetica. L'esempio assegna compiti concreti: la procedura modifica le scorte, la funzione calcola un importo e il trigger registra vecchia e nuova scorta. In MariaDB i trigger lavorano per riga: una modifica di molte righe genera lavoro per ciascuna.

Confrontare il controllo di COMMIT e ROLLBACK da parte della connessione chiamante con il contesto dei privilegi delle viste. Il comportamento automatico sul server può complicare la diagnosi. Distinguere operazioni richiamate intenzionalmente dall'applicazione e logica associata a ogni scrittura, scegliendo quali regole controllare nell'input e quali applicare a più percorsi di scrittura.

Preparare privilegi e due tabelle InnoDB

Usare appops_lab con un account di studio dotato di CREATE ROUTINE, TRIGGER e privilegi di tabella necessari. L'invocazione richiede anche EXECUTE. La guida ad account e concessioni aiuta a limitare i privilegi senza rendere amministratore globale l'applicazione. Il binary log, registro delle modifiche per recupero o replica, può aggiungere requisiti alla creazione di funzioni. Far verificare le regole all'amministratore anziché allentare la fiducia globale del server per l'esercizio.

Controllare server, database e oggetti esistenti, conservando gli altri esercizi in appops_lab. Accertare che nomi delle due tabelle, funzione, procedura e trigger non siano già usati. La preparazione non elimina oggetti preesistenti.

CREATE DATABASE IF NOT EXISTS appops_lab
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE appops_lab;
CREATE TABLE routine_stock (
  product_id INT NOT NULL PRIMARY KEY,
  stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB;
CREATE TABLE routine_audit (
  audit_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL,
  old_stock INT NOT NULL,
  new_stock INT NOT NULL
) ENGINE=InnoDB;
INSERT INTO routine_stock VALUES (1, 8);

Entrambe le tabelle usano InnoDB, così modifica delle scorte e inserimento del trigger possono essere annullati insieme. Se un oggetto esiste, esaminarne definizione e dati prima di ripetere la creazione.

Creare le definizioni complete dei tre oggetti

Lo script usa il client mariadb da riga di comando. DELIMITER stabilisce come il client riconosce la fine dell'istruzione e non è SQL inviato al server. Negli editor grafici usare l'esecuzione di script. Con un driver Python inviare ciascun CREATE come istruzione completa senza righe DELIMITER e senza terminatore //.

DELIMITER //
CREATE FUNCTION line_total(p_price DECIMAL(10,2), p_qty INT)
RETURNS DECIMAL(12,2)
DETERMINISTIC NO SQL SQL SECURITY INVOKER
BEGIN
  IF p_price IS NULL OR p_qty IS NULL OR p_price < 0 OR p_qty < 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Nonnegative price and quantity required';
  END IF;
  RETURN ROUND(p_price * p_qty, 2);
END//

CREATE PROCEDURE adjust_stock(IN p_product_id INT, IN p_delta INT)
SQL SECURITY INVOKER
MODIFIES SQL DATA
BEGIN
  IF p_delta IS NULL OR p_delta = 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Nonzero stock delta required';
  END IF;
  UPDATE routine_stock SET stock = stock + p_delta
  WHERE product_id = p_product_id AND stock + p_delta >= 0;
  IF ROW_COUNT() <> 1 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Missing product or insufficient stock';
  END IF;
END//

CREATE TRIGGER audit_stock_update
AFTER UPDATE ON routine_stock FOR EACH ROW
BEGIN
  IF OLD.stock <> NEW.stock THEN
    INSERT INTO routine_audit (product_id, old_stock, new_stock)
    VALUES (NEW.product_id, OLD.stock, NEW.stock);
  END IF;
END//
DELIMITER ;

line_total rifiuta prezzo o quantità negativi o NULL e restituisce due decimali. La quantità zero è ammessa. Valutare l'intervallo DECIMAL se prezzi o quantità possono essere grandi. DETERMINISTIC NO SQL descrive questa implementazione e deve rimanere veritiero se cambia. Il riferimento di CREATE FUNCTION descrive caratteristiche e sicurezza.

adjust_stock aggiorna un prodotto esistente soltanto se la scorta finale non è negativa. Se la condizione non è soddisfatta, SIGNAL produce un errore del dominio. Leggere ROW_COUNT subito dopo UPDATE: altre istruzioni possono sostituire l'operazione misurata. Il riferimento di CREATE PROCEDURE descrive parametri e contesto di invocazione.

Il trigger inserisce una registrazione soltanto se la scorta cambia davvero. OLD e NEW sono i valori prima e dopo la modifica. Questa tabella documenta la prova e non costituisce un audit indipendente resistente alle manomissioni. Il riferimento di CREATE TRIGGER spiega valori delle righe e momento AFTER UPDATE.

Confrontare entrambe le tabelle con rollback e commit

SELECT line_total(12.50, 3) AS amount;
START TRANSACTION;
CALL adjust_stock(1, -3);
SELECT product_id, stock FROM routine_stock;
SELECT product_id, old_stock, new_stock FROM routine_audit;
ROLLBACK;
SELECT stock FROM routine_stock WHERE product_id = 1;
SELECT COUNT(*) AS audit_rows FROM routine_audit;

La funzione restituisce 37.50. Nella transazione la scorta passa da otto a cinque e la registrazione mostra 8 → 5. Dopo ROLLBACK la scorta torna otto e le registrazioni sono zero. Un ID AUTO_INCREMENT non deve necessariamente essere riutilizzato dopo il rollback: un salto nella numerazione non dimostra un annullamento fallito.

START TRANSACTION;
CALL adjust_stock(1, -2);
COMMIT;
SELECT stock FROM routine_stock WHERE product_id = 1;
SELECT old_stock, new_stock FROM routine_audit ORDER BY audit_id;

Dopo COMMIT la scorta è sei e una registrazione mostra 8 → 6. La procedura omette intenzionalmente un COMMIT interno per permettere alla connessione chiamante di includerla in altro lavoro. Il trigger partecipa alla transazione dell'istruzione che lo attiva e non conferma separatamente.

Osservare input errati e limiti dei privilegi

Eseguire separatamente le due istruzioni seguenti. La prima tenta di togliere più delle sei unità disponibili, la seconda usa un prezzo negativo. Entrambe devono produrre SQLSTATE 45000. Verificare poi scorta ancora sei e una sola registrazione già confermata.

CALL adjust_stock(1, -99);
SELECT line_total(-1, 2);

Funzione e procedura usano SQL SECURITY INVOKER: l'accesso alle tabelle si verifica con i privilegi del chiamante. EXECUTE senza UPDATE non permette di modificare le scorte tramite questa procedura. Il trigger usa il contesto DEFINER dell'account che lo definisce e non viene dichiarato INVOKER. Gestire quell'account, il suo ciclo di vita e i privilegi necessari.

I limiti ufficiali dei trigger restringono azioni come il controllo delle transazioni e la modifica della tabella che li attiva. Mantenere il lavoro breve e collegato alla transazione del database, evitando di nascondere lunghe operazioni esterne dietro una modifica di riga.

Esaminare le definizioni e rimuovere soltanto gli oggetti di prova

SHOW CREATE PROCEDURE adjust_stock;
SHOW CREATE FUNCTION line_total;
SHOW TRIGGERS FROM appops_lab;

Conservare definizioni e contesti dei privilegi e accertare che backup e ripristino includano routine e trigger richiesti. Una definizione ripristinata altrove può fallire se manca l'account DEFINER o non possiede i privilegi adeguati. Alla fine dell'esercizio separato eliminare soltanto i suoi oggetti effettivamente eliminabili:

DROP TRIGGER appops_lab.audit_stock_update;
DROP PROCEDURE appops_lab.adjust_stock;
DROP FUNCTION appops_lab.line_total;
DROP TABLE appops_lab.routine_audit;
DROP TABLE appops_lab.routine_stock;

Un normale ROLLBACK non recupera oggetti rimossi. Verificare i nomi e conservare le altre tabelle in appops_lab. Modificare routine in produzione richiede anche coordinamento con i chiamanti e procedure di distribuzione e recupero.