You have an error in your SQL syntax; creating a trigger in Joomla
<p>I am using mysql and I want to create a trigger in Joomla 3.4.8.</p>

<p>I have a sql code that <strong>works</strong> in mySQLWorkbench:
<a href="https://i.stack.imgur.com/WUofo.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/WUofo.png" alt="enter image description here"></a></p>

<p>If I run it in a Joomla controller:</p>

<pre><code>$db = JFactory::getDbo();
$sql = $db-&gt;getQuery(true);
$sql = 'LOCK TABLES id0ap_virtuemart_products WRITE; 
        DROP TRIGGER IF EXISTS update_vm_products ;
        DELIMITER //
        CREATE TRIGGER update_vm_products 
        AFTER UPDATE
        ON id0ap_virtuemart_products FOR EACH ROW
        BEGIN
        INSERT INTO id0ap_virtuemart_notifications
        VALUES ("", NEW.virtuemart_product_id, "product","update");
        END //
        DELIMITER ;
        UNLOCK TABLES;';
$db-&gt;setQuery($sql);
$db-&gt;execute();
</code></pre>

<p>I get:</p>

<blockquote>
  <p>1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DROP TRIGGER IF EXISTS update_vm_products ; DELIMITER // ' at line 2 SQL=LOCK TABLES id0ap_virtuemart_products WRITE; DROP TRIGGER IF EXISTS update_vm_products ; DELIMITER // CREATE TRIGGER update_vm_products AFTER UPDATE ON id0ap_virtuemart_products FOR EACH ROW BEGIN INSERT INTO id0ap_virtuemart_notifications VALUES ("", NEW.virtuemart_product_id, "product","update"); END // DELIMITER ; UNLOCK TABLES;</p>
</blockquote>

<p>I can't find the problem. But I have dicovered something strange while I was trying to solve it. If I modify the sql deleting DELIMITERS and the DROP part:</p>

<pre><code>$sql = 'CREATE TRIGGER update_vm_products 
        AFTER UPDATE
        ON id0ap_virtuemart_products FOR EACH ROW
        BEGIN
        INSERT INTO id0ap_virtuemart_notifications
        VALUES ("", NEW.virtuemart_product_id, "product","update");
        END ;';
</code></pre>

<p>whether you just try to create the trigger it works and create the trigger, only fails when you add the DROP part. Any idea? Is there an object-oriented way to do this?</p>

<p>Thanks in advance!</p>