Devart Forums

Code: Select all

-- Clean up before starting.
    DROP TABLE IF EXISTS `test_tbl`;
    DROP TRIGGER IF EXISTS `test_tbl_before_insert`;
     
    -- Create the table.
    CREATE TABLE `test_tbl` (
        `id` Int Auto_Increment Not Null Primary Key,
        `random_value` VarChar(255) Not Null Default 'value',
        `created` DateTime Not Null
    );
     
    -- Create a trigger to update the created value.
    DELIMITER //
    DROP TRIGGER IF EXISTS `test_tbl_before_insert`//
    CREATE TRIGGER `test_tbl_before_insert`
    BEFORE INSERT ON `test_tbl`
    FOR EACH ROW
    BEGIN
        -- Only set the default value if it's empty
        IF NEW.created = '0000-00-00 00:00:00' THEN
            SET NEW.created = NOW();
        END IF;
    END;//
    DELIMITER ;
     
    -- INSERT using the default value.
    INSERT INTO `test_tbl`(`random_value`)
    VALUES ('First'), ('Second'), ('Third');
     
    -- INSERT explicit values
    INSERT INTO `test_tbl`(`random_value`, `created`)
    VALUES ('Fourth', '2010-01-01'), ('Fifth', '2009-12-24');
     
    -- Select stuff
    SELECT * FROM `test_tbl`;