2

Can you help me with this problem? I have two tables in a MySQL database:

ServiceProvider(SPID, Name, ... etc.)
hasTag(SPID, TagID)

Each service provider is supposed to have at least one tag, and a maximum of five tags. The max-constraint is not a problem, but the min-constraint refuses to work properly. I first tried to implement this via assertions, but then I found out, that MySQL does not support assertions. Thus, I wrote the following trigger:

delimiter |

CREATE  TRIGGER MinTags BEFORE INSERT
        ON ServiceProvider
        FOR EACH ROW BEGIN
            IF EXISTS (SELECT SPID FROM ServiceProvider
                       WHERE NOT EXISTS (SELECT DISTINCT SPID FROM hasTag))
            THEN INSERT INTO stop_action VALUES(1, 'Assert Failure');
            END IF;
        END;
|

delimiter ;

The insert of 'Assert Failure' into the stop_action table is only to create a constraint violation, so that the DB would abort the action.

Now, normally, when I insert any value into the ServiceProvider table, without inserting anything in to the hasTag table, I should get an error, right? But, somehow it doesn't work ... I can insert anything I want into the ServiceProvider table, without receiving any kind of error. Do you know, what is wrong with my code?

Valentino Ru
  • 4,964
  • 12
  • 43
  • 78
kafman
  • 2,862
  • 1
  • 29
  • 51
  • @eggyal I insert as follows: `insert into ServiceProvider(Name, Address, etc. ...) values ("Johaniter Café-Bar", "St. Johanns-Vorstadt 43", etc.);` And yes, SPID is an `AUTO_INCREMENT` column. – kafman Nov 11 '12 at 23:04
  • @eggyal Yes, that makes sense. But I don't understand why you mention that ... I am not inserting into `hasTag` nor do I the problem that I _cannot_ insert, but that I _can_ insert when sould not be able to. – kafman Nov 11 '12 at 23:11
  • @eggyal Ok, I see your point. But, I'd be happy if the insert would fail, as you explained it, but the fact is, that it doesn't. That's what confuses me. – kafman Nov 11 '12 at 23:18
  • Sorry, I misread: thought you were saying that all inserts failed. Are there any records in `ServiceProvider`, or is the table currently empty? – eggyal Nov 11 '12 at 23:22
  • @eggyal No worries. There is one record in the table (a test record, which was supposed to create an error). The `hasTag` table is empty, though. – kafman Nov 11 '12 at 23:27
  • I would suspect that further attempts to insert now raise an error? This is because the outer `EXISTS (SELECT SPID FROM ServiceProvider ...)` was false when `ServiceProvider` is empty. – eggyal Nov 11 '12 at 23:33
  • @eggyal Yes, indeed. So, should I change the trigger point from `before` to `after` (and adding a rollback)? – kafman Nov 11 '12 at 23:37
  • No... this trigger approach is nonsensical given that the new `SPID` can never exist in `hasTag` when a record is inserted into `ServiceProvider`. You need to find a different solution. – eggyal Nov 11 '12 at 23:39
  • Independently of if a trigger can or can not work in this case (see comments from @eggyal), I think the following question "But I'm still wondering, why my trigger does not work ... It's more a generic question, so that I know how to build triggers in the future." (+1 for that btw) is still not resolved. Please see my answer about the NEW.SPID syntax. Regards. – Marc Alff Nov 12 '12 at 09:29
  • @MarcAlff: But the reason why the OP could "*insert anything I want into the `ServiceProvider` table, without receiving any kind of error*" was not lack of `NEW.` qualifier - as [mentioned above](http://stackoverflow.com/questions/13336263/mysql-trigger-replacing-assertion-does-not-work#comment18198243_13336263), it was because no records existed in `ServiceProvider` and so the `EXISTS` test was false. Using a `NEW.` qualifier achieves nothing in this case, because one still can't test for existence of a referencing record in `hasTag` before one exists. – eggyal Nov 12 '12 at 09:40
  • @eggval, yes, I understand that, that's why I wrote "Independently of ...". Even if proper rows are present, not using NEW. is not going to work either. There are many things that prevented the trigger to work, and we both pointed to some of them ;) – Marc Alff Nov 12 '12 at 09:46

2 Answers2

1

How about denormalising a tad:

ALTER TABLE ServiceProvider
  ADD COLUMN TagID1 BIGINT UNSIGNED NOT NULL,
  ADD COLUMN TagID2 BIGINT UNSIGNED NULL,
  ADD COLUMN TagID3 BIGINT UNSIGNED NULL,
  ADD COLUMN TagID4 BIGINT UNSIGNED NULL,
  ADD COLUMN TagID5 BIGINT UNSIGNED NULL;

Include foreign key constraints, if appropriate.

eggyal
  • 122,705
  • 18
  • 212
  • 237
  • Yes, that would certainly work in this case, thank you. But I'm still wondering, why my trigger does not work ... It's more a generic question, so that I know how to build triggers in the future. – kafman Nov 11 '12 at 23:14
1

As written, this trigger does not even use the values from the row to be inserted.

The syntax to get the value of the SPID column is:

NEW.SPID

Also, consider using the SIGNAL statement to raise an error.

If you want to use ASSERT in SQL, this post may help:

SQL Scripts - Does the equivalent of a #define exist?

Community
  • 1
  • 1
Marc Alff
  • 8,227
  • 33
  • 59