CODEWITHSUNDEEP
mysqlsqldatabase-design
user1032531
user1032531

Reputation: 26281

Variable SQL column Default Value

I would like to have a column's default value be a specified on a per session basis. The below script doesn't work, but describes how I would like to use it. I am currently using MySQL 5.5.28, but can upgrade if necessary.

CREATE TABLE my_tbl (
 id INT NOT NULL AUTO_INCREMENT,
 data VARCHAR(45),
 date_created  TIMESTAMP DEFAULT   CURRENT_TIMESTAMP, 
 date_modified TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 created_by    INT DEFAULT         CUSTOM_USER_ID, 
 modified_by   INT ON UPDATE       CUSTOM_USER_ID
 PRIMARY KEY (id)
) ENGINE = InnoDB;

-- Will set created_id and modified_id=111
SET @CUSTOM_USER_ID=111;
INSERT INTO my_tbl(data) VALUES('hello');

-- Will set modified_id=222
SET @CUSTOM_USER_ID=222;
UPDATE my_tbl SET data='goodby' WHERE id=1;

-- Will keep modified_id=222
SET @CUSTOM_USER_ID=333;
UPDATE my_tbl SET data='hello again',modified_id=modified_id WHERE id=1;

Upvotes: 1

Views: 2527

Answers (2)

Sebas
Sebas

Reputation: 21532

So I'm posting an alternative:

delimiter $$

SET @CUSTOM_USER_ID=111;

CREATE TRIGGER myTrigger_INS BEFORE INSERT ON my_tbl FOR EACH ROW
BEGIN
    IF NEW.created_by IS NULL OR NEW.created_by = '' THEN
        SET NEW.created_by = @CUSTOM_USER_ID;
    END IF;
END$$

CREATE TRIGGER myTrigger_UPD BEFORE UPDATE ON my_tbl FOR EACH ROW
BEGIN
    IF NEW.modified_by IS NULL OR NEW.modified_by = '' THEN
        SET NEW.modified_by = @CUSTOM_USER_ID;
    END IF;
END$$

delimiter ;

CREDITS

Upvotes: 3

Mike Brant
Mike Brant

Reputation: 71384

This is not possible. Read the following from MySQL documentation:

The DEFAULT value clause in a data type specification indicates a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column. See Section 11.3.5, “Automatic Initialization and Updating for TIMESTAMP”.

You can't use an expression like this to define a default value.

Upvotes: 1

Related Questions