0
Well I am mounting a small code in PHP 8, PDO and Mysql 8. It will be responsible for creating a bunch of data and loading the tables from a file. SQL
So far I’m doing it this way:
// Tenho as constantes de conexão
DB_HOST = "localhost";
DB_USER = "root";
DB_PASSWD = "";
// Aqui eu crio o novo Banco de dados
// Conexão
$conn = new PDO("mysql:host=" . DB_HOST, DB_USER, DB_PASSWD);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Cria o BD
$sql = "CREATE DATABASE IF NOT EXISTS `bdVirgem`";
$conn->exec($sql);
Good up to that point this ok, the bank is created normally. Well now I will load the contents of the file . Sql into a variable and then pass it to the PDO:
// Carrega o arquivo .SQL
$file = file_get_contents("bdVirgem.sql");
// Opções de conexão
$options = [
PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8; SET time_zone='America/Sao_Paulo';",
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ,
PDO::ATTR_CASE => PDO::CASE_NATURAL
];
// Conexão
$conn = new PDO(
'mysql:host='.DB_HOST.';dbname='.$dbName.'',
DB_USER,
DB_PASSWD,
$options
);
// Executas comandos do arquivo .sql
$stmt = $conn->prepare($file);
$stmt->execute();
Good so far the database and created and the tables are loaded, but I’m only having a BUG.
At the end of the . sql file I have the creation instructions for Trigger
, They are not being created.
Follow an example:
CREATE TRIGGER removeProductCategory BEFORE DELETE ON productCategory
FOR EACH ROW
BEGIN
UPDATE product SET categoryCode = null WHERE categoryCode = OLD.code;
END;
-- ++++++++++++
CREATE TRIGGER removeCategory BEFORE DELETE ON category
FOR EACH ROW
BEGIN
UPDATE cashBookEntry SET categoryCode = null WHERE categoryCode = OLD.code;
END;
-- +++++++++++
CREATE TRIGGER addProductStock AFTER INSERT ON stockHandling
FOR EACH ROW
BEGIN
UPDATE product SET stock = (stock + NEW.quantity), level = (level + 1) WHERE code = NEW.productCode;
END;
CREATE TRIGGER subProductStock AFTER DELETE ON stockHandling
FOR EACH ROW
BEGIN
UPDATE product SET stock = (stock - OLD.quantity), level = (level - 1) WHERE code = OLD.productCode;
END;
Well someone knows where the problem occurs?
Try removing the
DELIMITER $
and changeEND$
forEND;
and removeDELIMITER ;
– Guilherme Nascimento
I tried that way and I made a mistake:
#1064 - Você tem um erro de sintaxe no seu SQL próximo a '' na linha 7
, Remembering that I have severalTriggers
file. I will update the question with an example.– Hugo Borges