CREATE TABLE IF NOT EXISTS `te` (
`id` int(30) NOT NULL,
`name` text NOT NULL,
`address` text NOT NULL,
`Aboutus` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Here Table 'te' consist 4 fields id, name, address, Aboutus, Aboutus is optional means how can i update default text to the Profile in db , by phpmyadmin sql
A DEFAULT value clause in a data type specification explicitly indicates a default value for a column. Examples: CREATE TABLE t1 ( i INT DEFAULT -1, c VARCHAR(10) DEFAULT '', price DOUBLE(16,2) DEFAULT 0.00 ); SERIAL DEFAULT VALUE is a special case.
The MySQL 'TEXT' type cannot have a DEFAULT value (other than NULL) (http://dev.mysql.com/doc/refman/5.1/en/data-type-defaults.html).
We have introduced a basic syntax code succeeding to illustrate the TEXT data type in MySQL. We can use TEXT while creating a table using the following query structure: CREATE TABLE TableName (ID INT PRIMARY KEY AUTO_INCREMENT, Title VARCHAR (255) NOT NULL, Description TEXT[forms]NOT NULL);
The exception is that, for TIMESTAMP and DATETIME columns, you can specify CURRENT_TIMESTAMP as the default. See Section 11.2. 6, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”. The BLOB and TEXT data types cannot be assigned a default value.
I have changed not null to null for about us field
CREATE TABLE IF NOT EXISTS `te` (
`id` int(30) NOT NULL,
`name` text NOT NULL,
`address` text NOT NULL,
`Aboutus` text NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Here is your trigger BEFORE INSERT
CREATE TRIGGER new_insert
BEFORE INSERT ON `te`
FOR EACH ROW
SET NEW.`Aboutus` = CASE WHEN NEW.Aboutus IS NULL THEN 'Not Updated' ELSE NEW.Aboutus END
;
Insert without Aboutus
INSERT INTO `te` (`id`, `name`, `address`)
VALUES (1, 'name', 'address') ;
Insert with Aboutus
INSERT INTO `te` (`id`, `name`, `address`, `Aboutus`)
VALUES (2, 'name', 'address', 'Aboutus') ;
Insert by passing null Aboutus
INSERT INTO `te` (`id`, `name`, `address`, `Aboutus`)
VALUES (3, 'name', 'address', null) ;
Edit As @garethD pointed a case for update scenario,you also need another trigger on BEFORE UPDATE
so if null appears in update then aboutus should be updated as Not Updated
CREATE TRIGGER update_trigger
BEFORE UPDATE ON `te`
FOR EACH ROW
SET NEW.`Aboutus` = CASE WHEN NEW.Aboutus IS NULL THEN 'Not Updated' ELSE NEW.Aboutus END
;
UPDATE te
SET AboutUs = NULL;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With