[MySQL] Constraints, exceptions en triggers

[Alain]

Ik ben bezig met een cursus databases en in deze cursus wordt veel met interbase gewerkt. Ik heb gedurende deze cursus een database opgezet voor een voor mij vatbar probleem. Deze stel ik telkens bij als ik weer wat nieuws geleerd heb. Ik ben nu wat aan het experimenteren met MySQL.

Het probleem

Ik wil regels toevoegen aan de database.

Zoektocht

In mijn zoektocht ben ik o.a. de volgende pagina's tegen gekomen:

http://dev.mysql.com/ - Voornamelijk het hoofdstuk over triggers
[google=mysql exception]
[google=mysql trigger throw exception]

Testcase

De orderdatum moet gelijk zijn aan de systeemdatum.

SQL:

CREATE TABLE Orders (
  ordernr INT UNSIGNED NOT NULL,
  orderdatum DATE NOT NULL,
  CONSTRAINT pk_order
    PRIMARY KEY (ordernr),
  CONSTRAINT ch_orderdatum
    CHECK (orderdatum = CURRENT_DATE)
) ENGINE = INNODB;

De opdracht geeft geen foutmeldingen of warnings, maar de check constraint wordt genegeerd. Om het probleem te omzeilen wil ik dus een trigger die een exception genereerd. Volgende poging:

SQL:

CREATE EXCEPTION E_Date 'Ongeldige datum invoer';

Dit wordt niet door MySQL ondersteund. Ik kom nu bij de volgende (werkende) code, maar dit is niet wat ik wil. Ik wil een foutmelding.

SQL:

DELIMITER //

CREATE TRIGGER T_BI_Orders
BEFORE INSERT ON Orders
FOR EACH ROW
BEGIN
  IF NEW.orderdatum <> CURRENT_DATE THEN
    -- Exception
    SET NEW.orderdatum = CURRENT_DATE;
  END IF;
END;//

DELIMITER ;

INSERT INTO Orders VALUES (1, CURRENT_DATE); 
INSERT INTO Orders VALUES (2, CURRENT_DATE - 1); 

Ik kan niet in de documentatie van MySQL terug vinden hoe ik een exception kan genereren. Het moet wel kunnen getuige:

quote: http://dev.mysql.com/doc/refman/5.1/en/using-triggers.html

If a BEFORE trigger fails, the operation on the corresponding row is not performed.

Hoe zou ik dit op kunnen lossen?

[Verwijderd]

Now()?

Dat zet ik altijd default op een column als een gecreëerde row de huidige datum (en tijd) moet hebben. Hoeft je query die informatie ook niet te bevatten omdat de (my)SQL server dit automatisch genereerd.

[ Voor 54% gewijzigd door Verwijderd op 13-06-2008 12:07 . Reden: aanvullen uitleg ]

[Alain]

Wat ik wil bereiken is een regel opstellen, die in dit geval betrekking heeft op een datum. Ik wil dus een foutmelding genereren op het moment dat een regel overtreden wordt en niet dat er een standaard waarde ingevuld wordt.

[Alain]

Ik heb gelezen dat ik met Conditions en handlers sql fouten af kan vangen en hiermee een trigger of procedure kan laten falen. Ik kan alleen nog steeds nergens vinden hoe ik zelf een exception kan triggeren.

Ik begin te twijfelen of het uberhaupt mogelijk is in MySQL. Heeft iemand nog een slim idee?

[eek]

code:

RAISE EXCEPTION 'Brakke datum';

Werkt dat niet?

[Alain]

Dat werkt inderdaad niet. Ik kan ook niets over vinden in de mysql documentatie over RAISE of RAISE EXCEPTION, dus ik denk dat dit in MySQL niet wordt ondersteund.

Ik heb een website gevonden die een MySQL Stored Procedures Library aanbied voor onder andere dit probleem. Zo ver wil ik niet gaan, dus ik denk dat ik alle regels in de client applicatie zal moeten afhandelen als ik MySQL ga gebruiken.

[Varienaja]

AlainS schreef op donderdag 12 juni 2008 @ 22:35:
De orderdatum moet gelijk zijn aan de systeemdatum.

SQL:

CREATE TABLE Orders (
  ordernr INT UNSIGNED NOT NULL,
  orderdatum DATE NOT NULL,
  CONSTRAINT pk_order
    PRIMARY KEY (ordernr),
  CONSTRAINT ch_orderdatum
    CHECK (orderdatum = CURRENT_DATE)
) ENGINE = INNODB;

Dit vind ik een zeer onzinnige constraint. Constraints moeten altijd gelden, en jouw constraint geldt nog niet eens 1 nanoseconde. Zoals al eerder werd gesuggereerd is een insert-trigger de manier voor specifiek deze functionaliteit.

