Unable to create trigger in SQLYog

Question

I'm seeing a large number of examples on how to do this in MySQL/SQLYog but everything I try gives me an error. I must be misunderstanding something critical here or something, it seems like it should be so simple.

I want to make a column use UUID() as it's default and I've read that triggers are the way to go about this.

I have tried the following methods and they all fail:

CREATE TRIGGER `tips`.`TableKey` BEFORE INSERT ON `tips`.`TableKey` FOR EACH ROW
SET `tips`.`TableKey` = UUID();

error: Unknown system variable 'TableKey'

CREATE TRIGGER UUID_Trigger
AFTER INSERT ON `databasename`.`tips`
FOR EACH ROW
SET `databasename`.`tips`.`TableKey` = UUID();

error: 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 '.TableKey = uuid()' at line 4

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    TRIGGER `databasename`.`tips_uuid` BEFORE INSERT
    ON `databasename`.`tips`
    FOR EACH ROW BEGIN
    IF `databasename`.tips.TableKey IS NULL
      THEN
        SET `databasename`.tips.TableKey = UUID();
      END IF;
    END$$

DELIMITER ;

error: 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 '.TableKey = UUID(); END IF; END' at line 8

CREATE TRIGGER tips_uuid_default
BEFORE INSERT ON tips
FOR EACH ROW
  IF tips.TableKey IS NULL
  THEN
    SET tips.TableKey = UUID();
  END IF;

error: Error Code: 1193 Unknown system variable 'TableKey'

and

Error Code: 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 'END IF' at line 1

Can anyone let me know what I'm doing wrong here? It never seems to know what the TableKey column is so I must be specifying the tables or columns incorrectly?

You update a column using `update .. set` statement, not plain set. You are mix-matching between setting a variable value and updating a column value — Madhur Bhaiya, Oct 11 '18 at 20:51

score 0 · Answer 1 · answered Oct 11 '18 at 20:53

0

You update a column using Update .. Set statement. You are mix-matching between setting a variable value and updating a column value. Try the following instead:

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    TRIGGER `databasename`.`tips_uuid` BEFORE INSERT
    ON `databasename`.`tips`
    FOR EACH ROW BEGIN 

    UPDATE `databasename`.tips 
    SET `databasename`.tips.TableKey = UUID() 
    WHERE `databasename`.tips.TableKey IS NULL;

    END$$

DELIMITER ;

answered Oct 11 '18 at 20:53

Madhur Bhaiya

28,155
10
49
57

It accepted the declaring of the trigger but when I try to add a new row (not touching the TableKey field) I get the following error: "Can't update table 'tips' in stored function/trigger because it is already used by statement which invoked this stored function/trigger." – user3653863 Oct 11 '18 at 20:57
@user3653863 yes we cannot update the same table inside a trigger. What is the actual context of the problem ? Maybe there is another method available other than trigger – Madhur Bhaiya Oct 11 '18 at 20:58
@user3653863 check: https://stackoverflow.com/a/12878145/2469308 – Madhur Bhaiya Oct 11 '18 at 20:59
I just want to emulate the behavior of having UUID() be the default value for TableKey in this table. I was hoping that I could add my normal data to this table (Right now I'm just adding a new row through the Table Data viewer in SQLYog) and the TableKey column would get updated to have a UUID() instead of NULL – user3653863 Oct 11 '18 at 21:00
@user3653863 Why don't you pass UUID() as value in your `insert` statement directly. – Madhur Bhaiya Oct 11 '18 at 21:03
This was kind of a trial for getting this up and running on a different project. I am starting on a new project today and can do what you recommended with my inserts, but there are 1 or 2 other projects with already existing code bases and they do not have these TableKey columns being populated. This would have been especially useful there. – user3653863 Oct 11 '18 at 21:08
That is the accepted way of doing this then, and there's no better/more efficient way? – user3653863 Oct 11 '18 at 21:15
@user3653863 unfortunately there is no way you can update same table in a trigger. You will have to handle it in application code only. Also look at generated columns – Madhur Bhaiya Oct 11 '18 at 22:58

Unable to create trigger in SQLYog

1 Answers1