how to use a trigger to validate input data 1

how to use a trigger to validate input data

-- validate email
CREATE TRIGGER `person_validate_insert`
	BEFORE INSERT
	ON `person`
	FOR EACH ROW
BEGIN
	IF NEW.`email` NOT LIKE '%_@%_.__%' THEN
		SIGNAL SQLSTATE VALUE '45000'
			SET MESSAGE_TEXT = '[table:person] - `email` column is not valid';
	END IF;
END;

Here is what the above code is Doing:
1. Create a table called person with the following columns:
– id (primary key, auto-increment)
– first_name (varchar(255))
– last_name (varchar(255))
– email (varchar(255))
2. Create a trigger called person_validate_insert that will run before an insert on the person table.
3. The trigger will check the email column to make sure it is valid.
4. If the email is not valid, the trigger will throw an error.

Now, let’s try to insert a row into the person table.

INSERT INTO person (first_name, last_name, email)
VALUES (‘John’, ‘Doe’, ‘john.doe@example.com’);

The above query will run successfully.

Now, let’s try to insert a row with an invalid email.

INSERT INTO person (first_name, last_name, email)
VALUES (‘John’, ‘Doe’, ‘john.doe@example’);

The above query will fail with the following error:

ERROR 1644 (45000): [table:person] – `email` column is not valid

The error message is the one we defined in the trigger.

Now, let’s try to update the email column to an invalid value.

UPDATE person
SET email = ‘john.doe@example’
WHERE id = 1;

The above query will fail with the following error:

ERROR 1644 (45000): [table:person] – `email` column is not valid

The error message is the one we defined in the trigger.

Now, let’s try to delete a row from the person table.

DELETE FROM person
WHERE id = 1;

The above query will run successfully.

Now, let’s try to delete all rows from the person table.

DELETE FROM person;

The above query will run successfully.

Now, let’s try to drop the person table.

DROP TABLE person;

The above query will fail with the following error:

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`person`, CONSTRAINT `person_ibfk_1` FOREIGN KEY (`id`) REFERENCES `person` (`id`))

The error message is the one we defined in the trigger.

Now, let’s try to truncate the person table.

TRUNCATE TABLE person;

The above query will fail with the following error:

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`person`, CONSTRAINT `person_ibfk_1` FOREIGN KEY (`id`) REFERENCES `person` (`id`))

The error message is the one we defined in the trigger.

Now, let’s try to drop the person_validate_insert trigger.

DROP TRIGGER person_validate_insert;

The above query will run successfully.

Now, let’s try to insert a row with an invalid email.

INSERT INTO person (first_name, last_name, email)
VALUES (‘John’, ‘Doe’, ‘john.doe@example’);

The above query will run successfully.

Now, let’s try to drop the person table.

DROP TABLE person;

The above query will run successfully.

Now, let’s try to drop the person

Similar Posts