Category Archives: mysql

MySQL: Triggers

Simple Example

-- begin

DROP DATABASE IF EXISTS `test`;

CREATE DATABASE `test`;

USE `test`;

-- DROP TABLE IF EXISTS `test`;

CREATE TABLE `test` (
	`val` int
);

CREATE TABLE `test_log` (
	`val` int
);

DROP TRIGGER IF EXISTS `t1`;

DELIMITER //

CREATE TRIGGER `t1`
AFTER INSERT ON `test` FOR EACH ROW
BEGIN
	INSERT INTO `test_log` VALUES (NEW.val);
END //

DELIMITER ;

SET @x = 99;

SELECT * FROM `test`;
SELECT * FROM `test_log`;

INSERT INTO `test` VALUES (@x);

SELECT * FROM `test`;
SELECT * FROM `test_log`;

-- end

Another example

CREATE TABLE `test_updated` (
	`val` int,
	`val1` int
);

DELIMITER //

CREATE TRIGGER `t2`
BEFORE INSERT ON `test` FOR EACH ROW
BEGIN
	IF NEW.val <= 0 THEN
	INSERT INTO `test_updated` VALUES (NEW.val, -1);
	ELSE
	INSERT INTO `test_updated` VALUES (NEW.val, 1);
	END IF;
END //

DELIMITER ;

SELECT * from `test_updated`;

INSERT INTO `test` values(-9);
INSERT INTO `test` values(9);

SELECT * from `test_updated`;

References
Introduction to MySQL Triggers

MySQL: Stored Procedures


-- begin

DROP DATABASE IF EXISTS `test`;

CREATE DATABASE `test`;

USE `test`;

-- DROP TABLE IF EXISTS `test`;

CREATE TABLE `test` (
	`val` int
);

DROP PROCEDURE IF EXISTS `p1`;

DELIMITER //

CREATE PROCEDURE `p1` (IN v1 INT, OUT v3 INT)
BEGIN
	DECLARE v2 INT; -- declare it here, not inside else block
	IF v1 <= 0 THEN
	SELECT "no iterations";
	ELSE
		SET v2 = 0;
		WHILE v1 >= v2 DO
			INSERT INTO `test` values(v1);
			SET v1 = v1 - 1;
		END WHILE;
		SET v3 = V1;
	END IF;
END //

DELIMITER ;

SET @x = 99;
SET @y = @x;

SELECT COUNT(*) FROM `test`;

SELECT @x, @y;

CALL `p1`(@x, @y);

SELECT COUNT(*) FROM `test`;

SELECT @x, @y;

-- end

References
An Introduction to Stored Procedures in MySQL 5

MySQL : Creating a new User

Creates a new User called user1 at localhost and with the password pass1

CREATE USER 'user1'@'localhost' IDENTIFIED BY 'pass1';

Grants permission for all kind of operations on all databases.

GRANT ALL ON *.* TO 'user1'@'localhost';

Original Source @databasef1