Databasutlösare

En databasutlösare är procedurkod som exekveras automatiskt som svar på vissa händelser i en viss tabell eller vy i en databas . Triggern används mest för att upprätthålla integriteten för informationen i databasen. Till exempel, när en ny post (som representerar en ny arbetare) läggs till i tabellen för anställda, ska nya poster också skapas i tabellerna för skatter, semester och löner. Triggers kan också användas för att logga historisk data, till exempel för att hålla reda på anställdas tidigare löner.

Triggers i DBMS

Nedan följer en serie beskrivningar av hur vissa populära DBMS- stöd utlöser.

Orakel

Förutom utlösare som aktiveras (och kör PL/SQL- kod) när data ändras, stöder Oracle 10g utlösare som aktiveras när objekt på schemanivå (det vill säga tabeller) ändras och när användarinloggning eller utloggning inträffar.

Utlösare på schemanivå

  • Efter skapelsen
  • Före Alter
  • Efter Alter
  • Innan Drop
  • Efter Drop
  • Före insättning

De fyra huvudtyperna av triggers är:

  1. Utlösare på radnivå: Detta exekveras före eller efter något kolumnvärde i en rad ändras
  2. Utlösare på kolumnnivå: Detta exekveras före eller efter de angivna kolumnändringarna
  3. För varje radtyp: Denna utlösare exekveras en gång för varje rad i resultatuppsättningen som påverkas av en infogning/uppdatering/borttagning
  4. För varje satstyp: Denna utlösare exekveras endast en gång för hela resultatuppsättningen, men aktiveras också varje gång satsen exekveras.

Utlösare på systemnivå

Från Oracle 8i kan databashändelser - inloggningar, utloggningar, startups - utlösa Oracle-utlösare.

Microsoft SQL Server

En lista över alla tillgängliga starthändelser i Microsoft SQL Server för DDL-utlösare finns på Microsoft Docs .

Att utföra villkorliga åtgärder i utlösare (eller testa data efter modifiering) görs genom att komma åt de tillfälliga tabellerna Infogade och Borttagna .

PostgreSQL

Introducerade stöd för triggers 1997. Följande funktionalitet i SQL:2003 var tidigare inte implementerad i PostgreSQL:

  • SQL tillåter triggers att aktiveras vid uppdateringar av specifika kolumner; Från och med version 9.0 av PostgreSQL är denna funktion även implementerad i PostgreSQL.
  • Standarden tillåter exekvering av ett antal andra SQL-satser än SELECT , INSERT , UPDATE , såsom CREATE TABLE som utlöst åtgärd. Detta kan göras genom att skapa en lagrad procedur eller funktion för att anropa CREATE TABLE.

Synopsis:

              
                
          CREATE  TRIGGER  name  {  BEFORE  |  EFTER  }  {  händelse  [  ELLER  ...  ]  }  PÅ  BORDET  [  FÖR  [  VARJE  ]  {  RAD  |  STATEMENT  }  ]  UTFÖR  PROCEDUR  funcname  (  argument  )

eldfågel

Firebird stöder flera utlösare på radnivå, FÖRE eller EFTER, INFOGA, UPPDATERA, DELETE (eller någon kombination av dessa) per tabell, där de alltid är "utöver" standardtabelländringarna, och ordningen på utlösare i förhållande till varje tabell. andra kan specificeras där det annars skulle vara tvetydigt (POSITION-sats.) Utlösare kan också finnas på vyer, där de alltid är "istället för" utlösare, och ersätter den förinställda uppdateringsbara vylogiken. (Före version 2.1 kördes utlösare för vyer som anses uppdateringsbara utöver standardlogiken.)

Firebird tar inte upp muterande tabellundantag (som Oracle), och utlösare kommer som standard att både kapsla och återkomma efter behov (SQL Server tillåter kapsling men inte rekursion, som standard.) Firebirds utlösare använder NYA och GAMLA kontextvariabler (inte infogade och borttagna tabeller ,) och tillhandahålla UPPDATERING, INFOGA och DELETE flaggor för att indikera den aktuella användningen av utlösaren.

              
   
   
                  
   

 
 {  SKAPA  |  ÅTERSKAPA  |  CREATE  OR  ALTER  }  TRIGGERnamn  FÖR  {  tabellnamn  |  _  _  visa  namn  }  [  AKTIV  |  INAKTIV  ]  {  FÖRE  |  EFTER  }  {  INFOGA  [  ELLER  UPPDATERA  ]  [  ELLER  DELETE  ]  |  UPPDATERA  [  ELLER  INFOGA  ]  [  ELLER  DELETE  ]  |  DELETE  [  ELLER  UPPDATERA  ]  [  ELLER  INFOGA  ]  }  [  POSITION  n  ]  SOM  BÖRJAN  ....  SLUTA

