Share
È probabile che tu sappia cos'è un trigger del database, almeno in termini concettuali. Le probabilità sono ancora maggiori che tu sappia che MySQL supporta i trigger e li ha supportati per parecchio tempo. Immagino, anche armato di questa conoscenza, che molti di voi non sfruttino i trigger con MySQL. Sono una di quelle cose che dovrebbero assolutamente essere nel tuo tool di sviluppo, in quanto possono davvero cambiare il modo in cui guardi i tuoi dati.
"Tuttavia, dal momento che le applicazioni diventano sempre più complicate, più siamo in grado di astrarre gli strati di un'applicazione per gestire ciò che dovrebbero, maggiore diventa l'usabilità dello sviluppo interno."
Per chi non lo sapesse, un trigger è una regola che tu metti su una tabella che sostanzialmente dice, ogni volta che ELIMINA, AGGIORNA o INSERI qualcosa in questa tabella, fai anche qualcos'altro. Ad esempio, potremmo voler registrare una modifica, ma invece di scrivere due query separate, una per la modifica e una per il registro, possiamo invece scrivere un trigger che dice "Ogni volta che questa riga viene aggiornata, crea una nuova riga in una tabella diversa per dirmi che l'aggiornamento è stato fatto ". Aggiunge un po 'di overhead alla query iniziale, ma poiché non ci sono due pacchetti che viaggiano verso il tuo database per fare due cose separate, c'è un guadagno complessivo di prestazioni (in teoria comunque).
I trigger sono stati introdotti in MySQL nella versione 5.0.2. La sintassi per un trigger è un po 'strana al primo risalto. MySQL utilizza lo standard ANSI SQL: 2003 per le procedure e altre funzioni. Se sei a tuo agio con un linguaggio di programmazione in generale, non è così difficile da capire. Le specifiche non sono disponibili liberamente, quindi farò del mio meglio per utilizzare strutture semplici e spiegare cosa sta accadendo all'interno del trigger. Avrai a che fare con le stesse strutture logiche fornite da qualsiasi linguaggio di programmazione.
Come accennato in precedenza, i trigger verranno eseguiti proceduralmente su eventi UPDATE, DELETE e INSERT. Ciò che non ho menzionato è che possono essere eseguiti prima o dopo l'evento definito. Pertanto, potresti avere un innesco che si attiva prima di un DELETE o dopo un DELETE, così via e così via. Ciò significa che potresti avere un trigger che si attiva prima di un INSERT e uno separato che viene attivato DOPO un INSERT, che può essere molto potente.
Vedrò tre usi che potresti considerare di aggiungere alla tua casella degli strumenti. Ci sono molti usi che non approfondirò, poiché ritengo che ci siano metodi migliori per ottenere gli stessi risultati o che meritino il loro tutorial. Ognuno di questi usi che sto esplorando ha una controparte nel tuo livello logico lato server e non sono nuovi concetti. Tuttavia, man mano che le applicazioni diventano sempre più complicate, più siamo in grado di astrarre gli strati di un'applicazione per gestire ciò che dovrebbero, maggiore è l'usabilità dello sviluppo interno che diventa.
Sto lavorando con un mitico sistema di carrelli, con articoli che hanno prezzi. Ho cercato di mantenere la struttura dei dati il più semplice possibile solo a scopo illustrativo. Sto nominando colonne e tabelle allo scopo di capire, e non per uso di produzione. Sto anche utilizzando TIMESTAMPS piuttosto che altre alternative per facilità. Per chi gioca la versione casalinga del gioco di oggi, sto usando i nomi delle tabelle di carrelli, cart_items, cart_log, items, items_cost.
Si prega di notare in questo tutorial che userò query molto semplici per esprimere i miei punti. Non leggo alcuna variabile, poiché non sto utilizzando alcun input da parte dell'utente. Voglio rendere le query più facili da leggere possibile, ma non usare questo tutorial per qualcosa di diverso dalle pratiche applicazioni di trigger. So che potrebbe esserci un commento o due su questo, quindi considera questo mio disclaimer.
Sto usando il Quick Profiler di Particle Tree PHP per vedere i tempi di esecuzione. Sto anche usando il livello di astrazione del database fornito nello strumento solo a mio vantaggio. È uno strumento piacevole e fa molto di più che fornire solo tempi di esecuzione SQL.
Sto anche usando Chive per illustrare gli effetti DB e creare i miei trigger. Chive è solo MySQL 5+ ed è molto simile a PHPMyAdmin. È più carino, ma anche molto bugger al momento. Sto usando Chive, semplicemente perché dà buone schermate su cosa sta succedendo con le query.
Un'altra breve nota. Potrebbe essere necessario modificare il delimitatore per MySQL durante la creazione di un trigger. Il delimitatore naturale per MySQL è; ma dal momento che utilizzeremo quel delimitatore per le nostre query aggiunte, potrebbe essere necessario rinominare esplicitamente il delimitatore se si creano questi tramite la riga di comando. Ho scelto di non mostrare questo perché, utilizzando Chive, non è necessario modificare il delimitatore.
Per modificare un delimitatore, devi semplicemente farlo prima del comando di attivazione:
DELIMITER $$
E questo dopo il tuo comando di innesco:
DELIMITER;
Se si esegue anche la minima normalizzazione nella struttura del database, è probabile che si verifichi un momento in cui è stata eliminata l'origine dati principale, ma i frammenti continuano a scorrere nel flusso di dati. Ad esempio, potresti avere un cart_id a cui si fa riferimento in due o tre tabelle senza chiavi esterne, in particolare perché le chiavi esterne non sono supportate dal motore MyISAM.
Quello che probabilmente hai fatto in passato è qualcosa di simile (semplificato per illustrazione):
$ sql = 'DELETE FROM no_trigger_cart_items WHERE cart_id = 1'; $ rs = $ this-> db-> query ($ sql); $ sql = 'DELETE FROM no_trigger_carts WHERE cart_id = 1'; $ rs = $ this-> db-> query ($ sql);
Ora, a seconda della tua organizzazione, potresti avere una singola API o un metodo per ripulire i tuoi carrelli. In questo caso, hai isolato la logica per eseguire queste due query. Se questo non è il caso, devi sempre ricordarti di cancellare gli articoli del carrello quando elimini un carrello specifico. Non difficile, ma quando dimentichi, stai perdendo la tua integrità dei dati.
Inserisci il nostro innesco. Creerò un trigger molto semplice in modo che ogni volta che elimino un carrello, il mio trigger venga attivato per eliminare tutti gli elementi del carrello che hanno lo stesso cart_id:
CREATE TRIGGER 'tutorial'. 'Before_delete_carts' PRIMA DELETE ON 'trigger_carts' PER OGNI RIGA INIZIA CANCELLA DA trigger_cart_items WHERE OLD.cart_id = cart_id; FINE
Sintassi molto semplice come ho detto sopra. Passiamo attraverso ogni riga.
La mia prima riga recita "tutorial CREATE TRIGGER". 'Before_delete_carts' ". Sto dicendo a MySQL di creare un trigger sul database "tutorial" per avere un nome di "before_delete_carts". Tendo a nominare i miei trigger con la formula "When_How_Table". Questo funziona per me, ma ci sono molti altri modi per farlo.
La mia seconda riga indica a MySQL la definizione di questo trigger, "PRIMA DELETE ON 'trigger_carts' FOR EACH ROW". Sto dicendo a MySQL che prima di eliminare su questo tavolo, ogni riga fa qualcosa. Quel qualcosa è spiegato in seguito, all'interno del nostro BEGIN e END. "DELETE FROM trigger_cart_items WHERE OLD.cart_id = cart_id;" Sto dicendo a MySQL prima di eliminare da trigger_carts, prendere OLD.cart_id e anche eliminare da trigger_cart_items. La sintassi OLD è la variabile definita. Ne discuteremo nella prossima sezione in cui combineremo OLD e NEW.
Non c'è davvero nulla per creare questo innesco. Il vantaggio è spostare la logica di integrità dei dati nel tuo livello dati, che potrei affermare, è la sua parte. C'è anche un altro lieve vantaggio e questo è il leggero guadagno di prestazioni, visto sotto.
Due query:
Una query con un trigger:
Come puoi vedere c'è un leggero aumento di prestazioni, che dovrebbe essere previsto. Il mio database che sto utilizzando è localhost con il mio server, ma se avessi utilizzato un server DB separato, il mio guadagno in termini di prestazioni sarebbe un po 'maggiore a causa del tempo di andata e ritorno tra i due server. La cancellazione del trigger ha un tempo leggermente più alto da eliminare, ma esiste una sola query, quindi il tempo complessivo diminuisce. Moltiplicare questo su tutto il codice che si utilizza per mantenere l'integrità dei dati, e il guadagno di prestazioni diventa almeno modesto.
Una nota sulla performance, la prima volta che viene eseguito il trigger, potrebbe essere molto più lenta delle volte successive. Non utilizzo i trigger necessariamente per il guadagno di prestazioni, ma piuttosto per spostare la logica dei dati sul mio livello dati, proprio come si desidera spostare la presentazione dal markup al livello di presentazione, altrimenti noto come CSS.
Il prossimo esempio che esamineremo riguarderà la registrazione. Diciamo che voglio tenere traccia di ogni articolo inserito in un carrello. Forse, voglio monitorare il mio tasso di acquisto degli articoli del carrello. Forse, voglio solo avere una copia di ogni articolo inserito in un carrello, non necessariamente venduto, solo per avere un'idea della mente dei miei clienti. Forse, hai creato gli elementi del tuo carrello come tabella MEMORY e vuoi registrare tutti gli elementi in una tabella InnoDB. Qualunque sia la ragione, diamo un'occhiata a un trigger INSERT, che aprirà alcune buone possibilità di registrazione o auditing dei nostri dati.
Prima dei trigger, probabilmente abbiamo fatto qualcosa di simile (di nuovo, semplificato per l'illustrazione):
Ora, possiamo creare un trigger molto semplice per questo processo di registrazione:
CREATE TRIGGER 'after_insert_cart_items' AFTER INSERT ON 'trigger_cart_items' PER OGNI RIGA INIZIA INSERIRE IN trigger_cart_log (cart_id, item_id) VALUES (NEW.cart_id, NEW.item_id); FINE
Ripassiamo questo, solo così c'è chiarezza su ciò che questo trigger sta facendo. Per prima cosa iniziamo con la riga, "CREATE TRIGGER 'after_insert_cart_items'". Sto ancora dicendo a MySQL di creare un trigger con il nome di "after_insert_cart_items". Il nome potrebbe essere "Foo", o "BullWinkle" o qualsiasi altra cosa si voglia chiamare, ma, di nuovo, preferisco illustrare i miei nomi di trigger. Quindi vediamo "AFTER INSERT ON" trigger_cart_items 'FOR EACH ROW ". Di nuovo, questo sta dicendo dopo che abbiamo inserito qualcosa su trigger_cart_items, per ogni riga inserita esegui ciò che è tra il mio BEGIN e END.
Infine, abbiamo appena "INSERT INTO trigger_cart_log (cart_id, item_id) VALUES (NEW.cart_id, NEW.item_id);" che è una query standard con l'eccezione dei miei due valori. Sto usando il NUOVO valore inserito nella tabella cart_items.
E abbiamo tagliato le nostre domande a metà con il sottile guadagno in termini di prestazioni:
E solo per verificare che il nostro trigger funzioni, vedo i valori nella mia tabella:
Anche questo è relativamente facile, ma stiamo lavorando con un paio di valori, che possono aggiungere un po 'di complessità. Diamo un'occhiata a qualcosa di un po 'più difficile.
A questo punto possiamo saltare la vecchia modalità di più query con una singola query. Immagino che sarà sempre un po 'noioso continuare a misurare le prestazioni delle query. Invece, passiamo ad alcuni esempi avanzati di trigger.
La logica aziendale è dove gli errori si insinuano sempre. Indipendentemente da quanto siamo attenti o organizzati, qualcosa scivola sempre attraverso le fessure. I trigger su UPDATE lo attenuano un po '. Abbiamo un po 'di potenza in un trigger per valutare quale fosse il valore OLD e impostare il NUOVO valore in base alla valutazione. Diciamo ad esempio che vogliamo avere sempre il nostro prezzo degli articoli come un margine del 30% sul costo degli articoli. È logico quindi che, quando AGGIORNA i nostri costi, abbiamo anche bisogno di AGGIORNARE il nostro prezzo. Gestiamola con un grilletto.
CREATE TRIGGER 'after_update_cost' DOPO AGGIORNAMENTO ON 'trigger_items_cost' PER OGNI RIGMA AGGIORNAMENTO AGGIORNAMENTO trigger_items SET prezzo = (NEW.cost * 1.3) WHERE item_id = NEW.item_id; FINE
Quello che stiamo facendo è aggiornare la tabella degli articoli con un prezzo basato sui tempi di NEW.cost 1.3. Ho inserito un costo di $ 50, quindi il mio nuovo prezzo dovrebbe essere di $ 65.
Abbastanza sicuro, questo grilletto ha funzionato bene.
Abbiamo bisogno di dare un'occhiata ad un esempio un po 'più avanzato. Abbiamo già la regola per modificare il prezzo di un articolo in base al suo costo. Diciamo che vogliamo modificare un po 'il nostro costo. Se il costo è inferiore a $ 50, il nostro costo è in realtà di $ 50. Se il costo è superiore a $ 50 ma inferiore a $ 100, il nostro costo diventa $ 100 dollari. Anche se il mio esempio probabilmente non corrisponde a una vera regola aziendale, adeguiamo i costi in base a fattori quotidiani. Sto solo cercando di mantenere l'esempio facile da capire.
Per fare questo, lavoreremo di nuovo con un UPDATE ma questa volta lo licenzeremo prima di eseguire la nostra query. Lavoreremo anche con una dichiarazione IF, che è a nostra disposizione.
Ecco il nuovo trigger:
CREA TRIGGER 'before_update_cost' PRIMA DI AGGIORNARE 'trigger_items_cost' PER OGNI FILA INIZIARE SE NUOVO.cost < 50 THEN SET NEW.cost = 50; ELSEIF NEW.cost > 50 E NOVITÀ < 100 THEN SET NEW.cost = 100; END IF; END
Quello che stiamo facendo ora non è chiamare una query, ma piuttosto semplicemente ignorare il valore. Sto dicendo che se il costo è inferiore a $ 50, allora fai $ 50. Se il costo è tra $ 50 e $ 100, quindi renderlo $ 100. Se è al di sopra di quello, allora lascio semplicemente che rimanga lo stesso. La mia sintassi qui non è quella straniera da qualsiasi altra lingua lato server. Abbiamo bisogno di chiudere la nostra clausola IF con un END IF; ma a parte questo, non è davvero difficile.
Solo per verificare se il nostro trigger funziona, ho inserito un valore di $ 30 per il costo e dovrebbe essere $ 50:
Quando inserisco un costo di $ 85, ecco il valore:
E, solo per verificare se il mio trigger AFTER UPDATE funziona ancora, il mio prezzo dovrebbe ora essere $ 130:
La vita è bella.
Ho solo toccato la punta dell'iceberg con trigger e MySQL. Mentre ci sono innumerevoli usi per i trigger, sono andato avanti bene in passato senza di loro gestendo i miei dati nel mio livello logico. Detto questo, la possibilità di aggiungere regole ai miei dati nel livello dati ha senso. Quando si aggiungono i modesti miglioramenti delle prestazioni, il vantaggio è ancora maggiore.
Ora dobbiamo occuparci di complicate applicazioni web ad alto traffico. L'uso di un trigger su un sito di vanity a pagina singola potrebbe non essere il miglior uso di tempo ed energia; un trigger su un'applicazione web complessa potrebbe fare il mondo della differenza. Spero che vi siano piaciuti gli esempi e, per favore, fatemi sapere cosa necessita di ulteriori spiegazioni.
Accentsconagua