mysql create trigger and procedure demo

DROP TABLE IF EXISTS prAddVote;
DELIMITER

$$CREATE /*[DEFINER = { user | CURRENT_USER }]*/ PROCEDURE `item`.`prAddVote`(voteTitle VARCHAR(255),voteSum INT,Image VARCHAR(255),endTime DATETIME,models BIT(1)) /*LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT 'string'*/ BEGIN SET @voteTitle = voteTitle; SET @voteSum = voteSum; SET @Image = Image; SET @endTime = endTime; SET @models = models; SET @insertSql = CONCAT('insert into voteMaster(voteTitle,voteSum,Image,endTime,models)values(?,?,?,?,?)'); PREPARE stmtinsert FROM @insertSql; EXECUTE stmtinsert USING @voteTitle,@voteSum,@Image,@endTime,@models; DEALLOCATE PREPARE stmtinsert; END$$ DELIMITER ;

DROP TRIGGER IF EXISTS updateVoteMasterVotSum;
DELIMITER

$$CREATE TRIGGER updateVoteMasterVotSum AFTER UPDATE ON voteDetails FOR EACH ROW BEGIN IF new.voteNum=old.voteNum+1 THEN UPDATE voteMaster SET voteSum=voteSum+1 WHERE voteMaster.voteMasterId = old.voteMasterId; END IF; END$$ ;

DROP TABLE IF EXISTS DateLookup;
CREATE TABLE DateLookup
(
DateKey INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
DateFull DATETIME,
CharacterDate VARCHAR(10),
FullYear CHAR(4),
QuarterNumber TINYINT,
WeekNumber TINYINT,
WeekDayName VARCHAR(10),
MonthDay TINYINT,
MONTHNAME VARCHAR(12),
YearDay SMALLINT,
DateDefinition VARCHAR(30),
WEEKDAY TINYINT,
MonthNumber TINYINT
);

SET @Date = DATE_FORMAT(‘2019-12-01’,’%Y-%c-%d %h:%i:%s’);
DROP PROCEDURE IF EXISTS update_datelookup;
DELIMITER ##
CREATE PROCEDURE update_datelookup()
BEGIN
loop1: WHILE @Date < ‘2020-01-01 00:00:00’ DO
INSERT INTO DateLookup
(
DateKey, DateFull, FullYear,
QuarterNumber, WeekNumber, WeekDayName,
MonthDay, MONTHNAME, YearDay,
DateDefinition, CharacterDate,
WEEKDAY,MonthNumber
)
SELECT
UNIX_TIMESTAMP(@Date), @Date, YEAR(@Date),
QUARTER(@Date), WEEK(@Date), DAYOFWEEK(@Date),
DAYOFMONTH(@Date), MONTHNAME(@Date), DAYOFYEAR(@Date),
DATE_FORMAT(@Date,’%Y-%c-%d %h:%i:%s’),DATE_FORMAT(@Date,’%Y-%m-%d’),
WEEKDAY(@Date),
MONTH(@Date);
SET @Date = DATE_ADD(@Date,INTERVAL 1 DAY) ;
END WHILE loop1;
END ##

CALL update_datelookup();