Jump to content


Photo

Smaal Bug With Editing Triggers


  • Please log in to reply
26 replies to this topic

#16 jonb2

jonb2

    Member

  • Members
  • PipPip
  • 17 posts

Posted 10 April 2012 - 02:19 PM

I know that is what the other editors are doing, which is fine. But there is NO way of updating the existing trigger code easily in SQLYog

Look, on the other editors I select the trigger I want to change, edit it, and save it. Next time I look at the trigger code, I can see the edit has happened.

Why is it so easy on the other apps and not in SQLYog ?????????????????????????????




"Save it back immediately" means in SQL: 1) DROP TRIGGER + 2) CREATE TRIGGER. That is what *execute all* does in SQLyog when the editor contains the two statements. We checked the MySQL server generala log and SQLyog/'execute all' and HeidiSQL/ 'save' execute exactly the same SQL statements in this case. And excuting SQL statements is how a MySQL client communicates with a MySQL server (because that is the only way a client can communciate with the server). What a client can do is listed in MySQL documentation here: http://dev.mysql.com...sql-syntax.html

The TRIGGER does not get executed when 'execute all'. But the CREATE TRIGGER statement does. This means that the server *saves* it (if you want to use that non-standard term)



#17 jonb2

jonb2

    Member

  • Members
  • PipPip
  • 17 posts

Posted 12 April 2012 - 03:40 PM

So, came back to find no answer. Perhaps we can add it to the feature list?




I know that is what the other editors are doing, which is fine. But there is NO way of updating the existing trigger code easily in SQLYog

Look, on the other editors I select the trigger I want to change, edit it, and save it. Next time I look at the trigger code, I can see the edit has happened.

Why is it so easy on the other apps and not in SQLYog ?????????????????????????????



#18 peterlaursen

peterlaursen

    Advanced Member

  • Admin
  • PipPipPip
  • 7,869 posts
  • Gender:Male
  • Location:Skagen, Denmark
  • Interests:well ... jazz/folk music, photography, chess, nature, ecology, history, bicycling, Highland Malts ... well, Lowland Malts and Cognac too actually :-) just wonder how I get the time to touch a computer! SQLyog and MONyog? no that's not interest, that's BASIC NEEDS simply!

Posted 12 April 2012 - 06:10 PM

"They all allow you to edit and save an existing trigger directly" . So does SQLyog as we have explained it multiple times. The discussion is closed from our side.


Computers make your grey hair come off ....

Peter Laursen
Webyog

#19 peterlaursen

peterlaursen

    Advanced Member

  • Admin
  • PipPipPip
  • 7,869 posts
  • Gender:Male
  • Location:Skagen, Denmark
  • Interests:well ... jazz/folk music, photography, chess, nature, ecology, history, bicycling, Highland Malts ... well, Lowland Malts and Cognac too actually :-) just wonder how I get the time to touch a computer! SQLyog and MONyog? no that's not interest, that's BASIC NEEDS simply!

Posted 13 April 2012 - 10:41 AM

What can we do here? You do not understand what we are telling and we do not understand what you are telling.

I can only say that to 'edit a TRIGGER easily' (the term you used in your mail) you

1) open the TRIGGER definition with ALTER TRIGGER from teh Object Browser Object

2) a USE + a DROP TRIGGER + a CREATE TRIGGER statement will display. Edit what you want to edit in the CREATE TRIGGER statement.

3) What you have changed will be saved (on the server) when executing the SQL statements. The TRIGGER does not execute. It is the CREATE TRIGGER statement that does.




Other tools may user other terms in the interface. But they all do the same as we do (DROP the TRIGGER and RECREATE it). The terms we use are the correct SQL terms (as per http://dev.mysql.com...sql-syntax.html - there is absolutely no SAVE TRIGGER or EDIT TRIGGER or similar statment in SQL).





Computers make your grey hair come off ....

Peter Laursen
Webyog

#20 jonb2

jonb2

    Member

  • Members
  • PipPip
  • 17 posts

Posted 13 April 2012 - 11:47 AM

Peter.

I believe there is a mismatch of understanding, sure.

Firstly, to help with the scenario, let me say I am creating/designing a DB. I have run the create DB from my schema in MySQLWorkbench. The triggers (set up in WB) are pre-defined and are handled correctly in both dbForge and Heidi, they are recognised by SQLyog too, but ...

This is how I see it.

A trigger is an action on the server. But it needs a 'script' to define the action.

I hover over a trigger in the object browser, right click, and choose ALTER (which is EDIT SCRIPT in my mind). This is how the other tools refer to it roughly.

Normally, I see the edit window, make some changes, then save the SCRIPT. I do not want to EXECUTE the trigger, just adjust the SCRIPT which instructs the execution of the trigger. In the other tools, this is how it works. It's quick, convenient, and if I return to the trigger script later, I see the changes I have made.

But in SQLyog, So I make my changes, but there is no way of saving the changes in the SCRIPT as far as I can see. Also, it seems to add a spurious delimiter define (DELIMITER ;) at the bottom of the script when I open it.

Hope this makes it clearer. Perhaps download the dbForge trial, set up a false table and define a trigger and edit it to see. You can do the same thing in SQLWorkbench, except you edit it and have to save it as a whole schema change, not in isolation.

Perhaps we might be talking of two different things, not sure, but I want EDIT/SAVE without execution. You, *I think* are talking about executing the trigger on the server.

J.








What can we do here? You do not understand what we are telling and we do not understand what you are telling.

I can only say that to 'edit a TRIGGER easily' (the term you used in your mail) you

1) open the TRIGGER definition with ALTER TRIGGER from teh Object Browser Object

