SQL trigger to check for time interval

Discussion in 'Mixed Languages' started by Stannieman, Feb 18, 2014.

  1. Stannieman

    Stannieman MDL Guru

    Sep 4, 2009
    2,232
    1,795
    90
    #1 Stannieman, Feb 18, 2014
    Last edited by a moderator: Apr 20, 2017
    Hi,

    I'm developing an application to manage/register users. Users can check in in our venue, but only once every 12 hours to prevent abuse.

    I have a table that looks like this:
    Code:
    CREATE TABLE check_ins (
    user BIGINT UNSIGNED,
    time DATETIME NOT NULL DEFAULT NOW(),
    CONSTRAINT check_ins_user_check_intime_uq UNIQUE (user, time),
    CONSTRAINT check_ins_user_fk FOREIGN KEY (user) REFERENCES users(user_id) ON DELETE CASCADE
    );
    To do the 12h check I'm currently getting the current time from the server, and the time of the user's last checkin, then I compare it and insert it if valid.

    Now I'd like to make it a little "cleaner" and just insert the user_id and NOW() into the table.
    Then there be should trigger to check right before the insert if the time is allowed.
    Something like NOW() - INTERVAL 12 HOURS > (SELECT MAX(time) FROM check_ins WHERE user = userToInsert)

    Then if the check fails the sql execution throws an exception which I catch in my code.

    Is this possible?

    Thanks,
    Stan
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...