[Alain]

Varienaja schreef op maandag 16 juni 2008 @ 19:33:
[...]

Dit vind ik een zeer onzinnige constraint. Constraints moeten altijd gelden, en jouw constraint geldt nog niet eens 1 nanoseconde.

Je hebt gelijk en dit realiseerde ik me na het openen van het topic. Zoals vaak met een versimpelt voorbeeld om een probleem te illustreren, was dit een ongelukkige keuze.

Zoals al eerder werd gesuggereerd is een insert-trigger de manier voor specifiek deze functionaliteit.

Ook met triggers heb ik het probleem nog niet op kunnen lossen zonder ranzige oplossingen. Op internet lees je soms dat een fake error wordt gegenereerd (bijvoorbeeld het inserten in een niet bestaande tabel) om de actie af te breken, maar een degelijke native oplossing ben ik nog niet tegen gekomen.

[ Voor 14% gewijzigd door Alain op 16-06-2008 20:06 ]

[Alain]

Ik heb inmiddels Firebird geprobeerd en dit heeft toch mijn voorkeur. Nu kan ik ook domains maken en het is nog gratis ook.

Mijn testcase:

SQL:

SQL> CREATE TABLE Orders (
CON>   ordernr INT NOT NULL,
CON>   orderdatum DATE NOT NULL,
CON>   CONSTRAINT pk_order
CON>     PRIMARY KEY (ordernr)
CON> );
SQL>
SQL> CREATE EXCEPTION E_Date 'Ongeldige datum invoer';
SQL>
SQL> SET TERM #;
SQL>
SQL> CREATE TRIGGER T_BI_Orders
CON> FOR Orders BEFORE INSERT AS
CON> BEGIN
CON>   IF (NEW.orderdatum <> CURRENT_DATE) THEN
CON>     EXCEPTION E_Date;
CON> END#
SQL>
SQL> SET TERM ;#
SQL> INSERT INTO Orders VALUES (1, CURRENT_DATE);
SQL> INSERT INTO Orders VALUES (2, CURRENT_DATE - 1);
Statement failed, SQLCODE = -836
exception 1
-E_DATE
-Ongeldige datum invoer
-At trigger 'T_BI_ORDERS'
SQL> SELECT * FROM Orders;

     ORDERNR  ORDERDATUM
============ ===========
           1 2008-06-16

[SchizoDuckie]

Even een vraag om mijn inzicht te vormen, waarom kiesje ervoor om dit soort logica in je database op te nemen? Dit hoort toch in je data-processing (front-+backenend code) laag? ipv in je database zelf, wat als tussentijds de specs veranderen

[Alain]

SchizoDuckie schreef op dinsdag 17 juni 2008 @ 00:38:
Even een vraag om mijn inzicht te vormen, waarom kiesje ervoor om dit soort logica in je database op te nemen? Dit hoort toch in je data-processing (front-+backenend code) laag? ipv in je database zelf, wat als tussentijds de specs veranderen

Dat is iets wat bij de analyse van de informatiestroom wel boven tafel komt. Ik wil eerst aftasten waar mijn grenzen liggen. De gedachte erachter is:

• De database kan geen data opslaan die niet aan de bedrijfsregels voldoet
• De client applicatie zorgt ervoor dat de data gevalideerd wordt
• Mocht er een database error optreden, dan heb ik werk

De informatiestroom bepaald de spec en als de informatiestroom veranderd, pas dan veranderd de spec.

[SchizoDuckie]

Duidelijk It is niet altijd de utopie die wij programmeurs graag zien van vast gevormde datastromen

[Alain]

Wat ik uiteindelijk wil bereiken is een projectadministratie systeem, voor alle technische informatie. Op dit moment wordt er een netwerkschijf gebruikt met alle projecten. 80 projecten met component A levert dus ook 80 keer dezelfde manual van component A op, op dezelfde netwerkschijf.

Ik ben net begonnen aan een opleiding informatica aan de open universiteit. Mijn propedeuse opdracht is: Het analyseren van de informatiestroom binnen een bedrijf en het opstellen van een datamodel en client applicatie.

Hier heb ik vrijstelling voor, maar dit zou het bedrijf waarvoor ik werk goed kunnen gebruiken. Mijn uiteindelijke leerdoel is een goed model neer te zetten voor de projectadministratie binnen ons bedrijf, puur en alleen als oefening voor mezelf. Daarnaast ben ik de enige software engineer, dus ontwerpkeuzes worden door mij gemaakt.

Het kan goed zijn dat ik op deze keuze's terug kom, maar ik moet wel weten wat mogelijk is.