2) a USE + a DROP TRIGGER + a CREATE TRIGGER statement will display. Edit what you want to edit in the CREATE TRIGGER statement.

3) What you have changed will be saved (on the server) when executing the SQL statements. The TRIGGER does not execute. It is the CREATE TRIGGER statement that does.




Other tools may user other terms in the interface. But they all do the same as we do (DROP the TRIGGER and RECREATE it). The terms we use are the correct SQL terms (as per http://dev.mysql.com...sql-syntax.html - there is absolutely no SAVE TRIGGER or EDIT TRIGGER or similar statment in SQL).



#21 jonb2

jonb2

    Member

  • Members
  • PipPip
  • 17 posts

Posted 13 April 2012 - 11:55 AM

Just to help to make it clearer (I hope) and thinking about it. I now believe the other tools save the script in isolation and then 'associate' it with the trigger.



Peter.

I believe there is a mismatch of understanding, sure.

Firstly, to help with the scenario, let me say I am creating/designing a DB. I have run the create DB from my schema in MySQLWorkbench. The triggers (set up in WB) are pre-defined and are handled correctly in both dbForge and Heidi, they are recognised by SQLyog too, but ...

This is how I see it.

A trigger is an action on the server. But it needs a 'script' to define the action.

I hover over a trigger in the object browser, right click, and choose ALTER (which is EDIT SCRIPT in my mind). This is how the other tools refer to it roughly.

Normally, I see the edit window, make some changes, then save the SCRIPT. I do not want to EXECUTE the trigger, just adjust the SCRIPT which instructs the execution of the trigger. In the other tools, this is how it works. It's quick, convenient, and if I return to the trigger script later, I see the changes I have made.

But in SQLyog, So I make my changes, but there is no way of saving the changes in the SCRIPT as far as I can see. Also, it seems to add a spurious delimiter define (DELIMITER ;) at the bottom of the script when I open it.

Hope this makes it clearer. Perhaps download the dbForge trial, set up a false table and define a trigger and edit it to see. You can do the same thing in SQLWorkbench, except you edit it and have to save it as a whole schema change, not in isolation.

Perhaps we might be talking of two different things, not sure, but I want EDIT/SAVE without execution. You, *I think* are talking about executing the trigger on the server.

J.



#22 peterlaursen

peterlaursen

    Advanced Member

  • Admin
  • PipPipPip
  • 7,869 posts
  • Gender:Male
  • Location:Skagen, Denmark
  • Interests:well ... jazz/folk music, photography, chess, nature, ecology, history, bicycling, Highland Malts ... well, Lowland Malts and Cognac too actually :-) just wonder how I get the time to touch a computer! SQLyog and MONyog? no that's not interest, that's BASIC NEEDS simply!

Posted 13 April 2012 - 12:33 PM

"I do not want to EXECUTE the trigger, just adjust the SCRIPT which instructs the execution of the trigger"




This is exactly what happnes if you *execute all*. You will execute a USE statement, a DROP TRIGGER statement and a CREATE TRIGGER statement. This *DOES NOT* execute the trigger. *execute all* means that you exceute all SQL statements in the currently active editor tab. Nothing else.

The TRIGGER is excuted *by the server* before|after INSERT|UPDATE DELETE as defined in the CREATE TRIGGER statement. It is simply not possible for a client to execute a TRIGGER. Only the server process can execute a TRIGGER.




Now: please try once: Do as I have described here. Next try to open it in WB or Heidi or whatever. You will see that exactly the same thing has happend as when you have edited (or whatever term they use) in this other tool.
Computers make your grey hair come off ....

Peter Laursen
Webyog

