Innodb – phpMyAdmin trigger error

innodbphpmyadminsyntaxtrigger

The following oddly does work, but shows an error in PhpMyAdmin… (see image below)

(in the definition block as seen in the image)

BEGIN
SET NEW.expiry = DATE_ADD(NOW(), INTERVAL NEW.duration SECOND);
SET NEW.delete_date = DATE_ADD(NOW(), INTERVAL (NEW.duration + (3600 * 2)) 
SECOND);
END

END has a red X. The syntax is "Unrecognized statement type. (near END)

WHAT I HAVE TRIED TO FIX THIS

SET NEW.expiry = DATE_ADD(NOW(), INTERVAL NEW.duration SECOND);
SET NEW.delete_date = DATE_ADD(NOW(), INTERVAL (NEW.duration + (3600 * 2)) 
SECOND);

The errors go away in phpMyAdmin but when I click GO it gives a huge error so that's obviously not correct.

I have read people are using define when more than 1 statement is needed so I tried…

DELIMITER $$
BEGIN
SET NEW.expiry = DATE_ADD(NOW(), INTERVAL NEW.duration SECOND);
SET NEW.delete_date = DATE_ADD(NOW(), INTERVAL (NEW.duration + (3600 * 2)) SECOND);
END$$

DELIMITER ;

I get unexpected characters ;
then I tried…

DELIMITER $$
BEGIN
SET NEW.expiry = DATE_ADD(NOW(), INTERVAL NEW.duration SECOND)$$
SET NEW.delete_date = DATE_ADD(NOW(), INTERVAL (NEW.duration + (3600 * 2)) SECOND)$$
END$$

DELIMITER ;

Which doesn't work and gives me the original error before I tried fixing anything. So, I don't know what to say. I am obviously a novice to all this. I just spent over an hour reading about triggers, mysql docs, but I don't know what I am doing wrong. I spent another 30 min reading other questions about similar things to see if I can spot the syntax problem I am causing by looking at other peoples examples in how they create triggers in this. I don't know what I am doing wrong. 🙂

Maybe it's valid but it gives an error in PhpMyAdmin incorrectly!!?!??!

Any ideas? Thank you!

It works… but, maybe it's not supposed to. I don't know the proper syntax to do this. Any tips would be appreciated.

I wrote all this because I want to do things properly so I figured I would ask someone.

Thank you for your time.

Best Answer

Instead of editing the existing trigger, try to open a new SQL Console and try this :

DROP TRIGGER IF EXISTS `AuditSetExpiryTokens`;
DELIMITER $$
CREATE DEFINER = CURRENT_USER TRIGGER `AuditSetExpiryTokens` BEFORE 
INSERT ON `token` FOR EACH ROW
BEGIN
 SET NEW.expiry=DATE_ADD(NOW(),INTERVAL NEW.duration SECOND);
 SET NEW.delete_date=DATE_ADD(NOW(),INTERVAL(NEW.duration+(3600*2)) SECOND);
END$$
DELIMITER ;

Related Solutions

Mysql – How to write signal function in MySQL that can be called from Triggers and Stored Functions

What you presented is essentially the correct way for interrupting triggers in MySQL 5.1

I wrote about this before

Dec 23, 2011 : check constraint does not work?
Apr 25, 2011 : Trigger in MySQL to prevent insertion

You cannot do Dynamic SQL in triggers.

You may have to resort to coding the my_signal like this:

CREATE PROCEDURE `my_signal`(error_type INT)
BEGIN
    IF error_type = 1 THEN
        UPDATE `Error: can not insert new OWNER !` set x=1;
    END IF;
    IF error_type = 2 THEN
        UPDATE `Error: Row can not reference itself!` set x=1;
    END IF;
    .
    .
END//

then change the trigger to look like this:

DELIMITER $$
CREATE
  TRIGGER `employee_before_insert` BEFORE INSERT
    ON `Employee`
    FOR EACH ROW BEGIN
      CASE
       WHEN NEW.designation = 'OWNER'  THEN
          CALL my_signal(1);
       WHEN NEW.SSN = NEW.MSSN THEN
          CALL my_signal(2);
      END CASE; 
  END$$   
DELIMITER ;

Sorry for the clumsy programming but Signal Processing is MySQL 5.1 is horrible.

phpMyAdmin – Trigger Syntax

It may be more efficient to simply use a cascading delete via a foreign key. When a row with the same product_code is deleted from products, it will be deleted in sales_detail as well.

ALTER TABLE sales_details
ADD FOREIGN KEY fk_product_code(product_code)
REFERENCES products(product_code)
ON DELETE CASCADE;

But if you'd like to use a trigger to delete rows from the products table that have the same product_code as the row deleted, this should do the trick:

 delimiter // 
 CREATE TRIGGER removeforeing 
  BEFORE DELETE ON products
  FOR EACH ROW BEGIN
    DELETE FROM sales_details WHERE product_code = OLD.product_code;
 END// 
 delimiter ;

Edit

Per documentation,

Within the trigger body, you can refer to columns in the subject table (the table associated with the trigger) by using the aliases OLD and NEW. OLD.col_name refers to a column of an existing row before it is updated or deleted. NEW.col_name refers to the column of a new row to be inserted or an existing row after it is updated.

WHAT I HAVE TRIED TO FIX THIS

Best Answer

Related Solutions

Mysql – How to write signal function in MySQL that can be called from Triggers and Stored Functions

phpMyAdmin – Trigger Syntax

Related Question