Från och med version 2.1 stöder Firebird dessutom följande utlösare på databasnivå:

  • CONNECT (undantag som tas upp här förhindrar att anslutningen slutförs)
  • KOPPLA IFRÅN
  • TRANSAKTION START
  • TRANSAKTIONSÅTAGANDE (undantag som tas upp här förhindrar transaktionen från att förbinda sig, eller förbereda om en tvåfasig åtagande är inblandad)
  • TRANSAKTION ÅTERKÄLLNING

Utlösare på databasnivå kan hjälpa till att upprätthålla begränsningar för flera tabeller eller emulera materialiserade vyer . Om ett undantag görs i en TRANSACTION COMMIT-utlösare, återställs ändringarna som gjorts av utlösaren hittills och klientapplikationen meddelas, men transaktionen förblir aktiv som om COMMIT aldrig hade begärts; klientapplikationen kan fortsätta att göra ändringar och begära COMMIT på nytt.

Syntax för databasutlösare:

        
    
            
   

 
 {  SKAPA  |  ÅTERSKAPA  |  CREATE  OR  ALTER  }  TRIGGERnamn  [  AKTIV  |  _  INAKTIV  ]  PÅ  {  CONNECT  |  KOPPLA FRÅN  |  TRANSAKTION  START  |  TRANSAKTIONSÅTAGANDE  |  _  TRANSAKTION  ROLLBACK  }  [  POSITION  n  ]  SOM  BÖRJAN  .....  SLUT

MySQL/MariaDB

Begränsat stöd för triggers i MySQL/MariaDB DBMS lades till i 5.0-versionen av MySQL, som lanserades 2005.

Från och med version 8.0 tillåter de DDL-utlösare (Data Definition Language) och för DML-utlösare (Data Manipulation Language). De tillåter också att båda typerna av DDL-trigger (EFTER eller BEFORE) används för att definiera triggers. De skapas med hjälp av satsen CREATE TRIGGER och raderas med hjälp av satsen DROP TRIGGER . Satsen som kallas på en händelse inträffar definieras efter satsen FOR EACH ROW , följt av ett nyckelord ( SET eller BEGIN ), som indikerar om det som följer är ett uttryck respektive en sats.

IBM DB2 LUW

IBM DB2 för distribuerade system som kallas DB2 för LUW (LUW betyder L inux, Unix , W indows) stöder tre triggertyper: Före trigger, Efter trigger och Istället för trigger. Både utlösare på satsnivå och radnivå stöds. Om det finns fler utlösare för samma operation på bordet bestäms avfyringsordningen av utlösarskapande data. Sedan version 9.7 stöder IBM DB2 autonoma transaktioner.

Före trigger är för att kontrollera data och bestämma om drift ska tillåtas. Om undantag kastas från före triggern avbryts operationen och inga data ändras. I DB2 är före utlösare skrivskyddade — du kan inte ändra data före utlösare. Efter att triggers är designade för efterbearbetning efter att begärd ändring har utförts. Efter triggers kan skriva data i tabeller och till skillnad från vissa [ som? ] andra databaser kan du skriva in i vilken tabell som helst inklusive tabell som utlösaren verkar på. Istället för triggers är för att göra visningar skrivbara.

Triggers är vanligtvis programmerade i SQL PL- språk.

SQLite 

          
               
   
          

   
 SKAPA  [  TEMP  |  TILLÄMPLIGT  ]  TRIGGER  [  OM  INTE  FINNS  ]  [  databasnamn  .]  triggernamn  [  FÖRE  |  EFTER  |  I STÄLLET  FÖR  ]  {  DELETE  |  INFOGA  |  UPPDATERA  [  AV  kolumnnamn  [,  kolumnnamn  ]...]  }  PÅ  {  tabellnamn  |  view_name  }  [  FÖR  VARJE  RAD  ]  [  NÄR  villkoret  är  obligatoriskt  ]  BÖRJA  ...  SLUTA

SQLite stöder bara utlösare på radnivå, inte utlösare på uttalandenivå.

Uppdaterbara vyer, som inte stöds i SQLite, kan emuleras med INSTEAD OF triggers.

XML-databaser

Ett exempel på implementering av triggers i en icke-relationell databas kan vara Sedna , som ger stöd för triggers baserade på XQuery . Utlösare i Sedna designades för att vara analoga med SQL:2003- utlösare, men baseras naturligt på XML-frågor och uppdateringsspråk ( Xpath , XQuery och XML-uppdateringsspråk).

