0

I'm working on an application using JSP and MYSQL.

The application includes feature for user to input 'Date' and 'Hours', which are stored in a table 'epinfo' in MYSQL.

        +-------+-------------+------+-----+------------+-------+
        | Field | Type        | Null | Key | Default    | Extra |
        +-------+-------------+------+-----+------------+-------+
        | uid   | varchar(10) | NO   | PRI |            |       |
        | dt    | date        | NO   | PRI | 0000-00-00 |       |
        | hrs   | int(2)      | YES  |     | NULL       |       |
        +-------+-------------+------+-----+------------+-------+

Another feature is applying for leave, stored in a different table 'leave'

     +--------+-------------+------+-----+------------+-------+
     | Field  | Type        | Null | Key | Default    | Extra |
     +--------+-------------+------+-----+------------+-------+
     | uid    | varchar(10) | NO   | PRI |            |       |
     | stdt   | date        | NO   | PRI | 0000-00-00 |       |
     | enddt  | date        | NO   | PRI | 0000-00-00 |       |
     +--------+-------------+------+-----+------------+-------+

I need to disable and delete any entries for this time period, in the table epinfo.

Eg. If a leave application entry is 2014-06-14 to 2014-06-16, I need to make sure the user cannot enter any data for 14,15,16th of June in the table epinfo.

I know a BEFORE INSERT Trigger can help here, which goes something like this:

       create trigger cons_check
       before insert on epinfo
       for each row
       begin
       if exists(select * from leave where stdt<=entered_date and enddt>=entered_date)
       then
       Disable entry in database
       else
       insert into table.
       end if
       end

Can someone help me create this trigger? Or help with a different solution? Also, the entered date is the value I have in my insert query. How can I use this value in the trigger?

user3270763
  • 125
  • 3
  • 12
  • Write a before-update trigger for table epinfo which checks if the values are consistent with the leave table. – Alex Monthy Jun 10 '14 at 12:00
  • I need help writing the trigger. If you know how to write one which does the work mentioned above, I would really appreciate your help. – user3270763 Jun 12 '14 at 10:38
  • You're on your way. Look up "signal sqlstate" in the mysql manual which allows you to throw an error where you "Disablee entry in database". You don't need an ELSE branch then. Have a look at http://stackoverflow.com/questions/24/throw-an-error-in-a-mysql-trigger – Alex Monthy Jun 12 '14 at 11:11
  • I had read about Signal sqlstate and it is not supported by the version of SQL I'm currently using. Thanks though. – user3270763 Jun 12 '14 at 11:39
  • Look here for a workaround (see 3rd answer): http://stackoverflow.com/questions/24/throw-an-error-in-a-mysql-trigger – Alex Monthy Jun 13 '14 at 16:36

0 Answers0