0

I have a table 'weekrit' that has a primary key, that consists of a 'startdatum' (date value that is the first day of the week) and a 'ritid' (a number starting with 1 every week) I use a BEFORE INSERT trigger In the webside database to autoincrement the 'ritid' value. On the website it works fine.

I made an export of the database and imported that in my localhost database. In this developer-environment the trigger does not seem to work: every insert in this table fails:

Errorno: 1364 Errormessage: "Field 'ritid' doesn't have a default value"

This is the INSERT statement:

INSERT INTO weekrit(startdatum, chauffeur, datum, tijdstip, combiheen, klantnaam, ritcode, van, bestemming, tijdretour, combiretour, ritprijs, betaalwijze, fooi, betaalwijzefooi, eigenrolstoel, opmerkingen, aangemaakt_door, telefoon_contact, contactnaam, tijdstempel) VALUES ("2025-06-09", "GR", "2025-06-09", "10:00:00", "N", "Jan Test", "", "Van Adres", "Bestemmingsadres", "11:00:00", "N", 28.00, "", "", "", "N", "", "", "", "", "")

This is the trigger:

CREATE TRIGGER `trgIncrementWeekrit` BEFORE INSERT ON `weekrit` FOR EACH ROW SET NEW.ritid = (
             SELECT IFNULL(MAX(ritid), 0) + 1
             FROM weekrit
             WHERE startdatum  = NEW.startdatum
        )

I tried to do an export of the 'weekrit' table (including the trigger) from the website database alone and I imported that again locally, but that made no difference. Again everything works fine on the website, but I cannot get it working in the developer environment. Any suggestions?

Improve this question
6
  • 3
    Query SELECT VERSION(); in the website database and the development database. Are they the same? Commented Jun 11 at 16:03
  • 2
    Are you aware that your trigger, even if it works, creates a race condition? If two sessions insert at the same time, both may calculate the same startdatum value for two different rows. Commented Jun 11 at 16:04
  • This is not necessarily an issue with the trigger, but with how the two mysql instances are configured. For example, mysql can be configured to provide a default value when a column is not provided any value in an insert statement and there is no default value defined for that particular column. It seems that this configuration is on in your prod environment, but is off in your dev environment. Commented Jun 11 at 17:04
  • Did you verify the trigger exists in your development environment? Try show triggers like 'weekrit'; Commented Jun 11 at 19:12
  • What behavior should be for insert first row for "startdatum". (SELECT ... WHERE startdatum = NEW.startdatum) returns null ? Try SET NEW.ritid =coalesce((SELECT ... WHERE startdatum = NEW.startdatum),1). Commented Jun 11 at 19:35
Related questions
433
Get the new record primary key ID from MySQL insert query?
17
MySQL trigger before Insert value Checking
196
automatically insert auto increment primary key and values into existing table
Load 6 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.