En trigger i Sedna ställs in på valfri noder i ett XML-dokument som lagras i databasen. När dessa noder uppdateras exekverar utlösaren automatiskt XQuery-frågor och uppdateringar som anges i dess body. Till exempel avbryter följande utlösare borttagning av personnod om det finns några öppna auktioner som denna person refererar till:

  
     
     
      
    
    
         
          
         
     SKAPA  TRIGGER  "trigger3"  INNAN  DELETE  PÅ  doc(  "auction"  )/  site  //  person  FÖR  VARJE  NOD  GÖR  {  if  (  existerar  (  $  WHERE  //  open_auction  /  bidder  /  personref  /  @person  =  $  OLD  /  @id )   )  then  (  )  else  $  OLD  ;  }

Utlösare på rad- och uttalandenivå

För att förstå hur triggerbeteende fungerar måste du vara medveten om de två huvudtyperna av triggers; dessa är utlösare på rad- och påståendenivå. Skillnaden mellan de två är hur många gånger koden inom triggern exekveras och vid vilken tidpunkt.

Anta att du har en trigger som är gjord för att anropas vid en UPPDATERING till ett visst bord. Utlösare på radnivå skulle köras en gång för varje rad som påverkas av UPPDATERING. Det är viktigt att tänka på om inga rader påverkas av kommandot UPDATE, kommer triggern inte att exekvera någon kod inom triggern. Utlösare på uttalandenivå kommer att anropas en gång oavsett hur många rader som påverkas av UPPDATERING. Här är det viktigt att notera att även om UPDATE-kommandot inte påverkade några rader, kommer koden i triggern fortfarande att köras en gång.

Använd alternativen FÖRE och EFTER bestäm när triggern anropas. Anta att du har en trigger som anropas på en INSERT till en viss tabell. Om din utlösare använder alternativet FÖRE, kommer koden i utlösaren att exekveras innan INSERT i tabellen sker. En vanlig användning av BEFORE-utlösaren är att verifiera ingångsvärdena för INSERT, eller modifiera värdena därefter. Låt oss nu säga att vi har en trigger som använder AFTER istället. Koden inom triggern exekveras efter att INSERT sker med tabellen. Ett exempel på användning av denna utlösare är att skapa en granskningshistorik över vem som har gjort inlägg i databasen, och hålla reda på de ändringar som gjorts. När du använder dessa alternativ måste du ha några saker i åtanke. Alternativet FÖRE tillåter dig inte att ändra tabeller, det är därför indatavalidering är en praktisk användning. Genom att använda AFTER-utlösare kan du modifiera tabeller som att infoga i en revisionshistoriktabell.

När du skapar en utlösare för att avgöra om det är sats- eller radnivå, inkludera helt enkelt FOR EACH ROW-satsen för en radnivå, eller utelämna satsen för en satsnivå. Var försiktig med att använda ytterligare INSERT / UPDATE / DELETE -kommandon inom din trigger, eftersom triggerrekursion är möjlig, vilket orsakar oönskat beteende. I exemplen nedan modifierar varje trigger en annan tabell, genom att titta på vad som ändras kan du se några vanliga tillämpningar av när olika triggertyper används.

Följande är ett Oracle-syntaxexempel på en radnivåutlösare som kallas EFTER en uppdatering FÖR VARJE RAD som påverkas. Denna utlösare anropas vid en uppdatering av en telefonboksdatabas. När triggern anropas lägger den till en post i en separat tabell med namnet phone_book_audit. Notera också att triggers kan dra fördel av schemaobjekt som sekvenser, i det här exemplet används audit_id_sequence.nexVal för att generera unika primärnycklar i phone_book_audit-tabellen. 

    
        

     
          
    
         
 SKAPA  ELLER  ERSÄTT  UTLÖSARE  phone_book_audit  EFTER  UPPDATERING  PÅ  telefonbok  FÖR  VARJE  RAD  BÖRJA  INFOGA  I  phone_book_audit  (  revisions  -  id  ,  revision_ändring  ,  revision_l_namn  ,  revision_f_namn  ,  revision_gammalt_telefonnummer  ,  revision_nytt_telefonnummer  ,  revision_nytt_telefonnummer  ,  granskningsställe  _datum  ,  _VÄRDE  revisions_datum  )  GAMLA  .  efternamn  ,  :  GAMLA  .  förnamn  _  ,  :  OLD  .  phone_number  ,  :  NEW  .  phone_number  ,  SYSDATE  );  SLUT  ;

