user3552185 user3552185 - 9 days ago 5
MySQL Question

Tiggers , before inserted trigger?

I have two tables , table (files) and table (Hashes) i want to create a procedure that inserts new (file) wich will add a ( Hash ) if not exists and then inserts its ( ID ) in the ( files) table , if exists select the (ID) and insert it , and i also must use ( before insert on files trigger ) i ried but it either gives me an CONSTRAINT error OR Insert in hash and no file inserted.

CREATE TABLE `Hashes` (
`ID` int(255) unsigned NOT NULL AUTO_INCREMENT,
`Hash` varchar(255) NOT NULL,
`Counter` int(255) NOT NULL DEFAULT '1',
PRIMARY KEY (`ID`),
UNIQUE KEY `Hash` (`Hash`)
)


and

CREATE TABLE `Files` (
`ID` int(255) unsigned NOT NULL AUTO_INCREMENT,
`Name` varchar(255) NOT NULL,
`ParentPath` varchar(255) DEFAULT NULL,
`Size` double NOT NULL,
`Date` date NOT NULL,
`ParentID` int(255) unsigned NOT NULL DEFAULT '0',
`HashID` int(255) unsigned DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `fk_Hash_ID` (`HashID`),
CONSTRAINT `fk_Hash_ID` FOREIGN KEY (`HashID`) REFERENCES `hashes` (`ID`)
)

////////
CREATE PROCEDURE SP_Files_Insert
(
parmName VARCHAR(255),
parmParentPath VARCHAR(255),
parmSize DOUBLE,
parmDate DATE,
parmParentID INTEGER,
parmHash VARCHAR(255)
)
BEGIN

INSERT INTO Files( Name, ParentPath, Size, Date, ParentID, HashID)
VALUES(parmName, parmParentPath, parmSize, parmDate, parmParentID, LAST_INSERT_ID());

END;

////////////
CREATE TRIGGER TR_Insert_File BEFORE INSERT ON Files
FOR EACH ROW
BEGIN

INSERT INTO Hash( Hash, Counter)
VALUE( parmHash, 1)
ON DUPLICATE KEY UPDATE ID = LAST_INSERT_ID(ID), Counter = Counter + 1;

END;

Answer

use delimiter in your trigger like below that will work for you

delimiter \\
CREATE TRIGGER TR_Insert_File BEFORE INSERT ON Files 
FOR EACH ROW
BEGIN

  INSERT INTO Hash( Hash, Counter) 
    VALUE( parmHash, 1) 
    ON DUPLICATE KEY UPDATE ID = LAST_INSERT_ID(ID), Counter = Counter + 1;

END\\ 
delimiter ;