Jump to content


Photo

Smaal Bug With Editing Triggers


  • Please log in to reply
26 replies to this topic

#1 jonb2

jonb2

    Member

  • Members
  • PipPip
  • 17 posts

Posted 09 April 2012 - 06:39 PM

Hi

Just discovered a small bug. 9.63. Ultimate.

EDIT: I can see why it does this. It's a useful facility for building statements with named entities. But in this case, it's technically wrong and could lead to errors.

1) Navigate to 'triggers' in property window.

2) Right click on trigger - choose alter

3) Double click on trigger in property window - the title of the trigger is 'added' to the end-of-text of the alter edit window.


Kind regards

Jon

#2 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 09 April 2012 - 07:46 PM

It is not a bug. It is per design.




The name of any database object (database, table, column, index trigger .. whatever) will be pasted to the current open edditor window on doubleclick. You can turn off this from tools .. preferences .. 'general' tab .. uncheck 'paste object name in editor on doubleclick'.

What did you expect would happen when doubleclicking on the trigger name?


Computers make your grey hair come off ....

Peter Laursen
Webyog

#3 jonb2

jonb2

    Member

  • Members
  • PipPip
  • 17 posts

Posted 10 April 2012 - 09:35 AM

Hi Peter

Yes, I can see your point. Conceded. But in this case it's recursive. You cannot re-use the trigger name in the edit once it's been created. I can live with it.

I just bought the ultimate version yesterday and was playing with it (missed the voucher which appeared in my inbox this morning, c'est la vie!).

I have another issue. I cannot see an easy way of altering a trigger and saving it without doing an SQL dump. Is there a way? As this seems particularly laborious. If not, I would ask this added to the feature list ASAP :-)

Kind regards

Jon


It is not a bug. It is per design.




The name of any database object (database, table, column, index trigger .. whatever) will be pasted to the current open edditor window on doubleclick. You can turn off this from tools .. preferences .. 'general' tab .. uncheck 'paste object name in editor on doubleclick'.

What did you expect would happen when doubleclicking on the trigger name?



#4 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 10 April 2012 - 09:47 AM

1)

ALTER TRIGGER will open an editor tab. Here you edit what you want. Once done you execute the statements basically like you would execute a simple SELECT statement etc.

Just click the blue 'double arrow' icon (or press SHIFT+F9). This is 'execute all'. All statements on the TAB (The USE, the DROP and the CREATE statements) will be executed.




2)

I do not understand "But in this case it's recursive. You cannot re-use the trigger name in the edit once it's been created". I think simply you are trying to operate the program as it was never designed to be operated.


Computers make your grey hair come off ....

Peter Laursen
Webyog

#5 jonb2

jonb2

    Member

  • Members
  • PipPip
  • 17 posts

Posted 10 April 2012 - 10:30 AM

P. I *think* you have missed my point :-)

Take the trigger below. I want to edit it, not run it. How do I edit and save it - easily? At the moment it wants to save it as SQL if I want to save it.

Secondly, take a look at it and to illustrate. I do not need the title of the trigger copied on a double-click, as it is already created, it already exists, it is not a dead parrot.

I think triggers need their own edit box perhaps.

==============================

DELIMITER $$

USE `iwin`$$

DROP TRIGGER /*!50032 IF EXISTS */ `links_ins`$$

CREATE
/*!50017 DEFINER = 'root'@'localhost' */
TRIGGER `links_ins` BEFORE INSERT ON `main_links`
FOR EACH ROW BEGIN
SET NEW.domain_crc = CRC32(NEW.domain);
SET NEW.url_crc = CRC32(NEW.URL);
SET NEW.ip4n = INET_ATON(NEW.ip4a);
END;
$$

DELIMITER ;

=====================










1)

ALTER TRIGGER will open an editor tab. Here you edit what you want. Once done you execute the statements basically like you would execute a simple SELECT statement etc.

Just click the blue 'double arrow' icon (or press SHIFT+F9). This is 'execute all'. All statements on the TAB (The USE, the DROP and the CREATE statements) will be executed.