Ringer nu en UPPDATERING på telefonbokstabellen för personer med efternamnet "Jones". 

          UPPDATERA  phone_book  SET  phone_number  =  '111-111-1111'  WHERE  last_name  =  'Jones'  ;
Audit_ID Audit_Change F_Name L_Namn New_Phone_Number Old_Phone_Number Revisionsdatum
1 Uppdatering Jordanien Jones 111-111-1111 098-765-4321 02-MAJ-14
2 Uppdatering Megan Jones 111-111-1111 111-222-3456 02-MAJ-14


Observera att tabellen phone_number_audit nu är fylld med två poster. Detta beror på att databasen har två poster med efternamnet "Jones". Eftersom uppdateringen ändrade två separata radvärden anropades den skapade triggern två gånger; en gång efter varje ändring.

Efter - utlösare på statement-nivå

En Oracle-syntaxsatsutlösare som anropas efter en UPPDATERING till telefonbokstabellen. När triggern anropas gör den en infogning i tabellen phone_book_edit_history

    
     

     
        
    
      
 SKAPA  ELLER  ERSÄTT  TRIGGER  phone_book_history  EFTER  UPPDATERING  PÅ  phone_book  BÖRJA  INFOGA  I  phone_book_edit_history  (  audit_history_id  ,  användarnamn  ,  modifiering  ,  edit_date  )  VÄRDEN  (  audit_history_id_sequence  .  nextVal  ,  USER  ,  '  SYSDATE '   )  ;  SLUT  ;

Gör nu exakt samma uppdatering som exemplet ovan, men den här gången med en utlösare på satsnivå.

          UPPDATERA  phone_book  SET  phone_number  =  '111-111-1111'  WHERE  last_name  =  'Jones'  ;
Audit_History_ID Användarnamn Modifiering Redigera datum
1 HAUSCHBC Uppdatering 02-MAJ-14

Resultatet visar att triggern bara anropades en gång, även om uppdateringen ändrade två rader.

Före varje - utlösare på radnivå

Det här exemplet visar en BEFORE EACH ROW-utlösare som modifierar INSERT med hjälp av en WHEN-villkorlig. Om efternamnet är större än 10 bokstäver, med hjälp av SUBSTR-funktionen ändrar vi kolumnvärdet last_name till en förkortning.

    
        
     

      0
 SKAPA  ELLER  ERSÄTT  TRIGGER  phone_book_insert  INNAN  INSERT  ON  phone_book  FÖR  VARJE  RAD  NÄR  (  LENGTH  (  new  .  last_name  )  >  10  )  BÖRJAR  :  ny  .  efternamn  :  =  SUBSTR  (:  nytt  .  efternamn  ,  ,  1  );  SLUT  ;

Utför nu en INSERT av någon med ett stort namn.

   
          INSERT  INTO  phone_book  VALUES  (  6  ,  'VeryVeryLongLastName'  ,  'Erin'  ,  'Minneapolis'  ,  'MN'  ,  '989 University Drive'  ,  '123-222-4456'  ,  55408  ,  TO_DATE  (  '11/21'  ,  ' 1991  MM/DD/ÅÅÅÅ' )   );
Person_ID Efternamn Förnamn Stad State_Abbreviation Adress Telefonnummer Postnummer DOB
6 V Erin Minneapolis MN 989 University Drive 123-222-4456 55408 21 NOV-91

Utlösaren fungerade enligt resultatet ovan och ändrade värdet på INSERT innan den kördes.

Före - utlösare på uttalandenivå

Att använda en BEFORE-satsutlösare är särskilt användbart när du upprätthåller databasbegränsningar. Det här exemplet visar hur man upprätthåller en begränsning för någon som heter "SOMEUSER" i tabellens telefonbok.

     
     

     
           
           
 SKAPA  ELLER  BYT  UTTRIGGER  HAUSCHBC  INNAN  SÄTT  I PÅ  SOMEUSER  .  phone_book  BEGIN  RAISE_APPLICATION_ERROR  (  num  =>  -  20050  ,  msg  =>  'Felmeddelandet går här.'  );  SLUT  ;

Nu, när "SOMEUSER" är inloggad efter att ha försökt INSERT kommer detta felmeddelande att visa:

SQL-fel: ORA-20050: Felmeddelandet visas här.

Anpassade fel som detta har en begränsning för vad num-variabeln kan definieras som. På grund av de många andra fördefinierade felen måste denna variabel vara i intervallet -20000 till -20999.

externa länkar

Hämtad från " https://sv.wikipedia.org/w/index.php?title=Database_trigger&oldid=1137404396 "