#23 jonb2

jonb2

    Member

  • Members
  • PipPip
  • 17 posts

Posted 13 April 2012 - 12:51 PM

Peter. Please accept my SINCERE APOLOGIES after all the noise I have made. You are absolutely right !!! I have no idea what my brain was up to, been doing too many midnight runs on this damn project.

I will go and beat myself with a sturdy stick while repeating the mantra -

Computers make your grey hair come off ....
Computers make your grey hair come off ....
Computers make your grey hair come off ....

Kind regards

Jon.

PS
Just leaves a small problem with the spurious delimiter I mentioned (ducking). Open ALTER and I am getting 'DELIMITER ;' put at the end of the edit box.



"I do not want to EXECUTE the trigger, just adjust the SCRIPT which instructs the execution of the trigger"




This is exactly what happnes if you *execute all*. You will execute a USE statement, a DROP TRIGGER statement and a CREATE TRIGGER statement. This *DOES NOT* execute the trigger. *execute all* means that you exceute all SQL statements in the currently active editor tab. Nothing else.

The TRIGGER is excuted *by the server* before|after INSERT|UPDATE DELETE as defined in the CREATE TRIGGER statement. It is simply not possible for a client to execute a TRIGGER. Only the server process can execute a TRIGGER.




Now: please try once: Do as I have described here. Next try to open it in WB or Heidi or whatever. You will see that exactly the same thing has happend as when you have edited (or whatever term they use) in this other tool.



#24 peterlaursen

peterlaursen

    Advanced Member

  • Admin
  • PipPipPip
  • 7,869 posts
  • Gender:Male
  • Location:Skagen, Denmark
  • Interests:well ... jazz/folk music, photography, chess, nature, ecology, history, bicycling, Highland Malts ... well, Lowland Malts and Cognac too actually :-) just wonder how I get the time to touch a computer! SQLyog and MONyog? no that's not interest, that's BASIC NEEDS simply!

Posted 13 April 2012 - 12:53 PM

I would also like to ask you how you would use the mysql command line client to *edit* a trigger? The answer is that you would execute exactly the same SQL statements as SQLyog does. Because this is the only way. And all the other clients do the same - they only use various terms in the GUI and they may not display all the SQL statements they execute for user.


Computers make your grey hair come off ....

Peter Laursen
Webyog

#25 jonb2

jonb2

    Member

  • Members
  • PipPip
  • 17 posts

Posted 13 April 2012 - 12:59 PM

Yup, it is just a different paradigm to how the other tools 'present' it functionally. But does the same thing.


I would also like to ask you how you would use the mysql command line client to *edit* a trigger? The answer is that you would execute exactly the same SQL statements as SQLyog does. Because this is the only way. And all the other clients do the same - they only use various terms in the GUI and they may not display all the SQL statements they execute for user.



#26 peterlaursen

peterlaursen

    Advanced Member

  • Admin
  • PipPipPip
  • 7,869 posts
  • Gender:Male
  • Location:Skagen, Denmark
  • Interests:well ... jazz/folk music, photography, chess, nature, ecology, history, bicycling, Highland Malts ... well, Lowland Malts and Cognac too actually :-) just wonder how I get the time to touch a computer! SQLyog and MONyog? no that's not interest, that's BASIC NEEDS simply!

Posted 13 April 2012 - 01:07 PM

"Open ALTER and I am getting 'DELIMITER ;' put at the end of the edit box"

Because if we did not restet the DELIMITER to its standard value at the end, following statements generated by the GUI (that use the standard ";" delimiter) would not be terminated. Statements in the ALTER TRIGGER tab run in same connection as (almost) everyhing else in a connection tab.


Computers make your grey hair come off ....

Peter Laursen
Webyog

#27 jonb2

jonb2

    Member

  • Members
  • PipPip
  • 17 posts

Posted 13 April 2012 - 01:19 PM

OK, I understand now. Does this mean that as well as a user chosen delimiter ($$), the semi-colon (;) is also recognised at the same time? Perhaps why the statements are terminated by them as a default? I suppose having the options of two allows 'overloading' the default.

e.g.

SET NEW.domain_crc = CRC32(NEW.domain);
SET NEW.url_crc = CRC32(NEW.URL);
SET NEW.ip4n = inet_aton(NEW.ip4a);

"Open ALTER and I am getting 'DELIMITER ;' put at the end of the edit box"

Because if we did not restet the DELIMITER to its standard value at the end, following statements generated by the GUI (that use the standard ";" delimiter) would not be terminated. Statements in the ALTER TRIGGER tab run in same connection as (almost) everyhing else in a connection tab.






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users