2)

I do not understand "But in this case it's recursive. You cannot re-use the trigger name in the edit once it's been created". I think simply you are trying to operate the program as it was never designed to be operated.



#6 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 10 April 2012 - 10:49 AM

If you excecute the CREATE TRIGGER statement you will save it. It will not run/execute




It is a *CREATE TRIGGER* statement that you execute, It is *not* a *EXECUTE TRIGGER* statement. There is no way for a user/a client to execute a TRIGGER. It is the server that executes a TRIGGER and it does "BEFORE INSERT ON `main_links`" in your case.

In this respect there is no difference between SQLyog and all other clients. You *save* a TRIGGER by *executing a CREATE TRIGGER statement*.




This is the TRIGGER implemetation in MySQL simply.


Computers make your grey hair come off ....

Peter Laursen
Webyog

#7 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 10 April 2012 - 10:55 AM

.. so it is no me that does not understand. It is your brain that gets stuck in a misunderstanding of what a TRIGGER is and how it is stored and executed and what "CREATE TRIGGER ..." means.

1) CREATE TRIGGER saves (to a .trg file in the server data directory). 2) Manipulating the table ON which the TRIGGER is defined (from any client) will cause the *server* to execute the TRIGGER. And this is the only way a TRIGGER can be executed. A client cannot execute a TRIGGER.
Computers make your grey hair come off ....

Peter Laursen
Webyog

#8 jonb2

jonb2

    Member

  • Members
  • PipPip
  • 17 posts

Posted 10 April 2012 - 11:08 AM

I use SQL Workbench, Heidi, dbForge. They all allow you to edit and save an existing trigger directly. It is treated as a code snippet.

I say again, I don't want to EXECUTE it, I want to EDIT it and SAVE an existing trigger directly.

I bought SQLyog because it has some stuff that these don't. But editing triggers is not one of them.




.. so it is no me that does not understand. It is your brain that gets stuck in a misunderstanding of what a TRIGGER is and how it is stored and executed and what "CREATE TRIGGER ..." means.

1) CREATE TRIGGER saves (to a .trg file in the server data directory). 2) Manipulating the table ON which the TRIGGER is defined (from any client) will cause the *server* to execute the TRIGGER. And this is the only way a TRIGGER can be executed. A client cannot execute a TRIGGER.



#9 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 10 April 2012 - 11:25 AM

If you want to save 'as a snippet' locally then either

1) file .. save

2) favorites .. add to favorites


Computers make your grey hair come off ....

Peter Laursen
Webyog

#10 jonb2

jonb2

    Member

  • Members
  • PipPip
  • 17 posts

Posted 10 April 2012 - 11:43 AM

Nope, I just want to do what the other admin tools allow you to do :-)


If you want to save 'as a snippet' locally then either

1) file .. save

2) favorites .. add to favorites



#11 ashwin

ashwin

    Advanced Member

  • Members
  • PipPipPip
  • 275 posts
  • Gender:Male
  • Location:Bangalore, India

Posted 10 April 2012 - 12:43 PM

I use SQL Workbench, Heidi, dbForge. They all allow you to edit and save an existing trigger directly. It is treated as a code snippet.
I say again, I don't want to EXECUTE it, I want to EDIT it and SAVE an existing trigger directly.


@jonb2.. We checked with HeidiSQL. Yes, they do allow to edit and save trigger. But..on SAVE they execute these queries(Checked from their query LOG)-
DROP TRIGGER `customer_create_date`;
/* 0 rows affected. */
CREATE DEFINER=`root`@`localhost` TRIGGER `customer_create_date` BEFORE INSERT ON `customer` FOR EACH ROW SET NEW.create_date = NOW();

Now, with SQLyog when you do Execute All these queries are executed-

DROP TRIGGER /*!50032 IF EXISTS */ `customer_create_date`; 
 CREATE /*!50017 DEFINER = 'root'@'localhost' */ TRIGGER `customer_create_date` BEFORE INSERT ON `customer` FOR EACH ROW SET NEW.create_date = NOW() ;

So both HeidiSQL and SQLyog does the same. But they have this SAVE and we have Execute All.

If I do not understand your concern it would be better if we have GoToMeeting session. Please tell us in which timezone you are in and suitable time to have online screen sharing session using GoToMeeting?
You can create a ticket for privacy(By mailing us to support@webyog.com).

#12 jonb2

jonb2

    Member

  • Members
  • PipPip
  • 17 posts

Posted 10 April 2012 - 01:46 PM

Hi Ashwin

Not sure we need a session yet, but thank you for the offer.

I have included 2 screenshots from dbForge and MySQLworkbench to illustrate how they allow they handle editing triggers. Note that they show the 'not saved' asterisk, not Workbench in the image, as I forgot to edit it. dbForge does this directly, Workbench is a schema update. But it's easy and persistent.

What I want is to open up the trigger code, edit it, and save it back immediately. When I next open it. I see my changes. Simple really. I cannot see any easy way to do this with your app.

Kind regards

Jon






@jonb2.. We checked with HeidiSQL. Yes, they do allow to edit and save trigger. But..on SAVE they execute these queries(Checked from their query LOG)-

DROP TRIGGER `customer_create_date`;
/* 0 rows affected. */
CREATE DEFINER=`root`@`localhost` TRIGGER `customer_create_date` BEFORE INSERT ON `customer` FOR EACH ROW SET NEW.create_date = NOW();

Now, with SQLyog when you do Execute All these queries are executed-

DROP TRIGGER /*!50032 IF EXISTS */ `customer_create_date`; 
 CREATE /*!50017 DEFINER = 'root'@'localhost' */ TRIGGER `customer_create_date` BEFORE INSERT ON `customer` FOR EACH ROW SET NEW.create_date = NOW() ;

So both HeidiSQL and SQLyog does the same. But they have this SAVE and we have Execute All.

If I do not understand your concern it would be better if we have GoToMeeting session. Please tell us in which timezone you are in and suitable time to have online screen sharing session using GoToMeeting?
You can create a ticket for privacy(By mailing us to support@webyog.com).

Attached Files



#13 jonb2

jonb2

    Member

  • Members
  • PipPip
  • 17 posts

Posted 10 April 2012 - 01:57 PM

PS.

A. I have just checked Heidi, it does what I want. It saves the edit.


@jonb2.. We checked with HeidiSQL. Yes, they do allow to edit and save trigger. But..on SAVE they execute these queries(Checked from their query LOG)-

DROP TRIGGER `customer_create_date`;
/* 0 rows affected. */
CREATE DEFINER=`root`@`localhost` TRIGGER `customer_create_date` BEFORE INSERT ON `customer` FOR EACH ROW SET NEW.create_date = NOW();

Now, with SQLyog when you do Execute All these queries are executed-

DROP TRIGGER /*!50032 IF EXISTS */ `customer_create_date`; 
 CREATE /*!50017 DEFINER = 'root'@'localhost' */ TRIGGER `customer_create_date` BEFORE INSERT ON `customer` FOR EACH ROW SET NEW.create_date = NOW() ;

So both HeidiSQL and SQLyog does the same. But they have this SAVE and we have Execute All.

If I do not understand your concern it would be better if we have GoToMeeting session. Please tell us in which timezone you are in and suitable time to have online screen sharing session using GoToMeeting?
You can create a ticket for privacy(By mailing us to support@webyog.com).



#14 jonb2

jonb2

    Member

  • Members
  • PipPip
  • 17 posts

Posted 10 April 2012 - 02:01 PM

Here is a screenshot from Heidi, note the stupid edit at the end of the code and the enabled save button at the bottom.




PS.

A. I have just checked Heidi, it does what I want. It saves the edit.

Attached Files



#15 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 10 April 2012 - 02:11 PM

"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)


Computers make your grey hair come off ....

Peter Laursen
Webyog




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users