Re: Assistance with trigger
Please ignore my request as I have managed to figure this out. Thank you. had to rewrite the insert to use Old.Column1... etc... and that works quite well. From: Machiel Richards Sent: Friday, 26 April 2019 10:48 AM To: mysql@lists.mysql.com Subject: Assistance with trigger Hi All I am hoping this email finds all well. I would like to request some assistance with a MySQL trigger please. We need to implement a trigger that will in short terms make a backup of a row before it gets deleted. so we have tableA and TableB (backup table). I added a before delete trigger to insert into TABLEB select from TABLEA... However upon delete it inserts the whole table instead of only the single row being deleted. Here is my insert and select statement : INSERT INTO tableB (Column1,backup_date,updated_by_user,Column4) SELECT Column1,NOW(),`username`,Column2 from tableA; How can I rewrite this section to only select and insert the rows that are actually being deleted. Your help would be greatly appreciated as I have not been able to find the answer on google as yet. Regards
Assistance with trigger
Hi All I am hoping this email finds all well. I would like to request some assistance with a MySQL trigger please. We need to implement a trigger that will in short terms make a backup of a row before it gets deleted. so we have tableA and TableB (backup table). I added a before delete trigger to insert into TABLEB select from TABLEA... However upon delete it inserts the whole table instead of only the single row being deleted. Here is my insert and select statement : INSERT INTO tableB (Column1,backup_date,updated_by_user,Column4) SELECT Column1,NOW(),`username`,Column2 from tableA; How can I rewrite this section to only select and insert the rows that are actually being deleted. Your help would be greatly appreciated as I have not been able to find the answer on google as yet. Regards
Re: Renaming a table that has a trigger, then attempting to drop that trigger results in 'table doesn't exist' error.
Hello Michael, On 1/6/2016 12:51 PM, Michael Vaughan wrote: If you execute the script below, you will get the following error: 'Error Code: 1146. Table 'testschema.TestRenamed' doesn't exist" delimiter // CREATE TABLE Test( id int not null primary key auto_increment, name varchar(255) )// CREATE TRIGGER TEST_TRIGGER BEFORE INSERT ON Test FOR EACH ROW BEGIN SET NEW.name = CONCAT(NEW.name, '_X'); END// RENAME TABLE Test TO TestRenamed// DROP TRIGGER Test.TEST_TRIGGER// Are there any workarounds for this? Thank you for also reporting this as a bug. http://bugs.mysql.com/bug.php?id=79873 -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Integrated Cloud Applications & Platform Services Office: Blountville, TN Become certified in MySQL! Visit https://www.mysql.com/certification/ for details. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Renaming a table that has a trigger, then attempting to drop that trigger results in 'table doesn't exist' error.
If you execute the script below, you will get the following error: 'Error Code: 1146. Table 'testschema.TestRenamed' doesn't exist" delimiter // CREATE TABLE Test( id int not null primary key auto_increment, name varchar(255) )// CREATE TRIGGER TEST_TRIGGER BEFORE INSERT ON Test FOR EACH ROW BEGIN SET NEW.name = CONCAT(NEW.name, '_X'); END// RENAME TABLE Test TO TestRenamed// DROP TRIGGER Test.TEST_TRIGGER// Are there any workarounds for this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Edit MySQL Trigger in Workbench problem
Hi, I've created a Trigger and want to edit it. Using MySQL Workbench, I can Alter the table, and click Triggers and select the trigger action I want to edit (on my local database, MySQL running on same PC) However, if I try the exact same procedure on a Trigger on a remote database, I don't see the Triggers under each action. The only way these can be edited is to drop and create it again. Thanks Neil
Editing existing Trigger MySQL 5.6
Hi, Using Workbench with MySQL 5.6 how do I edit a existing Trigger. Do I need to DROP the Trigger and create a new one ? If that is the case how can you run start command in a live environment ? Thanks Neil
Re: Editing existing Trigger MySQL 5.6
2013/05/29 10:39 +0100, Neil Tompkins Using Workbench with MySQL 5.6 how do I edit a existing Trigger. Do I need to DROP the Trigger and create a new one ? If that is the case how can you run start command in a live environment ? Whatever appearance Workbench adds (I do not know it), replacing a simple trigger looks something like this: DROP TRIGGER IF EXISTS tr; CREATE TRIGGER tr ...; . What do you mean by 'start'? The Windows command-line command? START SLAVE? START TRANSACTION? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Can not add trigger
I'm using mysql version 5.1.66 on ubuntu and can not add a trigger any more. When trying to add a trigger In phpmyadmin I get Your SQL query has been executed successfully but when I issue the command show triggers; no triggers are listed. Im logged into mysql as root and Im not sure why I get a successfully message and can not find the new trigger. I've looked in the schema table but its not there. I've added triggers before on this system. I'm stumped as to why its stopped working. Please help. Thanks Aaron
Re:Cant drop trigger.
Is there white space at the end of the trigger name? A On Feb 20, 2012 10:04 PM, Brent Clark brentgclarkl...@gmail.com wrote: Hi Guys I wonder if someone could urgently help me Im trying to drop some triggers. But mysql says the trigger does not exist. I ran SELECT TRIGGER_SCHEMA, EVENT_OBJECT_TABLE, TRIGGER_NAME FROM INFORMATION_SCHEMA.TRIGGERS +++--+ | TRIGGER_SCHEMA | EVENT_OBJECT_TABLE | TRIGGER_NAME | +++--+ | abc_db1 | foods | Foods Insert to Contributions,Search | But if I run DROP TRIGGER `abc_db1`.`Foods Insert to Contributions\,Search`; ERROR 1360 (HY000): Trigger does not exist I tried backtick, single quotes, doubles quotes. nothing appears to work. Thanks Brent -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: trick trigger
Just adding an extra note to the already good Shawn response. Theoretically this is just as any booking system that needs to be run in transactions (db or not). What you are doing is not trivial if that makes you feel better. The problem is basically that the web interface is asynchronous so you have to 'watch' your transaction in some way. Ad Shawn says you might collect all needed data on the user, check and eventually book on server or return the conflict. @Shawn Isn't data integrity constraints part of the business logic? Cheers Claudio On Jan 11, 2012 7:40 PM, Shawn Green (MySQL) shawn.l.gr...@oracle.com wrote: Hello John, On 1/11/2012 11:16, John G. Heim wrote: I am working on an app to allow a committee to schedule classes. The members of the committee can all update the database by changing the time or the instructor for a class. I have to write an app to warn them when they've scheduled an instructor for 2 classes at the same time or if they've scheduled any of a large list of classes at the same time. For example, they shouldn't schedule Calculus 212 at the same time as Physics 302 because a student might want to take both classes. And obviously, they shouldn't schedule Professor Higgenbothom to teach both Calculus 212 and Probability 278 at 10:00 AM on Monday, Wednesday, and Friday. The problem isn't actually writing mysql to select the conflicts. The problem is when and how to run the code. I could put it in a trigger but say someone assigns Dr. Higgy to teach Calc 212 at 10 AM MWF. They need to be able to see that he is now scheduled for another class if they look at Probability 278. Get the problem? An update to one record can necessitate an update to any number of other records. I'm just looking for basic suggestions on how you'd deal with this. Should I attempt to write a trigger that updates both Calc 212 and Physics 302 when either is changed? Am I going to create an infinate loop? I am thinking of telling the committee that it can't be done and they'll have to wait for the list of conflicts to be recalculated by a background process once an hour or so. My current database structure is that there is a link table for conflicts. If Calc 212 is scheduled at the same time as Physics 302, that is shown by there being 2 records in a conflicts table. The conflicts table would contain a record with the primary key for Calc 212, the pkey for Physics 302, and a code indicating that its a course conflict. There'd also be a record for Physics 302 indicating that it has a conflict with Calc 212. If Prof. Higgy is also scheduled to tach Calc 212 and Probability 278 at the same time, that would also create 2 records in the conflicts table. Like this: calc212 | phys302 | course_conflict phys302 | calc212 | courseConflict calc212 | prob278 | instructorConflict prob278 | calc212 | instructorConflict Then my web app can do a select for conflicts when displaying Calc 212, Probabbility 278, or Physics 302. But how to get that data into the table? I'm thinking of trying to write a trigger so that wen a class record is updated, the trigger deletes the conflicts records for the class if the id appears in either column 1 or column 2, re-calculate conflicts, and re-add the conflicts records. But if anybody has basic suggestions for a completely different approach, I'd like to hear them. This is all a matter of GUI design and application logic. For example, you could force the user to wait for some kind of database error before realizing that the data they just entered was invalid or you can pre-select conflict lists from the database and block out certain times and people as 'already used' before they make their selections. This requires your application to check with the database at certain events. Let's say you want to schedule a class for Higgy to teach Calc 212, well there are at least two lists, from your description, that you need to know before allowing the user to pick a date and time: 1) the list of all classes that Higgy is already teaching 2) the list of any other classes that might interfere with Calc 212 Some additional lists may also be useful * Any other Calc 212 sections already scheduled for other professors * Any 'no classes here' schedule preferences for Higgy * The list of teaching areas that may be available/unavailable in which your Calc 212 may be taught. These all need to be added to the logic present at the time the scheduler wants to make their choices so that they can avoid many un-necessary trips to the database for every schedule they want to create. Another thing to do is to temporarily block (not with a database-level transaction) access to both Higgy and Calc 212 to minimize the chance of conflicting with the changes made to the database by someone else also trying to enter scheduling information. Summary : * Get as much data as you can get before the request leaves the user. This frees up
Re: trick trigger
My two cents are this. This is the kind of problems they invented transactions for. If you find yourself doing this on non-transactional tables, you will need to use a lot of error checking, triggers, application checks and post-cleanups to make it work somehow. Likely, every once in a while you will still have to look at your tables and make sure there is no junk there. The point is, the more natural (and easy) way of resolving it is with transactions. The idea to do things like lock + check if there are no conflicts + update is also well handled with database procedures. Again, this is what they were originally invented for. This is an extension of Shawn's and Claudio's ideas about letting the database handle integrity. If both transactions and procedures are applied, the resolution will likely become trivial. The app will call the procedure, the procedure will lock the record in question, make checks, carry out updates and return success/failure codes. On Jan 16, 2012, at 1:18 AM, Claudio Nanni wrote: Just adding an extra note to the already good Shawn response. Theoretically this is just as any booking system that needs to be run in transactions (db or not). What you are doing is not trivial if that makes you feel better. The problem is basically that the web interface is asynchronous so you have to 'watch' your transaction in some way. Ad Shawn says you might collect all needed data on the user, check and eventually book on server or return the conflict. @Shawn Isn't data integrity constraints part of the business logic? Cheers Claudio On Jan 11, 2012 7:40 PM, Shawn Green (MySQL) shawn.l.gr...@oracle.com wrote: Hello John, On 1/11/2012 11:16, John G. Heim wrote: I am working on an app to allow a committee to schedule classes. The members of the committee can all update the database by changing the time or the instructor for a class. I have to write an app to warn them when they've scheduled an instructor for 2 classes at the same time or if they've scheduled any of a large list of classes at the same time. For example, they shouldn't schedule Calculus 212 at the same time as Physics 302 because a student might want to take both classes. And obviously, they shouldn't schedule Professor Higgenbothom to teach both Calculus 212 and Probability 278 at 10:00 AM on Monday, Wednesday, and Friday. The problem isn't actually writing mysql to select the conflicts. The problem is when and how to run the code. I could put it in a trigger but say someone assigns Dr. Higgy to teach Calc 212 at 10 AM MWF. They need to be able to see that he is now scheduled for another class if they look at Probability 278. Get the problem? An update to one record can necessitate an update to any number of other records. I'm just looking for basic suggestions on how you'd deal with this. Should I attempt to write a trigger that updates both Calc 212 and Physics 302 when either is changed? Am I going to create an infinate loop? I am thinking of telling the committee that it can't be done and they'll have to wait for the list of conflicts to be recalculated by a background process once an hour or so. My current database structure is that there is a link table for conflicts. If Calc 212 is scheduled at the same time as Physics 302, that is shown by there being 2 records in a conflicts table. The conflicts table would contain a record with the primary key for Calc 212, the pkey for Physics 302, and a code indicating that its a course conflict. There'd also be a record for Physics 302 indicating that it has a conflict with Calc 212. If Prof. Higgy is also scheduled to tach Calc 212 and Probability 278 at the same time, that would also create 2 records in the conflicts table. Like this: calc212 | phys302 | course_conflict phys302 | calc212 | courseConflict calc212 | prob278 | instructorConflict prob278 | calc212 | instructorConflict Then my web app can do a select for conflicts when displaying Calc 212, Probabbility 278, or Physics 302. But how to get that data into the table? I'm thinking of trying to write a trigger so that wen a class record is updated, the trigger deletes the conflicts records for the class if the id appears in either column 1 or column 2, re-calculate conflicts, and re-add the conflicts records. But if anybody has basic suggestions for a completely different approach, I'd like to hear them. This is all a matter of GUI design and application logic. For example, you could force the user to wait for some kind of database error before realizing that the data they just entered was invalid or you can pre-select conflict lists from the database and block out certain times and people as 'already used' before they make their selections. This requires your application to check with the database at certain events. Let's say you want to schedule a class for Higgy to teach Calc 212, well
trick trigger
I am working on an app to allow a committee to schedule classes. The members of the committee can all update the database by changing the time or the instructor for a class. I have to write an app to warn them when they've scheduled an instructor for 2 classes at the same time or if they've scheduled any of a large list of classes at the same time. For example, they shouldn't schedule Calculus 212 at the same time as Physics 302 because a student might want to take both classes. And obviously, they shouldn't schedule Professor Higgenbothom to teach both Calculus 212 and Probability 278 at 10:00 AM on Monday, Wednesday, and Friday. The problem isn't actually writing mysql to select the conflicts. The problem is when and how to run the code. I could put it in a trigger but say someone assigns Dr. Higgy to teach Calc 212 at 10 AM MWF. They need to be able to see that he is now scheduled for another class if they look at Probability 278. Get the problem? An update to one record can necessitate an update to any number of other records. I'm just looking for basic suggestions on how you'd deal with this. Should I attempt to write a trigger that updates both Calc 212 and Physics 302 when either is changed? Am I going to create an infinate loop? I am thinking of telling the committee that it can't be done and they'll have to wait for the list of conflicts to be recalculated by a background process once an hour or so. My current database structure is that there is a link table for conflicts. If Calc 212 is scheduled at the same time as Physics 302, that is shown by there being 2 records in a conflicts table. The conflicts table would contain a record with the primary key for Calc 212, the pkey for Physics 302, and a code indicating that its a course conflict. There'd also be a record for Physics 302 indicating that it has a conflict with Calc 212. If Prof. Higgy is also scheduled to tach Calc 212 and Probability 278 at the same time, that would also create 2 records in the conflicts table. Like this: calc212 | phys302 | course_conflict phys302 | calc212 | courseConflict calc212 | prob278 | instructorConflict prob278 | calc212 | instructorConflict Then my web app can do a select for conflicts when displaying Calc 212, Probabbility 278, or Physics 302. But how to get that data into the table? I'm thinking of trying to write a trigger so that wen a class record is updated, the trigger deletes the conflicts records for the class if the id appears in either column 1 or column 2, re-calculate conflicts, and re-add the conflicts records. But if anybody has basic suggestions for a completely different approach, I'd like to hear them. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: trick trigger
Hello John, On 1/11/2012 11:16, John G. Heim wrote: I am working on an app to allow a committee to schedule classes. The members of the committee can all update the database by changing the time or the instructor for a class. I have to write an app to warn them when they've scheduled an instructor for 2 classes at the same time or if they've scheduled any of a large list of classes at the same time. For example, they shouldn't schedule Calculus 212 at the same time as Physics 302 because a student might want to take both classes. And obviously, they shouldn't schedule Professor Higgenbothom to teach both Calculus 212 and Probability 278 at 10:00 AM on Monday, Wednesday, and Friday. The problem isn't actually writing mysql to select the conflicts. The problem is when and how to run the code. I could put it in a trigger but say someone assigns Dr. Higgy to teach Calc 212 at 10 AM MWF. They need to be able to see that he is now scheduled for another class if they look at Probability 278. Get the problem? An update to one record can necessitate an update to any number of other records. I'm just looking for basic suggestions on how you'd deal with this. Should I attempt to write a trigger that updates both Calc 212 and Physics 302 when either is changed? Am I going to create an infinate loop? I am thinking of telling the committee that it can't be done and they'll have to wait for the list of conflicts to be recalculated by a background process once an hour or so. My current database structure is that there is a link table for conflicts. If Calc 212 is scheduled at the same time as Physics 302, that is shown by there being 2 records in a conflicts table. The conflicts table would contain a record with the primary key for Calc 212, the pkey for Physics 302, and a code indicating that its a course conflict. There'd also be a record for Physics 302 indicating that it has a conflict with Calc 212. If Prof. Higgy is also scheduled to tach Calc 212 and Probability 278 at the same time, that would also create 2 records in the conflicts table. Like this: calc212 | phys302 | course_conflict phys302 | calc212 | courseConflict calc212 | prob278 | instructorConflict prob278 | calc212 | instructorConflict Then my web app can do a select for conflicts when displaying Calc 212, Probabbility 278, or Physics 302. But how to get that data into the table? I'm thinking of trying to write a trigger so that wen a class record is updated, the trigger deletes the conflicts records for the class if the id appears in either column 1 or column 2, re-calculate conflicts, and re-add the conflicts records. But if anybody has basic suggestions for a completely different approach, I'd like to hear them. This is all a matter of GUI design and application logic. For example, you could force the user to wait for some kind of database error before realizing that the data they just entered was invalid or you can pre-select conflict lists from the database and block out certain times and people as 'already used' before they make their selections. This requires your application to check with the database at certain events. Let's say you want to schedule a class for Higgy to teach Calc 212, well there are at least two lists, from your description, that you need to know before allowing the user to pick a date and time: 1) the list of all classes that Higgy is already teaching 2) the list of any other classes that might interfere with Calc 212 Some additional lists may also be useful * Any other Calc 212 sections already scheduled for other professors * Any 'no classes here' schedule preferences for Higgy * The list of teaching areas that may be available/unavailable in which your Calc 212 may be taught. These all need to be added to the logic present at the time the scheduler wants to make their choices so that they can avoid many un-necessary trips to the database for every schedule they want to create. Another thing to do is to temporarily block (not with a database-level transaction) access to both Higgy and Calc 212 to minimize the chance of conflicting with the changes made to the database by someone else also trying to enter scheduling information. Summary : * Get as much data as you can get before the request leaves the user. This frees up the database to handle just the data changes as they need to happen. Conflicts can still exist (always assume someone else may steal the room, for example) and those may need to be resolved through a different process. * Keep the business logic in your application, leave the data integrity rules to the database. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Mysql Trigger
Hi Members Whats wrong i am doing in following create trigger statement Create trigger test before insert on user for each row begin set New.host=upper(new.host) end Above statement giving error check sql syntax , please guide me. I want to put host field value in uppercase in user table -- http://linuxmantra.com
Re: Mysql Trigger
Am 10.07.2011 10:32, schrieb vishesh kumar: Hi Members Whats wrong i am doing in following create trigger statement Create trigger test before insert on user for each row begin set New.host=upper(new.host) end Above statement giving error check sql syntax , please guide me. I want to put host field value in uppercase in user table a) why before insert b) why for each row when a single update can do this c) update table_name set fieldname=upper(fieldname) signature.asc Description: OpenPGP digital signature
Re: Mysql Trigger
Thanks for reply , but i want to do only for certain record that i will put using IF On Sun, Jul 10, 2011 at 2:42 PM, Reindl Harald h.rei...@thelounge.netwrote: Am 10.07.2011 10:32, schrieb vishesh kumar: Hi Members Whats wrong i am doing in following create trigger statement Create trigger test before insert on user for each row begin set New.host=upper(new.host) end Above statement giving error check sql syntax , please guide me. I want to put host field value in uppercase in user table a) why before insert b) why for each row when a single update can do this c) update table_name set fieldname=upper(fieldname) -- http://linuxmantra.com
Re: Mysql Trigger
On 7/10/2011 3:32 AM, vishesh kumar wrote:Hi Members Whats wrong i am doing in following create trigger statement Create trigger test before insert on user for each row begin set New.host=upper(new.host) end Above statement giving error check sql syntax , please guide me. I want to put host field value in uppercase in user table One-liners don't need DELIMITERs, so you can write: create trigger test before insert on user for each row set new.host=upper(new.host); Multi-line Triggers need BEGIN...END so they need DELIMITERs too: drop trigger if exists test; delimiter go create trigger test before insert on user for each row begin set new.c=upper(new.c); set ...c ... end; go delimiter ; A note on names: once you have too many tables Triggers to remember, you wish you'd adopted a naming convention that helps you remember object names. A good convention for Trigger names is TablenameWhenAction, so yours would be TestBeforeIns. PB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: trigger-dumping
2011/03/10 23:03 -0600, Peter Brawley On 3/10/2011 8:10 PM, h...@tbbs.net wrote: MYSQLDUMP.EXE (Ver 10.13 Distrib 5.5.8, for Win32 (x86)) has flags for trigger-dumping; the help that I downloaded for this version says it dumps triggers--but it does not. Now what? Works here in 5.5.8. PB Maybe it has something to do with the hybrid version that I see that I have, 5.1 daemon with 5.5 command-line programs. At first I installed 5.5, could not get 'root' or any other user to use it, uninstalled it (but clearly not fully), and installed 5.1. I would like to start from the beginning with 5.5; any idea how to really purge my Vista machine for a really fresh start? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: trigger-dumping
any idea how to really purge my Vista machine for a really fresh start? How about installing a different OS :-) , or failing that a different version of Windows? I'm sorry, we declined to install Vista on any of our boxes. We've had no trouble upgrading to 5.5 from 5.1 on other Windows boxes. PB - On 3/11/2011 10:31 AM, Sándor Halász wrote: 2011/03/10 23:03 -0600, Peter Brawley On 3/10/2011 8:10 PM, h...@tbbs.net wrote: MYSQLDUMP.EXE (Ver 10.13 Distrib 5.5.8, for Win32 (x86)) has flags for trigger-dumping; the help that I downloaded for this version says it dumps triggers--but it does not. Now what? Works here in 5.5.8. PB Maybe it has something to do with the hybrid version that I see that I have, 5.1 daemon with 5.5 command-line programs. At first I installed 5.5, could not get 'root' or any other user to use it, uninstalled it (but clearly not fully), and installed 5.1. I would like to start from the beginning with 5.5; any idea how to really purge my Vista machine for a really fresh start? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
trigger-dumping
MYSQLDUMP.EXE (Ver 10.13 Distrib 5.5.8, for Win32 (x86)) has flags for trigger-dumping; the help that I downloaded for this version says it dumps triggers--but it does not. Now what? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Trigger?
Here's my latest attempt: localhost CREATE TRIGGER makefoo BEFORE INSERT ON testtrigger - FOR EACH ROW - SET NEW.foo = IFNULL(NEW.foo, 'ok') - | Query OK, 0 rows affected (0.00 sec) As you can see, the trigger syntax is correct; but it doesn't do what I want. localhost INSERT INTO testtrigger (id, foo) VALUES (NULL, 'xxx'); Query OK, 1 row affected (0.00 sec) localhost INSERT INTO testtrigger (id, foo) VALUES (NULL, NULL); ERROR 1048 (23000): Column 'foo' cannot be null localhost INSERT INTO testtrigger (id) VALUES (NULL); ERROR 1364 (HY000): Field 'foo' doesn't have a default value So I'm missing something important. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -Original Message- From: Michael Dykman [mailto:mdyk...@gmail.com] Sent: Monday, December 20, 2010 6:25 PM To: Jerry Schwartz Cc: mysql@lists.mysql.com Subject: Re: Trigger? The expression you supplied looks right enough.. how was it declared? as an on UPDATE/on INSERT trigger or just a single case? - michael dykman On Mon, Dec 20, 2010 at 5:21 PM, Jerry Schwartz je...@gii.co.jp wrote: I've never used a trigger before, and I want to make one that sounds like it should be simple. Create Table: CREATE TABLE `testtrigger` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `foo` char(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 Here's what I want to do: if no value is supplied for `foo`, or if a NULL value is supplied for `foo`, I want to set it to a particular value. I tried things like this: SET NEW.foo = IFNULL(NEW.foo,'ok') But that didn't work. If you point me in the right direction, I'll be okay from there (I hope). Thanks. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Trigger?
Aha! That was the clue I needed. Thank you so much. So, to make sure I understand: A “BEFORE” trigger is executed **between** the time that the record is assembled and the time that the action occurs. That’s why the constraints on the field value were being applied before my trigger was triggered. Contrariwise, I assume that an “AFTER” trigger would be executed last, after everything has been done. Am I correct? By the way, SET NEW.foo = IFNULL(NEW.foo, 'ok') works just fine. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com http://www.the-infoshop.com/ From: Wagner Bianchi [mailto:wagnerbianch...@gmail.com] Sent: Monday, December 20, 2010 6:44 PM To: Jerry Schwartz Cc: mysql@lists.mysql.com Subject: Re: Trigger? Well, to produce this result, the first thing that we have to do is to get rid of the NOT NULL constraint of the column `foo`. After it, the 'null' can be sent within a INSERT statement, as below: mysql show create table testtrigger\G *** 1. row *** Table: testtrigger Create Table: CREATE TABLE `testtrigger` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `foo` char(10) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 1 row in set (0.05 sec) so, after to create table, we create the trigger: mysql create trigger trg_test - before insert on testtrigger - for each row - begin - if(NEW.foo IS NULL || NEW.foo = '') then - set NEW.foo = 'Ok'; - end if; - end; - // Query OK, 0 rows affected (0.04 sec) mysql insert into testtrigger set id =100, foo =null; Query OK, 1 row affected (0.03 sec) mysql select * from testtrigger; +-+--+ | id | foo | +-+--+ | 100 | Ok | +-+--+ 1 row in set (0.00 sec) The way that your table is now, with foo NOT NULL, you can't send foo =null with a query cause column don't accept null values. The column was defined as a not null. Look this: mysql alter table testtrigger modify foo char(10) not null; Query OK, 1 row affected (0.10 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql insert into testtrigger set id =100, foo =null; ERROR 1048 (23000): Column 'foo' cannot be null Did you get? Best regards. -- Wagner Bianchi 2010/12/20 Jerry Schwartz je...@gii.co.jp I've never used a trigger before, and I want to make one that sounds like it should be simple. Create Table: CREATE TABLE `testtrigger` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `foo` char(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 Here's what I want to do: if no value is supplied for `foo`, or if a NULL value is supplied for `foo`, I want to set it to a particular value. I tried things like this: SET NEW.foo = IFNULL(NEW.foo,'ok') But that didn't work. If you point me in the right direction, I'll be okay from there (I hope). Thanks. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=wagnerbianch...@gmail.com
Re: Trigger?
I think if you built a trigger to update value of foo's column after, this trigger will not be compiled cause it will execute two transactions on the same one. Try it... Best regards. -- Wagner Bianchi 2010/12/21 Jerry Schwartz je...@gii.co.jp Aha! That was the clue I needed. Thank you so much. So, to make sure I understand: A “BEFORE” trigger is executed **between** the time that the record is assembled and the time that the action occurs. That’s why the constraints on the field value were being applied before my trigger was triggered. Contrariwise, I assume that an “AFTER” trigger would be executed last, after everything has been done. Am I correct? By the way, SET NEW.foo = IFNULL(NEW.foo, 'ok') works just fine. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com *From:* Wagner Bianchi [mailto:wagnerbianch...@gmail.com] *Sent:* Monday, December 20, 2010 6:44 PM *To:* Jerry Schwartz *Cc:* mysql@lists.mysql.com *Subject:* Re: Trigger? Well, to produce this result, the first thing that we have to do is to *get rid of* the NOT NULL constraint of the column `foo`. After it, the 'null' can be sent within a INSERT statement, as below: mysql show create table testtrigger\G *** 1. row *** Table: testtrigger Create Table: CREATE TABLE `testtrigger` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `foo` char(10) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 1 row in set (0.05 sec) so, after to create table, we create the trigger: mysql create trigger trg_test - before insert on testtrigger - for each row - begin - if(NEW.foo IS NULL || NEW.foo = '') then - set NEW.foo = 'Ok'; - end if; - end; - // Query OK, 0 rows affected (0.04 sec) mysql insert into testtrigger set id =100, foo =null; Query OK, 1 row affected (0.03 sec) mysql select * from testtrigger; +-+--+ | id | foo | +-+--+ | 100 | Ok | +-+--+ 1 row in set (0.00 sec) The way that your table is now, with foo NOT NULL, you can't send foo =null with a query cause column don't accept null values. The column was defined as a not null. Look this: mysql alter table testtrigger modify foo char(10) not null; Query OK, 1 row affected (0.10 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql insert into testtrigger set id =100, foo =null; ERROR 1048 (23000): Column 'foo' cannot be null Did you get? Best regards. -- Wagner Bianchi 2010/12/20 Jerry Schwartz je...@gii.co.jp I've never used a trigger before, and I want to make one that sounds like it should be simple. Create Table: CREATE TABLE `testtrigger` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `foo` char(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 Here's what I want to do: if no value is supplied for `foo`, or if a NULL value is supplied for `foo`, I want to set it to a particular value. I tried things like this: SET NEW.foo = IFNULL(NEW.foo,'ok') But that didn't work. If you point me in the right direction, I'll be okay from there (I hope). Thanks. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=wagnerbianch...@gmail.com
Trigger?
I've never used a trigger before, and I want to make one that sounds like it should be simple. Create Table: CREATE TABLE `testtrigger` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `foo` char(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 Here's what I want to do: if no value is supplied for `foo`, or if a NULL value is supplied for `foo`, I want to set it to a particular value. I tried things like this: SET NEW.foo = IFNULL(NEW.foo,'ok') But that didn't work. If you point me in the right direction, I'll be okay from there (I hope). Thanks. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Trigger?
The expression you supplied looks right enough.. how was it declared? as an on UPDATE/on INSERT trigger or just a single case? - michael dykman On Mon, Dec 20, 2010 at 5:21 PM, Jerry Schwartz je...@gii.co.jp wrote: I've never used a trigger before, and I want to make one that sounds like it should be simple. Create Table: CREATE TABLE `testtrigger` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `foo` char(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 Here's what I want to do: if no value is supplied for `foo`, or if a NULL value is supplied for `foo`, I want to set it to a particular value. I tried things like this: SET NEW.foo = IFNULL(NEW.foo,'ok') But that didn't work. If you point me in the right direction, I'll be okay from there (I hope). Thanks. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Trigger?
Well, to produce this result, the first thing that we have to do is to *get rid of* the NOT NULL constraint of the column `foo`. After it, the 'null' can be sent within a INSERT statement, as below: mysql show create table testtrigger\G *** 1. row *** Table: testtrigger Create Table: CREATE TABLE `testtrigger` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `foo` char(10) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 1 row in set (0.05 sec) so, after to create table, we create the trigger: mysql create trigger trg_test - before insert on testtrigger - for each row - begin - if(NEW.foo IS NULL || NEW.foo = '') then - set NEW.foo = 'Ok'; - end if; - end; - // Query OK, 0 rows affected (0.04 sec) mysql insert into testtrigger set id =100, foo =null; Query OK, 1 row affected (0.03 sec) mysql select * from testtrigger; +-+--+ | id | foo | +-+--+ | 100 | Ok | +-+--+ 1 row in set (0.00 sec) The way that your table is now, with foo NOT NULL, you can't send foo =null with a query cause column don't accept null values. The column was defined as a not null. Look this: mysql alter table testtrigger modify foo char(10) not null; Query OK, 1 row affected (0.10 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql insert into testtrigger set id =100, foo =null; ERROR 1048 (23000): Column 'foo' cannot be null Did you get? Best regards. -- Wagner Bianchi 2010/12/20 Jerry Schwartz je...@gii.co.jp I've never used a trigger before, and I want to make one that sounds like it should be simple. Create Table: CREATE TABLE `testtrigger` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `foo` char(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 Here's what I want to do: if no value is supplied for `foo`, or if a NULL value is supplied for `foo`, I want to set it to a particular value. I tried things like this: SET NEW.foo = IFNULL(NEW.foo,'ok') But that didn't work. If you point me in the right direction, I'll be okay from there (I hope). Thanks. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=wagnerbianch...@gmail.com
AFTER Delete Trigger question
Hi,Is there a way to update the same table on a delete trigger ?I need to call an UPDATE statement when a delete occurs.Best regards --Kevin Labecot,Innovanticwww.innovantic.frTél. : 05.56.45.60.54
Re: AFTER Delete Trigger question
As far as I know, you can´t change data on the same table in triggers. Kevin Labecot ke...@labecot.fr escreveu na mensagem news:4d2ce38b-d169-478b-aebf-c19f20dce...@labecot.fr... Hi, Is there a way to update the same table on a delete trigger ? I need to call an UPDATE statement when a delete occurs. Best regards -- Kevin Labecot, Innovantic www.innovantic.fr Tél. : 05.56.45.60.54
Re: ERROR 1442 (HY000) when delete inside trigger statement
That is correct. There is as far as I know no way in a MySQL trigger to neither to do operations on the table the trigger belongs to (obviously except the row that the trigger is operating on through the NEW variables) nor reject an insert, update, or delete. thanks jesper. ~viraj It is of course possible to do a workaround in a stored procedure and use permissions to ensure that the normal users cannot update the table directly. I don't know whether that will be an acceptable solution in your case though. Jesper -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=kali...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
ERROR 1442 (HY000) when delete inside trigger statement
i have two table, T1, T2. and 1 trigger. trigger is before update on T1 and it updates some values in T2. once it's done, the trigger tries to delete the subject row of T1 (delete from T1 where id = new.id) i tried with second trigger on T2 (after/before update) and with a procedure inside this trigger.. but, all the time i get.. issue: ERROR 1442 (HY000): Can't update table 'T1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. found http://forums.mysql.com/read.php?99,122354,122354#msg-122354 and many other articles which had ended up with no solution. (with before update, it's possible to set new values to NEW.*, but did not find anything useful to do a successful delete) could somebody please confirm this is not possible!!! so i can think about some other workaround :) thanks ~viraj -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: ERROR 1442 (HY000) when delete inside trigger statement
--- Original Message --- From: viraj kali...@gmail.com To: mysql@lists.mysql.com Sent: 19/2/10, 05:48:41 Subject: ERROR 1442 (HY000) when delete inside trigger statement issue: ERROR 1442 (HY000): Can't update table 'T1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. could somebody please confirm this is not possible!!! so i can think about some other workaround :) -- That is correct. There is as far as I know no way in a MySQL trigger to neither to do operations on the table the trigger belongs to (obviously except the row that the trigger is operating on through the NEW variables) nor reject an insert, update, or delete. It is of course possible to do a workaround in a stored procedure and use permissions to ensure that the normal users cannot update the table directly. I don't know whether that will be an acceptable solution in your case though. Jesper -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Error in running trigger
Hi, I have created the below trigger from root user with definer: CREATE definer=`roo...@`` TRIGGER `CONSUMER_PROFILE_before_delete` BEFORE DELETE ON CONSUMER_PROFILE FOR EACH ROW begin INSERT INTO DELETED_CONSUMER_PROFILE(CONSUMER_ID,CONSUMER_TYPE_ID,ENTERPRISE_URL_ID,CONSUMER_FIRST_NAME,CONSUMER_MIDDLE_INITIAL,CONSUMER_LAST_NAME, DATE_OF_BIRTH,GENDER,MARITAL_STATUS,MARRIED_TO,LOCALE_ID,PROFILE_PHOTO_PATH,STATUS,SKIP_STEP,LAST_MOD_TIME,LAST_MOD_BY ) VALUES(old.CONSUMER_ID,old.CONSUMER_TYPE_ID,old.ENTERPRISE_URL_ID,old.CONSUMER_FIRST_NAME,old.CONSUMER_MIDDLE_INITIAL, old.CONSUMER_LAST_NAME,old.DATE_OF_BIRTH,old.GENDER,old.MARITAL_STATUS,old.MARRIED_TO,old.LOCALE_ID,old.PROFILE_PHOTO_PATH, old.STATUS,old.SKIP_STEP,old.LAST_MOD_TIME,old.LAST_MOD_BY ); end; But when i see the trigger using show trigger command on mysql front it doesn't show and when i run the stament to hit the trigger it gives the error SQL execution erro # 1227. Response from the database: Access denied ; you need the SUPER privilege for this operation. But this user has all privilege. Why it is not executing ? Thanks in advance. Regards Jeetendra Ranjan
Re: Error in running trigger
is it root or root1 user. Also is this trigger executed by root user or some other user. On Tue, Dec 22, 2009 at 3:26 PM, Jeetendra Ranjan jeetendra.ran...@sampatti.com wrote: Hi, I have created the below trigger from root user with definer: CREATE definer=`roo...@`` TRIGGER `CONSUMER_PROFILE_before_delete` BEFORE DELETE ON CONSUMER_PROFILE FOR EACH ROW begin INSERT INTO DELETED_CONSUMER_PROFILE(CONSUMER_ID,CONSUMER_TYPE_ID,ENTERPRISE_URL_ID,CONSUMER_FIRST_NAME,CONSUMER_MIDDLE_INITIAL,CONSUMER_LAST_NAME, DATE_OF_BIRTH,GENDER,MARITAL_STATUS,MARRIED_TO,LOCALE_ID,PROFILE_PHOTO_PATH,STATUS,SKIP_STEP,LAST_MOD_TIME,LAST_MOD_BY ) VALUES(old.CONSUMER_ID,old.CONSUMER_TYPE_ID,old.ENTERPRISE_URL_ID,old.CONSUMER_FIRST_NAME,old.CONSUMER_MIDDLE_INITIAL, old.CONSUMER_LAST_NAME,old.DATE_OF_BIRTH,old.GENDER,old.MARITAL_STATUS,old.MARRIED_TO,old.LOCALE_ID,old.PROFILE_PHOTO_PATH, old.STATUS,old.SKIP_STEP,old.LAST_MOD_TIME,old.LAST_MOD_BY ); end; But when i see the trigger using show trigger command on mysql front it doesn't show and when i run the stament to hit the trigger it gives the error SQL execution erro # 1227. Response from the database: Access denied ; you need the SUPER privilege for this operation. But this user has all privilege. Why it is not executing ? Thanks in advance. Regards Jeetendra Ranjan
Re: Error in running trigger
Hi Ranjan, User doesn't have sufficient privileges. root access required. Thanks, Krishna On Tue, Dec 22, 2009 at 3:26 PM, Jeetendra Ranjan jeetendra.ran...@sampatti.com wrote: Hi, I have created the below trigger from root user with definer: CREATE definer=`*root...@`` TRIGGER `CONSUMER_PROFILE_before_delete` BEFORE DELETE ON CONSUMER_PROFILE FOR EACH ROW begin INSERT INTO DELETED_CONSUMER_PROFILE(CONSUMER_ID,CONSUMER_TYPE_ID,ENTERPRISE_URL_ID,CONSUMER_FIRST_NAME,CONSUMER_MIDDLE_INITIAL,CONSUMER_LAST_NAME, DATE_OF_BIRTH,GENDER,MARITAL_STATUS,MARRIED_TO,LOCALE_ID,PROFILE_PHOTO_PATH,STATUS,SKIP_STEP,LAST_MOD_TIME,LAST_MOD_BY ) VALUES(old.CONSUMER_ID,old.CONSUMER_TYPE_ID,old.ENTERPRISE_URL_ID,old.CONSUMER_FIRST_NAME,old.CONSUMER_MIDDLE_INITIAL, old.CONSUMER_LAST_NAME,old.DATE_OF_BIRTH,old.GENDER,old.MARITAL_STATUS,old.MARRIED_TO,old.LOCALE_ID,old.PROFILE_PHOTO_PATH, old.STATUS,old.SKIP_STEP,old.LAST_MOD_TIME,old.LAST_MOD_BY ); end; But when i see the trigger using show trigger command on mysql front it doesn't show and when i run the stament to hit the trigger it gives the error SQL execution erro # 1227. Response from the database: Access denied ; you need the SUPER privilege for this operation. But this user has all privilege. Why it is not executing ? Thanks in advance. Regards Jeetendra Ranjan
Re: Error in running trigger
please run this and lets us know the output show grants on root1; On Tue, Dec 22, 2009 at 5:50 PM, Krishna Chandra Prajapati prajapat...@gmail.com wrote: Hi Ranjan, User doesn't have sufficient privileges. root access required. Thanks, Krishna On Tue, Dec 22, 2009 at 3:26 PM, Jeetendra Ranjan jeetendra.ran...@sampatti.com wrote: Hi, I have created the below trigger from root user with definer: CREATE definer=`*root...@`` TRIGGER `CONSUMER_PROFILE_before_delete` BEFORE DELETE ON CONSUMER_PROFILE FOR EACH ROW begin INSERT INTO DELETED_CONSUMER_PROFILE(CONSUMER_ID,CONSUMER_TYPE_ID,ENTERPRISE_URL_ID,CONSUMER_FIRST_NAME,CONSUMER_MIDDLE_INITIAL,CONSUMER_LAST_NAME, DATE_OF_BIRTH,GENDER,MARITAL_STATUS,MARRIED_TO,LOCALE_ID,PROFILE_PHOTO_PATH,STATUS,SKIP_STEP,LAST_MOD_TIME,LAST_MOD_BY ) VALUES(old.CONSUMER_ID,old.CONSUMER_TYPE_ID,old.ENTERPRISE_URL_ID,old.CONSUMER_FIRST_NAME,old.CONSUMER_MIDDLE_INITIAL, old.CONSUMER_LAST_NAME,old.DATE_OF_BIRTH,old.GENDER,old.MARITAL_STATUS,old.MARRIED_TO,old.LOCALE_ID,old.PROFILE_PHOTO_PATH, old.STATUS,old.SKIP_STEP,old.LAST_MOD_TIME,old.LAST_MOD_BY ); end; But when i see the trigger using show trigger command on mysql front it doesn't show and when i run the stament to hit the trigger it gives the error SQL execution erro # 1227. Response from the database: Access denied ; you need the SUPER privilege for this operation. But this user has all privilege. Why it is not executing ? Thanks in advance. Regards Jeetendra Ranjan
error while creating trigger
Hi, I am getting error while creating a simple trigger mysql CREATE TRIGGER CONSUMER_PROFILE_before_delete BEFORE DELETE ON CONSUMER_PROFILE FOR EACH ROW - begin - INSERT INTO DELETED_CONSUMER_PROFILE(CONSUMER_ID,CONSUMER_TYPE_ID,ENTERPRISE_URL_ID,CONSUMER_FIRST_NAME,CONSUMER_MIDDLE_INITIAL,CONSUMER_LAST_NAME,DATE_OF_BIRTH,GENDER,MARITAL_STATUS,MARRIED_TO,LOCALE_ID,PROFILE_PHOTO_PATH,STATUS,SKIP_STEP,LAST_MOD_TIME,LAST_MOD_BY ) - VALUES(old.CONSUMER_ID,old.CONSUMER_TYPE_ID,old.ENTERPRISE_URL_ID,old.CONSUMER_FIRST_NAME,old.CONSUMER_MIDDLE_INITIAL,old.CONSUMER_LAST_NAME,old.DATE_OF_BIRTH,old.GENDER,old.MARITAL_STATUS,old.MARRIED_TO,old.LOCALE_ID,old.PROFILE_PHOTO_PATH,old.STATUS,old.SKIP_STEP,old.LAST_MOD_TIME,old.LAST_MOD_BY ); - INSERT INTO DELETED_CONSUMER_ACTION_AUDIT(CONSUMER_ID,CAMPAIGN_ID,CAMPAIGN_ACTION_ID,STATE_ID,LAST_MOD_TIME) VALUES(old.CONSUMER_ID,old.CAMPAIGN_ID,old.CAMPAIGN_ACTION_ID,old.STATE_ID,old.LAST_MOD_TIME); - END; - // ERROR 1054 (42S22): Unknown column 'CAMPAIGN_ID' in 'OLD' But this column is exist in DELETED_CONSUMER_ACTION_AUDIT table. What are the possible causes of this error ? Thanks in advance Jeetendra Ranjan
Re: error while creating trigger
Jeetendra, ERROR 1054 (42S22): Unknown column 'CAMPAIGN_ID' in 'OLD' But this column is exist in DELETED_CONSUMER_ACTION_AUDIT table. But does it exist in consumer_profile? PB - Jeetendra Ranjan wrote: Hi, I am getting error while creating a simple trigger mysql CREATE TRIGGER CONSUMER_PROFILE_before_delete BEFORE DELETE ON CONSUMER_PROFILE FOR EACH ROW - begin - INSERT INTO DELETED_CONSUMER_PROFILE(CONSUMER_ID,CONSUMER_TYPE_ID,ENTERPRISE_URL_ID,CONSUMER_FIRST_NAME,CONSUMER_MIDDLE_INITIAL,CONSUMER_LAST_NAME,DATE_OF_BIRTH,GENDER,MARITAL_STATUS,MARRIED_TO,LOCALE_ID,PROFILE_PHOTO_PATH,STATUS,SKIP_STEP,LAST_MOD_TIME,LAST_MOD_BY ) - VALUES(old.CONSUMER_ID,old.CONSUMER_TYPE_ID,old.ENTERPRISE_URL_ID,old.CONSUMER_FIRST_NAME,old.CONSUMER_MIDDLE_INITIAL,old.CONSUMER_LAST_NAME,old.DATE_OF_BIRTH,old.GENDER,old.MARITAL_STATUS,old.MARRIED_TO,old.LOCALE_ID,old.PROFILE_PHOTO_PATH,old.STATUS,old.SKIP_STEP,old.LAST_MOD_TIME,old.LAST_MOD_BY ); - INSERT INTO DELETED_CONSUMER_ACTION_AUDIT(CONSUMER_ID,CAMPAIGN_ID,CAMPAIGN_ACTION_ID,STATE_ID,LAST_MOD_TIME) VALUES(old.CONSUMER_ID,old.CAMPAIGN_ID,old.CAMPAIGN_ACTION_ID,old.STATE_ID,old.LAST_MOD_TIME); - END; - // ERROR 1054 (42S22): Unknown column 'CAMPAIGN_ID' in 'OLD' But this column is exist in DELETED_CONSUMER_ACTION_AUDIT table. What are the possible causes of this error ? Thanks in advance Jeetendra Ranjan No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.427 / Virus Database: 270.14.107/2564 - Release Date: 12/14/09 07:37:00
trigger
Hello, I am new to using triggers in mysql. I am using mysql 5.1.37 and would like to setup a trigger like: CREATE TRIGGER greylist AFTER INSERT on greylist delete from greylist where first_seen NOW()-60*60*24*5; END; When typing this into mysql I am getting an error. Where is my mistake? mysql show fields from greylist; +---+---+--+-+-+ | Field | Type | Null | Key | Default | +---+---+--+-+-+ | id| int(11) | NO | PRI | NULL| | SenderIP | varchar(15) | NO | MUL | NULL| | SenderAddress | varchar(1024) | NO | MUL | NULL| | first_seen| int(11) | NO | | NULL| +---+---+--+-+-+ 4 rows in set (0,00 sec) I would like to archive that after every insert in the greylist table I am purging the oldest xx records. Stefan www.stonki.de : My, myself and I www.kbarcode.net : barcode solution for KDE www.krename.net : renamer for KDE www.proftpd.de : a FTP server... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: trigger
Well you might need to parenthesize the expression, but note that simple integer math done on a DATE or DATETIME, the units are days. you probably want ... where first_seen (NOW() - 5); michael dykman On Wed, Nov 4, 2009 at 2:28 PM, Stefan Onken supp...@stonki.de wrote: Hello, I am new to using triggers in mysql. I am using mysql 5.1.37 and would like to setup a trigger like: CREATE TRIGGER greylist AFTER INSERT on greylist delete from greylist where first_seen NOW()-60*60*24*5; END; When typing this into mysql I am getting an error. Where is my mistake? mysql show fields from greylist; +---+---+--+-+-+ | Field | Type | Null | Key | Default | +---+---+--+-+-+ | id | int(11) | NO | PRI | NULL | | SenderIP | varchar(15) | NO | MUL | NULL | | SenderAddress | varchar(1024) | NO | MUL | NULL | | first_seen | int(11) | NO | | NULL | +---+---+--+-+-+ 4 rows in set (0,00 sec) I would like to archive that after every insert in the greylist table I am purging the oldest xx records. Stefan www.stonki.de : My, myself and I www.kbarcode.net : barcode solution for KDE www.krename.net : renamer for KDE www.proftpd.de : a FTP server... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com May you live every day of your life. Jonathan Swift -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: trigger
You are missing a BEGIN in the trigger delimiter | CREATE TRIGGER greylist AFTER INSERT on greylist BEGIN delete from greylist where first_seen NOW()-60*60*24*5; END; | delimiter ; Phil On Wed, Nov 4, 2009 at 2:28 PM, Stefan Onken supp...@stonki.de wrote: Hello, I am new to using triggers in mysql. I am using mysql 5.1.37 and would like to setup a trigger like: CREATE TRIGGER greylist AFTER INSERT on greylist delete from greylist where first_seen NOW()-60*60*24*5; END; When typing this into mysql I am getting an error. Where is my mistake? mysql show fields from greylist; +---+---+--+-+-+ | Field | Type | Null | Key | Default | +---+---+--+-+-+ | id| int(11) | NO | PRI | NULL| | SenderIP | varchar(15) | NO | MUL | NULL| | SenderAddress | varchar(1024) | NO | MUL | NULL| | first_seen| int(11) | NO | | NULL| +---+---+--+-+-+ 4 rows in set (0,00 sec) I would like to archive that after every insert in the greylist table I am purging the oldest xx records. Stefan www.stonki.de : My, myself and I www.kbarcode.net : barcode solution for KDE www.krename.net : renamer for KDE www.proftpd.de : a FTP server... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=pchap...@nc.rr.com -- Distributed Computing stats http://stats.free-dc.org
RE: trigger
1. Triggers must have FOR EACH ROW -- it's described in the manual: http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html So the correct syntax would be: CREATE TRIGGER greylist_ins AFTER INSERT on greylist FOR EACH ROW delete from greylist where first_seen NOW()-60*60*24*5; BEGIN/END and DELIMITER are not needed for single statement triggers 2. However you still can't do that. You can't update the table used in the trigger. What you really want is either a separate cron process, or a mysql event (if using 5.1) Regards Gavin Towey -Original Message- From: freedc@gmail.com [mailto:freedc@gmail.com] On Behalf Of Phil Sent: Wednesday, November 04, 2009 11:42 AM To: Mysql Subject: Re: trigger You are missing a BEGIN in the trigger delimiter | CREATE TRIGGER greylist AFTER INSERT on greylist BEGIN delete from greylist where first_seen NOW()-60*60*24*5; END; | delimiter ; Phil On Wed, Nov 4, 2009 at 2:28 PM, Stefan Onken supp...@stonki.de wrote: Hello, I am new to using triggers in mysql. I am using mysql 5.1.37 and would like to setup a trigger like: CREATE TRIGGER greylist AFTER INSERT on greylist delete from greylist where first_seen NOW()-60*60*24*5; END; When typing this into mysql I am getting an error. Where is my mistake? mysql show fields from greylist; +---+---+--+-+-+ | Field | Type | Null | Key | Default | +---+---+--+-+-+ | id| int(11) | NO | PRI | NULL| | SenderIP | varchar(15) | NO | MUL | NULL| | SenderAddress | varchar(1024) | NO | MUL | NULL| | first_seen| int(11) | NO | | NULL| +---+---+--+-+-+ 4 rows in set (0,00 sec) I would like to archive that after every insert in the greylist table I am purging the oldest xx records. Stefan www.stonki.de : My, myself and I www.kbarcode.net : barcode solution for KDE www.krename.net : renamer for KDE www.proftpd.de : a FTP server... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=pchap...@nc.rr.com -- Distributed Computing stats http://stats.free-dc.org The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: trigger
Oops, one more mistake: NOW()-60*60*24*5 isn't the way to do date math. It should be: NOW() - INTERVAL 5 DAY -Original Message- From: Gavin Towey Sent: Wednesday, November 04, 2009 2:33 PM To: 'Phil'; Mysql; 'Stefan Onken' Subject: RE: trigger 1. Triggers must have FOR EACH ROW -- it's described in the manual: http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html So the correct syntax would be: CREATE TRIGGER greylist_ins AFTER INSERT on greylist FOR EACH ROW delete from greylist where first_seen NOW()-60*60*24*5; BEGIN/END and DELIMITER are not needed for single statement triggers 2. However you still can't do that. You can't update the table used in the trigger. What you really want is either a separate cron process, or a mysql event (if using 5.1) Regards Gavin Towey -Original Message- From: freedc@gmail.com [mailto:freedc@gmail.com] On Behalf Of Phil Sent: Wednesday, November 04, 2009 11:42 AM To: Mysql Subject: Re: trigger You are missing a BEGIN in the trigger delimiter | CREATE TRIGGER greylist AFTER INSERT on greylist BEGIN delete from greylist where first_seen NOW()-60*60*24*5; END; | delimiter ; Phil On Wed, Nov 4, 2009 at 2:28 PM, Stefan Onken supp...@stonki.de wrote: Hello, I am new to using triggers in mysql. I am using mysql 5.1.37 and would like to setup a trigger like: CREATE TRIGGER greylist AFTER INSERT on greylist delete from greylist where first_seen NOW()-60*60*24*5; END; When typing this into mysql I am getting an error. Where is my mistake? mysql show fields from greylist; +---+---+--+-+-+ | Field | Type | Null | Key | Default | +---+---+--+-+-+ | id| int(11) | NO | PRI | NULL| | SenderIP | varchar(15) | NO | MUL | NULL| | SenderAddress | varchar(1024) | NO | MUL | NULL| | first_seen| int(11) | NO | | NULL| +---+---+--+-+-+ 4 rows in set (0,00 sec) I would like to archive that after every insert in the greylist table I am purging the oldest xx records. Stefan www.stonki.de : My, myself and I www.kbarcode.net : barcode solution for KDE www.krename.net : renamer for KDE www.proftpd.de : a FTP server... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=pchap...@nc.rr.com -- Distributed Computing stats http://stats.free-dc.org The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: trigger
ah, yes I'd missed the 'for each row' when I posted. But for the date math part, look at the column, it's an int() not a date. Puzzled me a little at the time so I tried it.. mysql select now()-60*60*24*5 from dual; +---+ | now()-60*60*24*5 | +---+ | 20091103730524.00 | +---+ 1 row in set (0.00 sec) Does give back a number at least rather than a date, so I assumed on the side of the poster that he was storing his value in last_seen as a number..(which remains to be seen :)) Not the way I would do it, but each to their own! Phil On Wed, Nov 4, 2009 at 5:40 PM, Gavin Towey gto...@ffn.com wrote: Oops, one more mistake: NOW()-60*60*24*5 isn't the way to do date math. It should be: NOW() - INTERVAL 5 DAY -Original Message- From: Gavin Towey Sent: Wednesday, November 04, 2009 2:33 PM To: 'Phil'; Mysql; 'Stefan Onken' Subject: RE: trigger 1. Triggers must have FOR EACH ROW -- it's described in the manual: http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html So the correct syntax would be: CREATE TRIGGER greylist_ins AFTER INSERT on greylist FOR EACH ROW delete from greylist where first_seen NOW()-60*60*24*5; BEGIN/END and DELIMITER are not needed for single statement triggers 2. However you still can't do that. You can't update the table used in the trigger. What you really want is either a separate cron process, or a mysql event (if using 5.1) Regards Gavin Towey -Original Message- From: freedc@gmail.com [mailto:freedc@gmail.com] On Behalf Of Phil Sent: Wednesday, November 04, 2009 11:42 AM To: Mysql Subject: Re: trigger You are missing a BEGIN in the trigger delimiter | CREATE TRIGGER greylist AFTER INSERT on greylist BEGIN delete from greylist where first_seen NOW()-60*60*24*5; END; | delimiter ; Phil On Wed, Nov 4, 2009 at 2:28 PM, Stefan Onken supp...@stonki.de wrote: Hello, I am new to using triggers in mysql. I am using mysql 5.1.37 and would like to setup a trigger like: CREATE TRIGGER greylist AFTER INSERT on greylist delete from greylist where first_seen NOW()-60*60*24*5; END; When typing this into mysql I am getting an error. Where is my mistake? mysql show fields from greylist; +---+---+--+-+-+ | Field | Type | Null | Key | Default | +---+---+--+-+-+ | id| int(11) | NO | PRI | NULL| | SenderIP | varchar(15) | NO | MUL | NULL| | SenderAddress | varchar(1024) | NO | MUL | NULL| | first_seen| int(11) | NO | | NULL| +---+---+--+-+-+ 4 rows in set (0,00 sec) I would like to archive that after every insert in the greylist table I am purging the oldest xx records. Stefan www.stonki.de : My, myself and I www.kbarcode.net : barcode solution for KDE www.krename.net : renamer for KDE www.proftpd.de : a FTP server... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=pchap...@nc.rr.com -- Distributed Computing stats http://stats.free-dc.org The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=pchap...@nc.rr.com -- Distributed Computing stats http://stats.free-dc.org
Error - Select Column Not Found Within Trigger
This is the first time I have attempted to expand beyond basic sql for storing data in a table like structure. So my level of knowledge is familiar with basic SQL, but lacking in expanded knowledge. I expect my error is a newbie mistake. I have attempted to create a trigger to update another table with the calculated balance of two columns in two different tables. Monetary amounts are stored in integer form so $6.59 would be represented as 659. The goal here is to move more of the funtionality into SQL where the data can manage itself and away from application code. The following trigger inserts fine, however upon adding a row to the trigger-enabled table it recieves an error. The row inserts fine as can be proved by selecting the table afterwards, but the trigger operation did not complete. * The following is my trigger:* create trigger billing.update_account_balance after insert on billing.debits for each row begin declare total_debits int; declare total_credits int; declare total_balance int; select SUM(debits.amount) into total_debits from debits where debits.enabled=1 and account=new.account; select SUM(credits.amount) into total_credits from credits where credits.enabled=1 and account=new.account; set total_balance = total_credits - total_debits; update accounts set accounts.balance = total_balance where number=new.account; end *The following is the error as described by PHPMyAdmin:* *SQL query:* INSERT INTO `billing`.`debits` ( `account` , `date` , `line_number` , `amount` , `product_code` , `rep_id` , `description` , `enabled` ) VALUES ( '1', '2009-10-04 13:05:14', NULL , '91', '3', '1', 'Service / Support', '1' ) *MySQL said:* #1054 - Unknown column 'credits.enabled' in 'where clause' *The following is my database structure exported by PHPMyAdmin:* CREATE TABLE `accounts` ( `number` int(10) unsigned NOT NULL auto_increment, `balance` int(11) NOT NULL default '0', PRIMARY KEY (`number`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=2 ; CREATE TABLE `credits` ( `account` int(11) NOT NULL, `date` timestamp NOT NULL default CURRENT_TIMESTAMP, `line_number` int(10) unsigned NOT NULL auto_increment, `amount` int(11) NOT NULL default '0', `product_code` int(11) NOT NULL default '0', `rep_id` int(11) NOT NULL default '0', `description` text collate utf8_bin NOT NULL, PRIMARY KEY (`line_number`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ; CREATE TABLE `debits` ( `account` int(11) NOT NULL, `date` timestamp NOT NULL default CURRENT_TIMESTAMP, `line_number` int(10) unsigned NOT NULL auto_increment, `amount` int(11) NOT NULL default '0', `product_code` int(11) NOT NULL default '0', `rep_id` int(11) NOT NULL default '0', `description` text collate utf8_bin NOT NULL, `enabled` tinyint(1) NOT NULL default '1', PRIMARY KEY (`line_number`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=9 ; DROP TRIGGER IF EXISTS `billing`.`update_account_balance`; DELIMITER // CREATE TRIGGER `billing`.`update_account_balance` AFTER INSERT ON `billing`.`debits` FOR EACH ROW begin declare total_debits int; declare total_credits int; declare total_balance int; select SUM(debits.amount) into total_debits from debits where debits.enabled=1 and account=new.account; select SUM(credits.amount) into total_credits from credits where credits.enabled=1 and account=new.account; set total_balance = total_credits - total_debits; update accounts set accounts.balance = total_balance where number=new.account; end // DELIMITER ; CREATE TABLE `products` ( `id` int(11) NOT NULL auto_increment, `description` text collate utf8_bin NOT NULL, `daily_charge` int(11) NOT NULL default '0', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=4 ; Thank you in advance. Dan
Re: Error - Select Column Not Found Within Trigger
It appears to be a simple enough error message. Here is your trigger you are reffering quite explicitly to credits.enabled: select SUM(credits.amount) into total_credits from credits where credits.enabled=1 and account=new.account; and this table has no such column defined. debits does, but not this one CREATE TABLE `credits` ( `account` int(11) NOT NULL, `date` timestamp NOT NULL default CURRENT_TIMESTAMP, `line_number` int(10) unsigned NOT NULL auto_increment, `amount` int(11) NOT NULL default '0', `product_code` int(11) NOT NULL default '0', `rep_id` int(11) NOT NULL default '0', `description` text collate utf8_bin NOT NULL, PRIMARY KEY (`line_number`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ; - michael dykman On Sat, Oct 17, 2009 at 2:20 PM, Dan Saul daniel.s...@gmail.com wrote: This is the first time I have attempted to expand beyond basic sql for storing data in a table like structure. So my level of knowledge is familiar with basic SQL, but lacking in expanded knowledge. I expect my error is a newbie mistake. I have attempted to create a trigger to update another table with the calculated balance of two columns in two different tables. Monetary amounts are stored in integer form so $6.59 would be represented as 659. The goal here is to move more of the funtionality into SQL where the data can manage itself and away from application code. The following trigger inserts fine, however upon adding a row to the trigger-enabled table it recieves an error. The row inserts fine as can be proved by selecting the table afterwards, but the trigger operation did not complete. * The following is my trigger:* create trigger billing.update_account_balance after insert on billing.debits for each row begin declare total_debits int; declare total_credits int; declare total_balance int; select SUM(debits.amount) into total_debits from debits where debits.enabled=1 and account=new.account; select SUM(credits.amount) into total_credits from credits where credits.enabled=1 and account=new.account; set total_balance = total_credits - total_debits; update accounts set accounts.balance = total_balance where number=new.account; end *The following is the error as described by PHPMyAdmin:* *SQL query:* INSERT INTO `billing`.`debits` ( `account` , `date` , `line_number` , `amount` , `product_code` , `rep_id` , `description` , `enabled` ) VALUES ( '1', '2009-10-04 13:05:14', NULL , '91', '3', '1', 'Service / Support', '1' ) *MySQL said:* #1054 - Unknown column 'credits.enabled' in 'where clause' *The following is my database structure exported by PHPMyAdmin:* CREATE TABLE `accounts` ( `number` int(10) unsigned NOT NULL auto_increment, `balance` int(11) NOT NULL default '0', PRIMARY KEY (`number`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=2 ; CREATE TABLE `credits` ( `account` int(11) NOT NULL, `date` timestamp NOT NULL default CURRENT_TIMESTAMP, `line_number` int(10) unsigned NOT NULL auto_increment, `amount` int(11) NOT NULL default '0', `product_code` int(11) NOT NULL default '0', `rep_id` int(11) NOT NULL default '0', `description` text collate utf8_bin NOT NULL, PRIMARY KEY (`line_number`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ; CREATE TABLE `debits` ( `account` int(11) NOT NULL, `date` timestamp NOT NULL default CURRENT_TIMESTAMP, `line_number` int(10) unsigned NOT NULL auto_increment, `amount` int(11) NOT NULL default '0', `product_code` int(11) NOT NULL default '0', `rep_id` int(11) NOT NULL default '0', `description` text collate utf8_bin NOT NULL, `enabled` tinyint(1) NOT NULL default '1', PRIMARY KEY (`line_number`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=9 ; DROP TRIGGER IF EXISTS `billing`.`update_account_balance`; DELIMITER // CREATE TRIGGER `billing`.`update_account_balance` AFTER INSERT ON `billing`.`debits` FOR EACH ROW begin declare total_debits int; declare total_credits int; declare total_balance int; select SUM(debits.amount) into total_debits from debits where debits.enabled=1 and account=new.account; select SUM(credits.amount) into total_credits from credits where credits.enabled=1 and account=new.account; set total_balance = total_credits - total_debits; update accounts set accounts.balance = total_balance where number=new.account; end // DELIMITER ; CREATE TABLE `products` ( `id` int(11) NOT NULL auto_increment, `description` text collate utf8_bin NOT NULL, `daily_charge` int(11) NOT NULL default '0', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=4 ; Thank you in advance. Dan -- - michael dykman - mdyk...@gmail.com May you live every day of your life. Jonathan Swift -- - michael dykman - mdyk...@gmail.com May you live every
Re: Error - Select Column Not Found Within Trigger
Well that is embarasing, thank you, It always ends up being the simple things that get you. On Sat, Oct 17, 2009 at 1:25 PM, Michael Dykman mdyk...@gmail.com wrote: It appears to be a simple enough error message. Here is your trigger you are reffering quite explicitly to credits.enabled: select SUM(credits.amount) into total_credits from credits where credits.enabled=1 and account=new.account; and this table has no such column defined. debits does, but not this one CREATE TABLE `credits` ( `account` int(11) NOT NULL, `date` timestamp NOT NULL default CURRENT_TIMESTAMP, `line_number` int(10) unsigned NOT NULL auto_increment, `amount` int(11) NOT NULL default '0', `product_code` int(11) NOT NULL default '0', `rep_id` int(11) NOT NULL default '0', `description` text collate utf8_bin NOT NULL, PRIMARY KEY (`line_number`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ; - michael dykman On Sat, Oct 17, 2009 at 2:20 PM, Dan Saul daniel.s...@gmail.com wrote: This is the first time I have attempted to expand beyond basic sql for storing data in a table like structure. So my level of knowledge is familiar with basic SQL, but lacking in expanded knowledge. I expect my error is a newbie mistake. I have attempted to create a trigger to update another table with the calculated balance of two columns in two different tables. Monetary amounts are stored in integer form so $6.59 would be represented as 659. The goal here is to move more of the funtionality into SQL where the data can manage itself and away from application code. The following trigger inserts fine, however upon adding a row to the trigger-enabled table it recieves an error. The row inserts fine as can be proved by selecting the table afterwards, but the trigger operation did not complete. * The following is my trigger:* create trigger billing.update_account_balance after insert on billing.debits for each row begin declare total_debits int; declare total_credits int; declare total_balance int; select SUM(debits.amount) into total_debits from debits where debits.enabled=1 and account=new.account; select SUM(credits.amount) into total_credits from credits where credits.enabled=1 and account=new.account; set total_balance = total_credits - total_debits; update accounts set accounts.balance = total_balance where number=new.account; end *The following is the error as described by PHPMyAdmin:* *SQL query:* INSERT INTO `billing`.`debits` ( `account` , `date` , `line_number` , `amount` , `product_code` , `rep_id` , `description` , `enabled` ) VALUES ( '1', '2009-10-04 13:05:14', NULL , '91', '3', '1', 'Service / Support', '1' ) *MySQL said:* #1054 - Unknown column 'credits.enabled' in 'where clause' *The following is my database structure exported by PHPMyAdmin:* CREATE TABLE `accounts` ( `number` int(10) unsigned NOT NULL auto_increment, `balance` int(11) NOT NULL default '0', PRIMARY KEY (`number`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=2 ; CREATE TABLE `credits` ( `account` int(11) NOT NULL, `date` timestamp NOT NULL default CURRENT_TIMESTAMP, `line_number` int(10) unsigned NOT NULL auto_increment, `amount` int(11) NOT NULL default '0', `product_code` int(11) NOT NULL default '0', `rep_id` int(11) NOT NULL default '0', `description` text collate utf8_bin NOT NULL, PRIMARY KEY (`line_number`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ; CREATE TABLE `debits` ( `account` int(11) NOT NULL, `date` timestamp NOT NULL default CURRENT_TIMESTAMP, `line_number` int(10) unsigned NOT NULL auto_increment, `amount` int(11) NOT NULL default '0', `product_code` int(11) NOT NULL default '0', `rep_id` int(11) NOT NULL default '0', `description` text collate utf8_bin NOT NULL, `enabled` tinyint(1) NOT NULL default '1', PRIMARY KEY (`line_number`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=9 ; DROP TRIGGER IF EXISTS `billing`.`update_account_balance`; DELIMITER // CREATE TRIGGER `billing`.`update_account_balance` AFTER INSERT ON `billing`.`debits` FOR EACH ROW begin declare total_debits int; declare total_credits int; declare total_balance int; select SUM(debits.amount) into total_debits from debits where debits.enabled=1 and account=new.account; select SUM(credits.amount) into total_credits from credits where credits.enabled=1 and account=new.account; set total_balance = total_credits - total_debits; update accounts set accounts.balance = total_balance where number=new.account; end // DELIMITER ; CREATE TABLE `products` ( `id` int(11) NOT NULL auto_increment, `description` text collate utf8_bin NOT NULL, `daily_charge` int(11) NOT NULL default
Another Trigger Question!
Good Day once again.
Im needing assistance again with triggers!
Create Trigger hostchange
BEFORE UPDATE ON host
FOR EACH ROW
BEGIN
IF NEW.status != OLD.status THEN
use smsgw;
insert into outbox (number, insertdate, text, phone, dreport) VALUES
('0836075144', '', 'HostDown', '1', '1'));
END IF;
END;
I have my idea of a trigger above that for obvious reasons wont work ,
however describes what im trying to do best!
Im monitoring a value status and if it changes id like to insert a record
into a entirely separate table in a separate DB.
Any ideas on how syntax should be?
Also anyone know of any good ebooks that describe triggers in depth? I find
most info I find on web is simplistic at best or simply don't explain well
anough for my level!
Regards
Re: Another Trigger Question!
In the last episode (Jul 09), Marcel Grandemange said:
Create Trigger hostchange
BEFORE UPDATE ON host
FOR EACH ROW
BEGIN
IF NEW.status != OLD.status THEN
use smsgw;
insert into outbox (number, insertdate, text, phone, dreport) VALUES
('0836075144', '', 'HostDown', '1', '1'));
END IF;
END;
I have my idea of a trigger above that for obvious reasons wont work ,
however describes what im trying to do best!
Im monitoring a value status and if it changes id like to insert a record
into a entirely separate table in a separate DB.
insert into smsgw.outbox [...]
--
Dan Nelson
dnel...@allantgroup.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Trigger and Warning: #1265 Data truncated
Curdate() returns a datetime, which you are pushing into a date field. It is truncating the time part. Just truncate it and you should be fine :) Hmmm. Not according to http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_curdate or, indeed, experiments: mysql select curdate(); ++ | curdate() | ++ | 2009-06-09 | ++ 1 row in set (0.02 sec) So have I misunderstood your answer, or is there some other reason for the error I get? Thanks, Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Trigger and Warning: #1265 Data truncated
I'm very new to triggers, so I suspect I've done something naive. When a row is inserted into a table, I want populate a 'date' column with the date 45 days hence. I've created a trigger: CREATE TRIGGER `test`.`setExpiryDate` BEFORE INSERT ON `test`.`mysql_auth` FOR EACH ROW SET NEW.expires = curdate()+interval 45 day It works, doing what I want, but I get the following error: Warning: #1265 Data truncated for column 'expires' at row 1 The table is: CREATE TABLE `mysql_auth` ( `id` int(11) NOT NULL auto_increment, `user` varchar(25) NOT NULL, `password` varchar(32) NOT NULL, `expires` date NOT NULL, `login_failures` int(11) NOT NULL default '0', `last_login_failure` datetime default NULL, PRIMARY KEY (`id`) ) Can someone help me understand what it happening here, and how to fix it? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Trigger and Warning: #1265 Data truncated
Curdate() returns a datetime, which you are pushing into a date field. It is truncating the time part. Just truncate it and you should be fine :) Walter On Fri, Jun 5, 2009 at 5:56 AM, Keith Edmundsk...@midnighthax.com wrote: I'm very new to triggers, so I suspect I've done something naive. When a row is inserted into a table, I want populate a 'date' column with the date 45 days hence. I've created a trigger: CREATE TRIGGER `test`.`setExpiryDate` BEFORE INSERT ON `test`.`mysql_auth` FOR EACH ROW SET NEW.expires = curdate()+interval 45 day It works, doing what I want, but I get the following error: Warning: #1265 Data truncated for column 'expires' at row 1 The table is: CREATE TABLE `mysql_auth` ( `id` int(11) NOT NULL auto_increment, `user` varchar(25) NOT NULL, `password` varchar(32) NOT NULL, `expires` date NOT NULL, `login_failures` int(11) NOT NULL default '0', `last_login_failure` datetime default NULL, PRIMARY KEY (`id`) ) Can someone help me understand what it happening here, and how to fix it? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=li...@olindata.com -- Walter Heck, Engineer @ Open Query (http://openquery.com) Affordable Training and ProActive Support for MySQL related technologies Follow our blog at http://openquery.com/blog/ OurDelta: free enhanced builds for MySQL @ http://ourdelta.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Trigger working with server 5.0.51 but not 5.0.22
Hi, I'm developed a trigger with mysql version 5.0.51(ubuntu), and when I tried to move this to a production server (version 5.0.22 fedora) I ran into 2 problems: 1. I try to install the trigger from a text file, and the first lines were: DELIMITER $$ DROP TRIGGER IF EXISTS cfe_tg_calcular_consumos; CREATE TRIGGER cfe_tg_calcular_consumos AFTER INSERT ON cfe_lecturas_tiendas but mysql say there is an error near EXISTS cfe_tg_calculas_consumos. Of course I use this text file at my development environment without problem. And at production server I need to delete the line DROP TRIGGER ... to create the trigger. 2. This trigger create a temporary table and then call a stored procedure wich does some arithmetic and put the result in the temporary table. When the trigger get fired at the production server, I got ***SQL Error:* 1146: Table 'filasPOS.tmp_calculos_res' doesn't exist Is there a big change in handling triggers from version 5.0.22 to 5.0.51? Or I missing something? Thanks in advance -- Mauricio Tellez
Re: Trigger working with server 5.0.51 but not 5.0.22
2009/5/13 Mauricio Tellez mauricio.tel...@gmail.com: Hi, I'm developed a trigger with mysql version 5.0.51(ubuntu), and when I tried to move this to a production server (version 5.0.22 fedora) I ran into [...] but mysql say there is an error near EXISTS cfe_tg_calculas_consumos. Of http://dev.mysql.com/doc/refman/5.0/en/drop-trigger.html The IF EXISTS clause was added in MySQL 5.0.32. Greetings, mattia. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Trigger working with server 5.0.51 but not 5.0.22
The DROP TRIGGER command is in the wrong place. You changed the delimiter to $$ but still tried to use the semicolon(;) with DROP TRIGGER. Your code should read like this: DROP TRIGGER IF EXISTS cfe_tg_calcular_consumos; DELIMITER $$ CREATE TRIGGER cfe_tg_calcular_consumos AFTER INSERT ON cfe_lecturas_tiendas Or change the ; to $$ on the DROP TRIGGER command like this: DELIMITER $$ DROP TRIGGER IF EXISTS cfe_tg_calcular_consumos $$ CREATE TRIGGER cfe_tg_calcular_consumos AFTER INSERT ON cfe_lecturas_tiendas Give it a try !!! Rolando A. Edwards MySQL DBA (CMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM : RolandoLogicWorx Skype : RolandoLogicWorx redwa...@logicworks.net -Original Message- From: Mauricio Tellez [mailto:mauricio.tel...@gmail.com] Sent: Wednesday, May 13, 2009 4:35 AM To: mysql@lists.mysql.com Subject: Trigger working with server 5.0.51 but not 5.0.22 Hi, I'm developed a trigger with mysql version 5.0.51(ubuntu), and when I tried to move this to a production server (version 5.0.22 fedora) I ran into 2 problems: 1. I try to install the trigger from a text file, and the first lines were: DELIMITER $$ DROP TRIGGER IF EXISTS cfe_tg_calcular_consumos; CREATE TRIGGER cfe_tg_calcular_consumos AFTER INSERT ON cfe_lecturas_tiendas but mysql say there is an error near EXISTS cfe_tg_calculas_consumos. Of course I use this text file at my development environment without problem. And at production server I need to delete the line DROP TRIGGER ... to create the trigger. 2. This trigger create a temporary table and then call a stored procedure wich does some arithmetic and put the result in the temporary table. When the trigger get fired at the production server, I got ***SQL Error:* 1146: Table 'filasPOS.tmp_calculos_res' doesn't exist Is there a big change in handling triggers from version 5.0.22 to 5.0.51? Or I missing something? Thanks in advance -- Mauricio Tellez -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Trigger causes error in binlog
Hi all, I am having some issues with my replication setup which I have narrowed down to being a problem on the master, more specifically an issue with the binlog when using a trigger. I have a trigger on one of my tables that archives an entry before deleting it: DELIMITER // CREATE TRIGGER d_adi_95_long BEFORE DELETE ON adi_95_long FOR EACH ROW BEGIN INSERT INTO D_adi_95_long select *,NULL,NOW() from adi_95_long WHERE adi_id=OLD.adi_id; END; // DELIMITER ; The table D_adi_95_long is identical to adi_95_long except the last 2 columns, one being a auto_increment field, the other a timestamp. When I delete from adi_95_long, the trigger is executed but I get this line in my binlog (which I assume creates the problem on the slaves): ERROR: Error in Log_event::read_log_event(): 'read error', data_len: 173056, event_type: 73 I have the exact same scenario for various other tables and have no issues there, this is by far the table with the most columns (209) however. When I started researching this by googling the errors I was getting in the slaves error log: 081230 12:20:25 [ERROR] Error reading packet from server: error reading log entry ( server_errno=1236) 081230 12:20:25 [ERROR] Got fatal error 1236: 'error reading log entry' from master when reading data from binary log I found a post suggesting this was a max_allowed_packet (currently 256M) issue on the master, which I could not confirm. It seems possible though as I am having this issues with the largest of tables only. What is the unit for data_len: 173056 in above binlog entry? Any other ideas are appreciated as well as I am not sure what to do here. Thanks Olaf - Confidentiality Notice: The following mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. The recipient is responsible to maintain the confidentiality of this information and to use the information only for authorized purposes. If you are not the intended recipient (or authorized to receive information for the intended recipient), you are hereby notified that any review, use, disclosure, distribution, copying, printing, or action taken in reliance on the contents of this e-mail is strictly prohibited. If you have received this communication in error, please notify us immediately by reply e-mail and destroy all copies of the original message. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Trying to Create a Trigger
Going back to the OP's problem - the original issue I believe was he was using old instead of OLD (case-sensitive) - now that's sorted, MySQL is complaining about a syntax error toward the end of the function declaration. I'm surprised by the case sensitivity of OLD though, it works fine on my Windows system. If this is really it... then ugh... Case sensitivity on object names has to be one of the most retarted things ever... With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database questions? Check the forum: http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Trying to Create a Trigger
I'm trying to create a trigger (5.0.45) and I've read the documentation at mysql.com. I keep getting a syntax error, but can't figure out what the error is. Here's the trigger I'm trying to create: delimiter // create trigger jobposts_control before delete on jobposts for each row begin declare dummy varchar(255); set @counted = ( select count(ad.adsource_id) from adsource ad, jobposts jp where ad.adsource_id = jp.adsource_id and old.jobpost_id = jp.jobpost_id ); if @counted = 1 then SET dummy = Cannot delete this record end if; end // delimiter ; When I run the set query without the and old.jobpost_id line, it runs correctly. So the syntax problem is elsewhere, but where? -- Lola J. Lee Beno - ColdFusion Programmer/Web Designer for Hire http://www.lolajl.net/resume | Blog at http://www.lolajl.net/blog/ In rivers, the water that you touch is the last of what has passed and the first of that which comes; so with present time. - Leonardo da Vinci (1452-1519) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to Create a Trigger
On 12/5/08, Lola J. Lee Beno [EMAIL PROTECTED] wrote: I'm trying to create a trigger (5.0.45) and I've read the documentation at mysql.com. I keep getting a syntax error, but can't figure out what the error is. Here's the trigger I'm trying to create: delimiter // create trigger jobposts_control before delete on jobposts for each row begin declare dummy varchar(255); set @counted = ( select count(ad.adsource_id) from adsource ad, jobposts jp where ad.adsource_id = jp.adsource_id and old.jobpost_id = jp.jobpost_id ); if @counted = 1 then SET dummy = Cannot delete this record end if; end // delimiter ; When I run the set query without the and old.jobpost_id line, it runs correctly. So the syntax problem is elsewhere, but where? I'm no expert, but 'old' is a table, I'm guessing, and it isn't referenced in the 'from' clause of the query. could it be that simple...? David
Re: Trying to Create a Trigger
David Giragosian wrote: I'm no expert, but 'old' is a table, I'm guessing, and it isn't referenced in the 'from' clause of the query. could it be that simple...? David No . . . 'old' is a virtual table that is the same as the table I'm doing work on. See http://dev.mysql.com/doc/refman/5.0/en/trigger-syntax.html. What I need to do is check if jobposts record has jobposts.adsource_id (foreign key) which still exists in adsource table (primary key). If adsource record still exists, then do not proceed with deleting jobpost record - that's what I'm trying to do and thus is what this trigger is supposed to do. -- Lola J. Lee Beno - ColdFusion Programmer/Web Designer for Hire http://www.lolajl.net/resume | Blog at http://www.lolajl.net/blog/ In rivers, the water that you touch is the last of what has passed and the first of that which comes; so with present time. - Leonardo da Vinci (1452-1519) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to Create a Trigger
I'm trying to create a trigger (5.0.45) and I've read the documentation at mysql.com. I keep getting a syntax error, but can't figure out what the error is. Here's the trigger I'm trying to create: delimiter // create trigger jobposts_control before delete on jobposts for each row begin declare dummy varchar(255); set @counted = ( select count(ad.adsource_id) from adsource ad, jobposts jp where ad.adsource_id = jp.adsource_id and old.jobpost_id = jp.jobpost_id ); if @counted = 1 then SET dummy = Cannot delete this record end if; end // delimiter ; When I run the set query without the and old.jobpost_id line, it runs correctly. So the syntax problem is elsewhere, but where? What is the exact error message? With regards, Martijn Tonies Upscene Productions http://www.upscene.com Free Database Workbench Lite for MySQL at www.upscene.com ! Database questions? Check the forum: http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to Create a Trigger
can you not use referential integrity for this - assuming the tables are or can be made to be innodb? Does the jobposts table have a jobpost_id field, or is it just id? Maybe it's a typo? On Fri, Dec 5, 2008 at 11:28 AM, Lola J. Lee Beno [EMAIL PROTECTED] wrote: David Giragosian wrote: I'm no expert, but 'old' is a table, I'm guessing, and it isn't referenced in the 'from' clause of the query. could it be that simple...? David No . . . 'old' is a virtual table that is the same as the table I'm doing work on. See http://dev.mysql.com/doc/refman/5.0/en/trigger-syntax.html. What I need to do is check if jobposts record has jobposts.adsource_id (foreign key) which still exists in adsource table (primary key). If adsource record still exists, then do not proceed with deleting jobpost record - that's what I'm trying to do and thus is what this trigger is supposed to do. -- Lola J. Lee Beno - ColdFusion Programmer/Web Designer for Hire http://www.lolajl.net/resume | Blog at http://www.lolajl.net/blog/ In rivers, the water that you touch is the last of what has passed and the first of that which comes; so with present time. - Leonardo da Vinci (1452-1519) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: Trying to Create a Trigger
Jim Lyons wrote: can you not use referential integrity for this - assuming the tables are or can be made to be innodb? The tables are myISAM. These could be changed to innodb but I want to see if i can get this trigger work. Does the jobposts table have a jobpost_id field, or is it just id? Maybe Yes. Jobpost_id. No, not a typo. It's primary key, jobposts.adsource_id is foreign key while adsource.adsource_id is primary key. -- Lola J. Lee Beno - ColdFusion Programmer/Web Designer for Hire http://www.lolajl.net/resume | Blog at http://www.lolajl.net/blog/ In rivers, the water that you touch is the last of what has passed and the first of that which comes; so with present time. - Leonardo da Vinci (1452-1519) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to Create a Trigger
Martijn Tonies wrote: What is the exact error message? Here's the latest query: delimiter // create trigger jobposts_control before delete on jobposts for each row begin declare dummy varchar(255); set @counted = ( select count(ad.adsource_id) from adsource ad, jobposts jp where ad.adsource_id = jp.adsource_id and old.jobpost_id = jp.jobpost_id ); if @counted = 1 then SET dummy = Cannot delete this record; end if; end // delimiter ; Here's the error message: ERROR: Unknown column 'old.jobpost_id' in 'where clause' Query = set @counted = ( select count(ad.adsource_id) from adsource ad, jobposts jp where ad.adsource_id = jp.adsource_id and old.jobpost_id = jp.jobpost_id ) It looks like I can't do what I want to do . . . get the jobpost_id to check jobpost and adsource tables BEFORE proceeding to delete the record. If so, what's the best way to do this? -- Lola J. Lee Beno - ColdFusion Programmer/Web Designer for Hire http://www.lolajl.net/resume | Blog at http://www.lolajl.net/blog/ In rivers, the water that you touch is the last of what has passed and the first of that which comes; so with present time. - Leonardo da Vinci (1452-1519) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to Create a Trigger
Hi, What is the exact error message? Here's the latest query: delimiter // create trigger jobposts_control before delete on jobposts for each row begin declare dummy varchar(255); set @counted = ( select count(ad.adsource_id) from adsource ad, jobposts jp where ad.adsource_id = jp.adsource_id and old.jobpost_id = jp.jobpost_id ); if @counted = 1 then SET dummy = Cannot delete this record; end if; end // delimiter ; Here's the error message: ERROR: Unknown column 'old.jobpost_id' in 'where clause' Works fine here (although with different tables). Just for fun and giggles, have you tried OLD. (uppercase?) As a complete sidenote: It's better to write your JOINs with a JOIN clause and to put your strings inside single quotes as per SQL standard, double quotes are really for object names. With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database questions? Check the forum: http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to Create a Trigger
Martijn Tonies wrote: Hi, What is the exact error message? Here's the latest query: delimiter // create trigger jobposts_control before delete on jobposts for each row begin declare dummy varchar(255); set @counted = ( select count(ad.adsource_id) from adsource ad, jobposts jp where ad.adsource_id = jp.adsource_id and old.jobpost_id = jp.jobpost_id ); if @counted = 1 then SET dummy = Cannot delete this record; end if; end // delimiter ; Here's the error message: ERROR: Unknown column 'old.jobpost_id' in 'where clause' Works fine here (although with different tables). Just for fun and giggles, have you tried OLD. (uppercase?) Okay . . . I tried OLD. delimiter // create trigger jobposts_control before delete on jobposts for each row begin declare dummy varchar(255); set @counted = ( select count(ad.adsource_id) from adsource ad, jobposts jp where ad.adsource_id = jp.adsource_id and OLD.jobpost_id = jp.jobpost_id ); if @counted = 1 then SET dummy = 'Cannot delete this record'; end if; end // delimiter; Now I just have this error message: 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 'end // delimiter' at line 1 Query = end // delimiter As a complete sidenote: It's better to write your JOINs with a JOIN clause and to put your strings inside single quotes as per SQL standard, double quotes are really for object names. Right . . . I'll get the JOIN clause figured out after I figure out what's causing the above error message. -- Lola J. Lee Beno - ColdFusion Programmer/Web Designer for Hire http://www.lolajl.net/resume | Blog at http://www.lolajl.net/blog/ In rivers, the water that you touch is the last of what has passed and the first of that which comes; so with present time. - Leonardo da Vinci (1452-1519) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to Create a Trigger
delimiter // create trigger jobposts_control before delete on jobposts for each row begin declare dummy varchar(255); set @counted = ( select count(ad.adsource_id) from adsource ad, jobposts jp where ad.adsource_id = jp.adsource_id and OLD.jobpost_id = jp.jobpost_id ); if @counted = 1 then SET dummy = 'Cannot delete this record'; end if; end // delimiter; Now I just have this error message: 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 'end // delimiter' at line 1 Query = end // delimiter I'm guessing it's the first semi-colon in your IF statement. Does this work...? if @counted = 1 then SET dummy = 'Cannot delete this record' end if; Just a guess! Andy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to Create a Trigger
Andy Shellam wrote: I'm guessing it's the first semi-colon in your IF statement. Does this work...? if @counted = 1 then SET dummy = 'Cannot delete this record' end if; Just a guess! Andy. That's not it, unfortunately. 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 'if @counted = 1 then SET dummy = 'Cannot delete this record' end if' at line 1 Query = if @counted = 1 then SET dummy = 'Cannot delete this record' end if -- Lola J. Lee Beno - ColdFusion Programmer/Web Designer for Hire http://www.lolajl.net/resume | Blog at http://www.lolajl.net/blog/ In rivers, the water that you touch is the last of what has passed and the first of that which comes; so with present time. - Leonardo da Vinci (1452-1519) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to Create a Trigger
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 'if @counted = 1 then SET dummy = 'Cannot delete this record' end if' at line 1 This works on 5.1.30 and 6.0.7: create table jobposts(adsource_id int,jobpost_id int); create table adsource(adsource_id int); delimiter // create trigger jobposts_control before delete on jobposts for each row begin declare dummy varchar(255); set @counted = (select count(ad.adsource_id) from adsource ad, jobposts jp where ad.adsource_id = jp.adsource_id and old.jobpost_id = jp.jobpost_id ); if @counted = 1 then SET dummy = Cannot delete this record; end if; end // delimiter ; PB - Lola J. Lee Beno wrote: Andy Shellam wrote: I'm guessing it's the first semi-colon in your IF statement. Does this work...? if @counted = 1 then SET dummy = 'Cannot delete this record' end if; Just a guess! Andy. That's not it, unfortunately. 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 'if @counted = 1 then SET dummy = 'Cannot delete this record' end if' at line 1 Query = if @counted = 1 then SET dummy = 'Cannot delete this record' end if Internal Virus Database is out of date. Checked by AVG - http://www.avg.com Version: 8.0.176 / Virus Database: 270.9.11/1820 - Release Date: 11/29/2008 6:52 PM
Re: Trying to Create a Trigger
Hi, What is the exact error message? Here's the latest query: delimiter // create trigger jobposts_control before delete on jobposts for each row begin declare dummy varchar(255); set @counted = ( select count(ad.adsource_id) from adsource ad, jobposts jp where ad.adsource_id = jp.adsource_id and old.jobpost_id = jp.jobpost_id ); if @counted = 1 then SET dummy = Cannot delete this record; end if; end // delimiter ; Here's the error message: ERROR: Unknown column 'old.jobpost_id' in 'where clause' Works fine here (although with different tables). Just for fun and giggles, have you tried OLD. (uppercase?) Okay . . . I tried OLD. delimiter // create trigger jobposts_control before delete on jobposts for each row begin declare dummy varchar(255); set @counted = ( select count(ad.adsource_id) from adsource ad, jobposts jp where ad.adsource_id = jp.adsource_id and OLD.jobpost_id = jp.jobpost_id ); if @counted = 1 then SET dummy = 'Cannot delete this record'; end if; end // delimiter; Try: end; // delimiter ; I just wrote the trigger source in the Trigger Editor in Database Workbench and it doesn't bother about delimiters and such... It also has no ; after the final END, but hey, who knows ;-) With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database questions? Check the forum: http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to Create a Trigger
On Fri, December 5, 2008 12:14, Martijn Tonies wrote: Hi, What is the exact error message? Here's the latest query: delimiter // create trigger jobposts_control before delete on jobposts for each row begin declare dummy varchar(255); set @counted = ( select count(ad.adsource_id) from adsource ad, jobposts jp where ad.adsource_id = jp.adsource_id and old.jobpost_id = jp.jobpost_id ); if @counted = 1 then SET dummy = Cannot delete this record; end if; end // delimiter ; Here's the error message: ERROR: Unknown column 'old.jobpost_id' in 'where clause' Works fine here (although with different tables). Just for fun and giggles, have you tried OLD. (uppercase?) Okay . . . I tried OLD. delimiter // create trigger jobposts_control before delete on jobposts for each row begin declare dummy varchar(255); set @counted = ( select count(ad.adsource_id) from adsource ad, jobposts jp where ad.adsource_id = jp.adsource_id and OLD.jobpost_id = jp.jobpost_id ); if @counted = 1 then SET dummy = 'Cannot delete this record'; end if; end // delimiter; Try: end; // delimiter ; I just wrote the trigger source in the Trigger Editor in Database Workbench and it doesn't bother about delimiters and such... It also has no ; after the final END, but hey, who knows ;-) With regards, Martijn Tonies Upscene Productions http://www.upscene.com I think you are missing the point. Where is 'OLD' or 'old' defined? Before you try to imbed it in a trigger, try the basic query. That seems to be what its complaining about. -- William R. Mussatto Systems Engineer http://www.csz.com 909-920-9154 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to Create a Trigger
I think you are missing the point. Where is 'OLD' or 'old' defined? Before you try to imbed it in a trigger, try the basic query. That seems to be what its complaining about. OLD is a virtual table which is only present in a trigger - it's like a table with the same layout as the table the trigger is attached to, which contains the row (or rows) that were deleted (for a DELETE query) or updated (for an UPDATE query - pre-update.) Just the same as (IIRC) NEW is a virtual table that contains the row (or rows) that were inserted (for an INSERT query) or updated (for an UPDATE query - post-update.) The query in question will fail anywhere outside the trigger because NEW and OLD are only in scope in triggers. OLD is akin to the deleted virtual table in MS SQL Server triggers, just like NEW is akin to the inserted virtual table in MSSQL. Going back to the OP's problem - the original issue I believe was he was using old instead of OLD (case-sensitive) - now that's sorted, MySQL is complaining about a syntax error toward the end of the function declaration. Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to create a trigger
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, I have 2 databases: production_db and test_db. Both databases have the same tables. Now, I want to do as follows: If a new record is inserted to the production_db interst the same recort into the test_db. I am pretty sure, it's a case fo a trigger, but I don't know how to difine it. Thanks, Uwe -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iQIcBAEBAgAGBQJJBDhNAAoJEEJXG7BUuynnJscQAMyQlA8Goq4nVKWrBOyjpMaI rB77YW6cTuNO7HLRcLVwTfNgtjowDGyeOKVjFxEAjZxMzwFdC4Kr+OXYb5sy+dIF LznzPwHSrSXwyEOHJXBmw57uk68oR/08XTp23m1Zcl6SY0L4cpdjnlVpfFkJcSR5 1mas874EGbjhM4XBBtaYhZofqz6fxoLthvBNIK4xv/RawVHcPoHa8ZUhOIitJkDS iXGF0dE+FrRJK4C8GBPPttX0RHys4+uQCAlyDPjRN9xPh+0iqg5rI2uwLiK9yWgh 8H18MDEed+l8cbMiP/Mdnb4uTPZ+qlTBV/BVn/Q9kgQSMY3wg1drqEIfFsuiLLp8 fqm+6P+Jsz9nZamjQ1g8DCw83c3AKbM9NkeW0Ywe1Y+BiGPsSVyHAu7fSYfvy7B2 K8Rj/rDIgvxIUOpQFQdpXzX+xE1U1xsmW/3DyLSXLxg5JysPfMnVocw1Nca8HVoW kCYOgC+fmlc9UAJevPhB0LjmCXvgn2O1eMGLEd3Ya31fp1QdXN1QoqTTqVurQLWM Qw+TRwWy//1Hip6lj8NALvDlWKQRWcQCR48oDQw+sgKNexu/rqHUKXZnBoo/Ndq2 eXNNY8P3BPlq0Pcxl7sGDyjBnnYXGmbmaz3TNmir5PXf3MDwTRldGQIhdfoc5Nw7 xKxCltGbCepsAQEp0sLz =z18P -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
trigger that calls a webservice??
Hi there, is it possible to define an update trigger that calls a webservice (or just some external method that would do it). we have a web frontent, that does the indexing of data in its own catalog (zope/plone). so I would like to be able to push an update to the frontend. thanks robert -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: trigger that calls a webservice??
On Fri, June 20, 2008 9:12 am, robert rottermann wrote: Hi there, is it possible to define an update trigger that calls a webservice (or just some external method that would do it). we have a web frontent, that does the indexing of data in its own catalog (zope/plone). so I would like to be able to push an update to the frontend. thanks robert I think the answer is no (at least it was last year) but I found work arounds. Google for mysql external command trigger. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: trigger that calls a webservice??
James wrote: On Fri, June 20, 2008 9:12 am, robert rottermann wrote: Hi there, is it possible to define an update trigger that calls a webservice (or just some external method that would do it). we have a web frontent, that does the indexing of data in its own catalog (zope/plone). so I would like to be able to push an update to the frontend. thanks robert I think the answer is no (at least it was last year) but I found work arounds. Google for mysql external command trigger. Well, you can create a UDF, and should be able to call the UDF within a trigger.. Check out some of the memcached UDFs that were created: http://capttofu.livejournal.com/8078.html These should give a good idea of A) how to create a UDF and B) how to talk to another process within them to send / update data etc. You might even choose to cache your stuff in memcached and just use these anyway! ;) Cheers, Mark -- Mark Leith MySQL Regional Support Manager, Americas Sun Microsystems, Inc., http://www.sun.com/mysql/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: trigger that calls a webservice??
On 20 Jun 2008, at 06:43, James wrote: On Fri, June 20, 2008 9:12 am, robert rottermann wrote: Hi there, is it possible to define an update trigger that calls a webservice (or just some external method that would do it). we have a web frontent, that does the indexing of data in its own catalog (zope/plone). so I would like to be able to push an update to the frontend. thanks robert I think the answer is no (at least it was last year) but I found work arounds. Google for mysql external command trigger. You can declare a stored proc as an XMLRPC request and use that as a trigger. Such functionality already works in our experimental tree. http://forge.mysql.com/wiki/ProjectPage_External_Language_Stored_Procedures Regards Antony. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trigger problem
On May 15, 2008, at 4:30 AM, rustam ershtukaev wrote: I have been trying to write a trigger in mysql, but can't get it to work. It's really simple,i just need my trigger to add varchar value to a table on insert if postcode = 1000. Where does postcode come from? Your SELECT statement appears to have no relation to the row to be inserted. It also appear that it will always set v_postcode to 1000 if the departement table contains *any* rows with a postcode of 1000. this is how i did it: delimiter | drop trigger testdep| create trigger testdep before insert on departements for each row begin declare v_postcode INTEGER; declare v_place VARCHAR; select departement_postcode into v_postcode from departement where departement_postcode = 1000; IF v_postcode = 1000 then update departementen set departement_place = 'New York' where departement_postcode = 1000; END IF; END| but when i insert a new row i don't get my v_place value set so if there someone who has time to help me with this i would greatly appreciate this :) -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Trigger problem
I have been trying to write a trigger in mysql, but can't get it to work. It's really simple,i just need my trigger to add varchar value to a table on insert if postcode = 1000. this is how i did it: delimiter | drop trigger testdep| create trigger testdep before insert on departements for each row begin declare v_postcode INTEGER; declare v_place VARCHAR; select departement_postcode into v_postcode from departement where departement_postcode = 1000; IF v_postcode = 1000 then update departementen set departement_place = 'New York' where departement_postcode = 1000; END IF; END| but when i insert a new row i don't get my v_place value set so if there someone who has time to help me with this i would greatly appreciate this :)
Re: Trigger problem
On Thu, May 15, 2008 at 2:30 AM, rustam ershtukaev [EMAIL PROTECTED] wrote: I have been trying to write a trigger in mysql, but can't get it to work. It's really simple,i just need my trigger to add varchar value to a table on insert if postcode = 1000. this is how i did it: delimiter | drop trigger testdep| create trigger testdep before insert on departements for each row begin declare v_postcode INTEGER; declare v_place VARCHAR; select departement_postcode into v_postcode from departement where departement_postcode = 1000; IF v_postcode = 1000 then update departementen set departement_place = 'New York' where departement_postcode = 1000; END IF; END| but when i insert a new row i don't get my v_place value set so if there someone who has time to help me with this i would greatly appreciate this :) If I had to make a guess it is because you are using before insert, and there are no other rows that match: select departement_postcode into v_postcode from departement where departement_postcode = 1000; so the if statement fails. Example: * Using after* mysql drop table if exists t1,t2; Query OK, 0 rows affected (0.00 sec) mysql CREATE TABLE `t1` ( - `col1` int, - `col2` varchar(20) - ) ; Query OK, 0 rows affected (0.03 sec) mysql mysql CREATE TABLE `t2` ( - `col1` int, - `col2` varchar(20) - ); Query OK, 0 rows affected (0.03 sec) mysql mysql delimiter | mysql drop trigger if exists testdep| Query OK, 0 rows affected, 1 warning (0.00 sec) mysql create trigger testdep -after insert on t1 -for each row - begin -declare v_col1 INTEGER; - - select col1 -into v_col1 -from t1 -where col1 = 5; - - IF v_col1 = 5 then -update t2 set col2 = 'delta'; - END IF; - - END| Query OK, 0 rows affected (0.01 sec) mysql delimiter ; mysql mysql insert into t1 values(0,'alpha'); Query OK, 1 row affected, 1 warning (0.00 sec) mysql mysql insert into t2 values(5,'bravo'); Query OK, 1 row affected (0.00 sec) mysql mysql insert into t1 values(5,'charlie'); Query OK, 1 row affected (0.00 sec) mysql mysql select * from t2; +--+---+ | col1 | col2 | +--+---+ |5 | delta | +--+---+ 1 row in set (0.00 sec) * On the other hand before* mysql drop table if exists t1,t2; Query OK, 0 rows affected (0.00 sec) mysql CREATE TABLE `t1` ( - `col1` int, - `col2` varchar(20) - ) ; Query OK, 0 rows affected (0.03 sec) mysql mysql CREATE TABLE `t2` ( - `col1` int, - `col2` varchar(20) - ); Query OK, 0 rows affected (0.05 sec) mysql mysql delimiter | mysql drop trigger if exists testdep| Query OK, 0 rows affected, 1 warning (0.00 sec) mysql create trigger testdep -before insert on t1 -for each row - begin -declare v_col1 INTEGER; - - select col1 -into v_col1 -from t1 -where col1 = 5; - - IF v_col1 = 5 then -update t2 set col2 = 'delta'; - END IF; - - END| Query OK, 0 rows affected (0.00 sec) mysql delimiter ; mysql mysql insert into t1 values(0,'alpha'); Query OK, 1 row affected, 1 warning (0.00 sec) mysql mysql insert into t2 values(5,'bravo'); Query OK, 1 row affected (0.00 sec) mysql mysql insert into t1 values(5,'charlie'); Query OK, 1 row affected, 1 warning (0.00 sec) mysql mysql select * from t2; +--+---+ | col1 | col2 | +--+---+ |5 | bravo | +--+---+ 1 row in set (0.00 sec) I suggest you provide a complete example (ddl and dml, and btw is a trigger definition ddl or dml?) if you need more assistance. -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DISABLE TRIGGER alternative
Hi All, I want to disable a trigger on a table for the statements that run within a stored procedure. As DISABLE TRIGGER is not an option I was wondering if any body has any alternatives/ideas on how to achieve this Thanks Olaf - Confidentiality Notice: The following mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. The recipient is responsible to maintain the confidentiality of this information and to use the information only for authorized purposes. If you are not the intended recipient (or authorized to receive information for the intended recipient), you are hereby notified that any review, use, disclosure, distribution, copying, printing, or action taken in reliance on the contents of this e-mail is strictly prohibited. If you have received this communication in error, please notify us immediately by reply e-mail and destroy all copies of the original message. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
trigger question..
hi.
using mysql 5.0.27 and playing with triggers.
a simple db:
create table foo{
aa varchar (10),
bb int auto_increment,
cc varchar (10),
} innondb
i'm trying to figure out how to create a trigger, such that if the user does
an insert into foo (cc) value (www);
the table will concat the www with the 'id' value to produce:
foo
aabb cc
www-1 1 www
i've been reviewing triggers, and various examples, and for the life of me,
i can't figure out how to modify a field of the row or the item i'm
triggering off of...
any thoughts/comments/pointers would be helpful!!
thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: trigger question..
create table foo
(aa varchar(20),
id integer,
cc varchar(20));
delimiter |
create trigger foo_ins before insert on foo
for each row
begin
set new.aa = concat(new.cc,'-',new.id);
end; |
delimiter ;
insert into foo (cc,id) values ('www',1);
select * from foo;
-Original Message-
From: bruce [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 19, 2007 7:59 PM
To: 'mysql list'
Subject: trigger question..
hi.
using mysql 5.0.27 and playing with triggers.
a simple db:
create table foo{
aa varchar (10),
bb int auto_increment,
cc varchar (10),
} innondb
i'm trying to figure out how to create a trigger, such that if the user does
an insert into foo (cc) value (www); the table will concat the www with
the 'id' value to produce:
foo
aabb cc
www-1 1 www
i've been reviewing triggers, and various examples, and for the life of me,
i can't figure out how to modify a field of the row or the item i'm
triggering off of...
any thoughts/comments/pointers would be helpful!!
thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Internal Virus Database is out-of-date.
Checked by AVG Free Edition.
Version: 7.5.503 / Virus Database: 269.16.17 - Release Date: 12/6/2007 12:00
AM
Internal Virus Database is out-of-date.
Checked by AVG Free Edition.
Version: 7.5.503 / Virus Database: 269.16.17 - Release Date: 12/6/2007 12:00
AM
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: trigger question..
hi joe...
thanks, and what you provided works... but you changed the tbl def...
you're using an int, and you're passing the value to the tbl.
in my situation, i'm using an auto_increment, and i can't seem to use the
create before... as the auto_increment wouldn't be set prior to the
row being created...
any more pointers/thoughts/...
thanks
-Original Message-
From: joe [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 19, 2007 7:33 PM
To: 'bruce'; 'mysql list'
Subject: RE: trigger question..
create table foo
(aa varchar(20),
id integer,
cc varchar(20));
delimiter |
create trigger foo_ins before insert on foo
for each row
begin
set new.aa = concat(new.cc,'-',new.id);
end; |
delimiter ;
insert into foo (cc,id) values ('www',1);
select * from foo;
-Original Message-
From: bruce [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 19, 2007 7:59 PM
To: 'mysql list'
Subject: trigger question..
hi.
using mysql 5.0.27 and playing with triggers.
a simple db:
create table foo{
aa varchar (10),
bb int auto_increment,
cc varchar (10),
} innondb
i'm trying to figure out how to create a trigger, such that if the user does
an insert into foo (cc) value (www); the table will concat the www with
the 'id' value to produce:
foo
aabb cc
www-1 1 www
i've been reviewing triggers, and various examples, and for the life of me,
i can't figure out how to modify a field of the row or the item i'm
triggering off of...
any thoughts/comments/pointers would be helpful!!
thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Internal Virus Database is out-of-date.
Checked by AVG Free Edition.
Version: 7.5.503 / Virus Database: 269.16.17 - Release Date: 12/6/2007 12:00
AM
Internal Virus Database is out-of-date.
Checked by AVG Free Edition.
Version: 7.5.503 / Virus Database: 269.16.17 - Release Date: 12/6/2007 12:00
AM
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: before insert trigger
Not in MySQL 5.0. There is no error trapping mechanism to escape triggers as currently implemented. You are better off writing a stored procedure to do the INSERTs and have your application call the stored procedure. -Original Message- From: Ed Reed [mailto:[EMAIL PROTECTED] Sent: Thursday, December 13, 2007 7:09 PM To: mysql@lists.mysql.com Subject: before insert trigger Is it possible for a trigger to test the data to be inserted and if it doesn't meet specific criteria not insert the data at all? I have an application that writes a lot of data to my table. I don't have control over this application and it writes a lot more data then I need. So I'd like to have a trigger that doesn't allow certain records to be written at all. I can't seem to find anything in the documentation that describes what I'm looking for. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
before insert trigger
Is it possible for a trigger to test the data to be inserted and if it doesn't meet specific criteria not insert the data at all? I have an application that writes a lot of data to my table. I don't have control over this application and it writes a lot more data then I need. So I'd like to have a trigger that doesn't allow certain records to be written at all. I can't seem to find anything in the documentation that describes what I'm looking for. Thanks
Re: trigger question...
Hi Bruce, delimiter | create trigger mfgtst after insert on masterHostTBL for each row begin set @tmp = 55; insert into masterTestResultStartTBL set hostID = NEW.id, testResultVal = 88; /* set @q = masterTestResultStartValTBL.serverStartVal; set @w = masterTestResultStartValTBL.serverSepVal; */ /* set hostID = NEW.id, testResultVal = 88; */ /* (NEW.id-1)*masterTestResultStartValTBL.serverSepVal; */ end; | delimiter ; -- i can't seem to figure out where/when/how to get the values in the masterTestResultStartValTBL to be used... -- set @q = masterTestResultStartValTBL.serverStartVal; -- set @w = masterTestResultStartValTBL.serverSepVal; when i import the sql... i get an invalid table in the field.. Your trigger is on table masterHostTBL, that means if you want to get values from table masterTestResultStartValTBL, you need to SELECT them from that table. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
trigger question...
hi... i have the following test sql/schema. i'm trying to create a trigger that would allow an item in tbl2 to be updated, based upon values from the tbl that's being inserted into, and the value in a 2nd tbl.element. the sql/schema: /* test schema for stratalight file project # # b douglas # #3 creates the database, tbls for the project # the tbl contains the file information for the various systems # drives/users in thew stratalight system/environment # # # # */ drop database if exists jfrank; create database jfrank; use jfrank; /* basic data tbl */ DROP TABLE IF EXISTS masterTestResultStartValTBL; CREATE TABLE masterTestResultStartValTBL ( serverStartVal int(20) not null default '0', serverSepVal int(20) not null default '0', prodVal int(10) not null default '0' ) TYPE=MyISAM DEFAULT CHARSET=latin1; /* result startTBL */ DROP TABLE IF EXISTS masterTestResultStartTBL; CREATE TABLE masterTestResultStartTBL ( hostID int(5) not null default '0', testResultVal int(20) not null default '0', id int(10) NOT NULL auto_increment, PRIMARY KEY (id) ) TYPE=MyISAM DEFAULT CHARSET=latin1; DROP TABLE IF EXISTS masterHostTBL; CREATE TABLE masterHostTBL ( host varchar(50) default '', id int(15) NOT NULL auto_increment, PRIMARY KEY (id) ) TYPE=MyISAM DEFAULT CHARSET=latin1; --set @q = masterTestResultStartValTBL.serverStartVal; --set @w = masterTestResultStartValTBL.serverSepVal; delimiter | create trigger mfgtst after insert on masterHostTBL for each row begin set @tmp = 55; insert into masterTestResultStartTBL set hostID = NEW.id, testResultVal = 88; /* set @q = masterTestResultStartValTBL.serverStartVal; set @w = masterTestResultStartValTBL.serverSepVal; */ /* set hostID = NEW.id, testResultVal = 88; */ /* (NEW.id-1)*masterTestResultStartValTBL.serverSepVal; */ end; | delimiter ; -- i can't seem to figure out where/when/how to get the values in the masterTestResultStartValTBL to be used... -- set @q = masterTestResultStartValTBL.serverStartVal; -- set @w = masterTestResultStartValTBL.serverSepVal; when i import the sql... i get an invalid table in the field.. any thoughts/pointers/questions would be appreciated. basically, the app inserts a value in tbl1. i'd like the trigger to be able to then use a value in tbl2, and then compute a final value, that would then be inserted into tbl3. thanks!!! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Trigger/Locking question--
Hi... I'm trying to get me head around a possible situation involving locks/triggers. Suppose I have two tables: FooTBL CatTBL in FooTBL, I have a trigger that operates such that whenever a new row is added to FooTBL, it's immeadiately copied to CatTBL. I'm trying to understand what happens if I do an operation with CatTBL, while FooTBL is trying to write to CatTBL because of the trigger. If CatTBL is in use, does the trigger on FooTBL not get implemented? How does locking CatTBL play a role in this? My basic need is to reliably be able to ensure that everytime a row is added to FooTBL, that it gets copied to CatTBL. At the same time, if I'm doing some query to CatTBL (read/write/delete/update/etc...) that I don't cause an issue with FooTBL or it's trigger(s). Thanks... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trigger problem
On Thu, 2007-11-08 at 17:56 -0800, Lucky Wijaya wrote: Yes, the trigger code is works. Many thanks !! Now I understand the use of delimiter command. Thanks again... =) My next question is, do we able to view the triggers that has been created ? And how ? David Schneider-Joseph [EMAIL PROTECTED] wrote: My apologies, try this: DELIMITER ;; CREATE TRIGGER DEL_TB_INSTRUKSI AFTER DELETE ON Tb_Instruksi FOR EACH ROW BEGIN DELETE FROM Tb_Stuffing WHERE No_Instruksi = OLD.No_Instruksi; END; ;; DELIMITER ; To answer your question: The DELIMITER statement tells MySQL to use a different set of characters to terminate statements. This is necessary when you want to use a ; in your actual statement. In this case, the entire trigger definition is considered one statement, but the ; in the DELETE... line is being interpreted as the termination of it. Yes, it's dumb. On Nov 7, 2007, at 2:53 AM, Lucky Wijaya wrote: No, I didn't set the delimiter. But, it still have an error after I set delimiter in my trigger as your example. By the way, what's delimiter mean ? And what it's for ? Thanks to you Mr. David. David Schneider-Joseph wrote: Lucky, Did you make sure to set your delimiter before and after the CREATE TRIGGER statement? e.g.: DELIMITER ;; CREATE TRIGGER DEL_TB_INSTRUKSI AFTER DELETE ON Tb_Instruksi FOR EACH ROW BEGIN DELETE FROM Tb_Stuffing WHERE No_Instruksi = OLD.No_Instruksi; END;; DELIMITER ; On Nov 6, 2007, at 11:11 PM, Lucky Wijaya wrote: Hi, my name is Lucky from Indonesia. I build an database application using Delphi 7 MySQL as the RDBMS. Now, I'm having problem in creating trigger in MySQL. Here is the code of the trigger: CREATE TRIGGER DEL_TB_INSTRUKSI AFTER DELETE ON Tb_Instruksi FOR EACH ROW BEGIN DELETE FROM Tb_Stuffing WHERE No_Instruksi = OLD.No_Instruksi; END; It results an error message that the SQL syntax (on delete command) is incorrect. I didn't find yet the incorrect part of my SQL syntax. Could somebody help my problem ? Thank you very much. Note: I'm already using MySQL v. 5.0.41 and using GUI in creating the trigger. I also have tried to create the trigger through mysql command line, but it result the same error message. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com mysql show triggers; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
trigger/cron process questions...
Hi. I'm considering a situation where I have a number of child/client servers, each of which are running local apps that feed into a local mysql db/tbl. In order to manage the data, I want to copy all the mysql db/tbl data from the chil/client systems, to a single central/master db. I do not want to simply have the local apps write directly to the central db for a number of reasons. The approach I need, is to write local, and then copy this information from the local mysql, to the central/parent mysql/db on a separate machine. I've considered Replication (Master/Slave) but then realized that you can't have a slave, with multiple masters. In my case, each of the child systems, would be considered to be Masters, with the central machines being the slave. So it appears that the mysql replication isn't suitable. I'm considering simply using cron processes on the child machines, where the cron app would simply fir on a periodic basis, and write any new data from the child db to the central system (assuming the network/central machine is up/running). This kind of process is simple, full proof, and pretty straightfoward to implement. In researching, I've come across articles discussing triggers, and I'm wondering if triggers might prove usefful or this issue. Is it possible to have a periodic trigger, IE a trigger that gets fired based on time. I could have a cron process that updates a tbl on a periodic basis, and a trigger on that tbl. When that trigger fires, it could then update/insert the local data into the remote/central db/tbl. Thoughts/Comments/Pointers/Etc.. .would be helpful. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trigger problem
Yes, the trigger code is works. Many thanks !! Now I understand the use of delimiter command. Thanks again... =) My next question is, do we able to view the triggers that has been created ? And how ? David Schneider-Joseph [EMAIL PROTECTED] wrote: My apologies, try this: DELIMITER ;; CREATE TRIGGER DEL_TB_INSTRUKSI AFTER DELETE ON Tb_Instruksi FOR EACH ROW BEGIN DELETE FROM Tb_Stuffing WHERE No_Instruksi = OLD.No_Instruksi; END; ;; DELIMITER ; To answer your question: The DELIMITER statement tells MySQL to use a different set of characters to terminate statements. This is necessary when you want to use a ; in your actual statement. In this case, the entire trigger definition is considered one statement, but the ; in the DELETE... line is being interpreted as the termination of it. Yes, it's dumb. On Nov 7, 2007, at 2:53 AM, Lucky Wijaya wrote: No, I didn't set the delimiter. But, it still have an error after I set delimiter in my trigger as your example. By the way, what's delimiter mean ? And what it's for ? Thanks to you Mr. David. David Schneider-Joseph wrote: Lucky, Did you make sure to set your delimiter before and after the CREATE TRIGGER statement? e.g.: DELIMITER ;; CREATE TRIGGER DEL_TB_INSTRUKSI AFTER DELETE ON Tb_Instruksi FOR EACH ROW BEGIN DELETE FROM Tb_Stuffing WHERE No_Instruksi = OLD.No_Instruksi; END;; DELIMITER ; On Nov 6, 2007, at 11:11 PM, Lucky Wijaya wrote: Hi, my name is Lucky from Indonesia. I build an database application using Delphi 7 MySQL as the RDBMS. Now, I'm having problem in creating trigger in MySQL. Here is the code of the trigger: CREATE TRIGGER DEL_TB_INSTRUKSI AFTER DELETE ON Tb_Instruksi FOR EACH ROW BEGIN DELETE FROM Tb_Stuffing WHERE No_Instruksi = OLD.No_Instruksi; END; It results an error message that the SQL syntax (on delete command) is incorrect. I didn't find yet the incorrect part of my SQL syntax. Could somebody help my problem ? Thank you very much. Note: I'm already using MySQL v. 5.0.41 and using GUI in creating the trigger. I also have tried to create the trigger through mysql command line, but it result the same error message. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: Trigger problem
My apologies, try this: DELIMITER ;; CREATE TRIGGER DEL_TB_INSTRUKSI AFTER DELETE ON Tb_Instruksi FOR EACH ROW BEGIN DELETE FROM Tb_Stuffing WHERE No_Instruksi = OLD.No_Instruksi; END; ;; DELIMITER ; To answer your question: The DELIMITER statement tells MySQL to use a different set of characters to terminate statements. This is necessary when you want to use a ; in your actual statement. In this case, the entire trigger definition is considered one statement, but the ; in the DELETE... line is being interpreted as the termination of it. Yes, it's dumb. On Nov 7, 2007, at 2:53 AM, Lucky Wijaya wrote: No, I didn't set the delimiter. But, it still have an error after I set delimiter in my trigger as your example. By the way, what's delimiter mean ? And what it's for ? Thanks to you Mr. David. David Schneider-Joseph [EMAIL PROTECTED] wrote: Lucky, Did you make sure to set your delimiter before and after the CREATE TRIGGER statement? e.g.: DELIMITER ;; CREATE TRIGGER DEL_TB_INSTRUKSI AFTER DELETE ON Tb_Instruksi FOR EACH ROW BEGIN DELETE FROM Tb_Stuffing WHERE No_Instruksi = OLD.No_Instruksi; END;; DELIMITER ; On Nov 6, 2007, at 11:11 PM, Lucky Wijaya wrote: Hi, my name is Lucky from Indonesia. I build an database application using Delphi 7 MySQL as the RDBMS. Now, I'm having problem in creating trigger in MySQL. Here is the code of the trigger: CREATE TRIGGER DEL_TB_INSTRUKSI AFTER DELETE ON Tb_Instruksi FOR EACH ROW BEGIN DELETE FROM Tb_Stuffing WHERE No_Instruksi = OLD.No_Instruksi; END; It results an error message that the SQL syntax (on delete command) is incorrect. I didn't find yet the incorrect part of my SQL syntax. Could somebody help my problem ? Thank you very much. Note: I'm already using MySQL v. 5.0.41 and using GUI in creating the trigger. I also have tried to create the trigger through mysql command line, but it result the same error message. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Trigger problem
Hi, my name is Lucky from Indonesia. I build an database application using Delphi 7 MySQL as the RDBMS. Now, I'm having problem in creating trigger in MySQL. Here is the code of the trigger: CREATE TRIGGER DEL_TB_INSTRUKSI AFTER DELETE ON Tb_Instruksi FOR EACH ROW BEGIN DELETE FROM Tb_Stuffing WHERE No_Instruksi = OLD.No_Instruksi; END; It results an error message that the SQL syntax (on delete command) is incorrect. I didn't find yet the incorrect part of my SQL syntax. Could somebody help my problem ? Thank you very much. Note: I'm already using MySQL v. 5.0.41 and using GUI in creating the trigger. I also have tried to create the trigger through mysql command line, but it result the same error message. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: Trigger problem
Lucky, Did you make sure to set your delimiter before and after the CREATE TRIGGER statement? e.g.: DELIMITER ;; CREATE TRIGGER DEL_TB_INSTRUKSI AFTER DELETE ON Tb_Instruksi FOR EACH ROW BEGIN DELETE FROM Tb_Stuffing WHERE No_Instruksi = OLD.No_Instruksi; END;; DELIMITER ; On Nov 6, 2007, at 11:11 PM, Lucky Wijaya wrote: Hi, my name is Lucky from Indonesia. I build an database application using Delphi 7 MySQL as the RDBMS. Now, I'm having problem in creating trigger in MySQL. Here is the code of the trigger: CREATE TRIGGER DEL_TB_INSTRUKSI AFTER DELETE ON Tb_Instruksi FOR EACH ROW BEGIN DELETE FROM Tb_Stuffing WHERE No_Instruksi = OLD.No_Instruksi; END; It results an error message that the SQL syntax (on delete command) is incorrect. I didn't find yet the incorrect part of my SQL syntax. Could somebody help my problem ? Thank you very much. Note: I'm already using MySQL v. 5.0.41 and using GUI in creating the trigger. I also have tried to create the trigger through mysql command line, but it result the same error message. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trigger problem
No, I didn't set the delimiter. But, it still have an error after I set delimiter in my trigger as your example. By the way, what's delimiter mean ? And what it's for ? Thanks to you Mr. David. David Schneider-Joseph [EMAIL PROTECTED] wrote: Lucky, Did you make sure to set your delimiter before and after the CREATE TRIGGER statement? e.g.: DELIMITER ;; CREATE TRIGGER DEL_TB_INSTRUKSI AFTER DELETE ON Tb_Instruksi FOR EACH ROW BEGIN DELETE FROM Tb_Stuffing WHERE No_Instruksi = OLD.No_Instruksi; END;; DELIMITER ; On Nov 6, 2007, at 11:11 PM, Lucky Wijaya wrote: Hi, my name is Lucky from Indonesia. I build an database application using Delphi 7 MySQL as the RDBMS. Now, I'm having problem in creating trigger in MySQL. Here is the code of the trigger: CREATE TRIGGER DEL_TB_INSTRUKSI AFTER DELETE ON Tb_Instruksi FOR EACH ROW BEGIN DELETE FROM Tb_Stuffing WHERE No_Instruksi = OLD.No_Instruksi; END; It results an error message that the SQL syntax (on delete command) is incorrect. I didn't find yet the incorrect part of my SQL syntax. Could somebody help my problem ? Thank you very much. Note: I'm already using MySQL v. 5.0.41 and using GUI in creating the trigger. I also have tried to create the trigger through mysql command line, but it result the same error message. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: Trigger
Hi, I have a problem with a trigger which should conver a unix timestamp to a MySQL date datatype. The trigger works if the column is varchar, but when the column is date type, it write the date of 1969-31-12. Instead of column, I guess you mean value? Any ideas? DROP TABLE IF EXISTS `visitas`; CREATE TABLE `visitas` ( `id` int(11) NOT NULL auto_increment, `date` varchar(25) default NULL, If you want to store a date-value, use a date-datatype, not a character based datatype. `elapsed` int default NULL, `src_ip` varchar(15) default NULL, `result_code` varchar(25) default NULL, `http_status` TINYINT default NULL, `bytes` int default NULL, `request` varchar(50) default NULL, `authname` varchar(10) default NULL, `type` varchar(20) default NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; /*!50003 SET @OLD_SQL_MODE=@@SQL_MODE*/; DELIMITER ;; /*!50003 SET SESSION SQL_MODE=STRICT_TRANS_TABLES */;; /*!50003 CREATE */ /*!50017 [EMAIL PROTECTED] */ /*!50003 TRIGGER `unix2normaltime` BEFORE INSERT ON `visitas` FOR EACH ROW begin set New.date=date(from_unixtime(New.date)); end */;; Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trigger
If i define that the value for the field must be varchar data type, `fecha` varchar(25) default NULL, the triger records the right date. I think because is wrinting a string only. But if i define the field as a date data type, `fecha` date default NULL, the value writen is: 1969-31-12. I suppose this is because the table is waiting for a date data type, but the triger sends a string data type. When you send an INSERT statement to the server, what value are you passing for the date column? If you want to store a DATE, send a date! Don't send a unixtime, or a varchar in some format, send a date. Why on earth would you go sending a unixtime to the server and then using a trigger to convert it into a real date?! im correct? if im, should CAST help me? No, sending the proper value, that's what you should be doing. And storing a date-value in a DATE datatype will only be more convenient later on. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com I have a problem with a trigger which should conver a unix timestamp to a MySQL date datatype. The trigger works if the column is varchar, but when the column is date type, it write the date of 1969-31-12. Instead of column, I guess you mean value? Any ideas? DROP TABLE IF EXISTS `visitas`; CREATE TABLE `visitas` ( `id` int(11) NOT NULL auto_increment, `date` varchar(25) default NULL, If you want to store a date-value, use a date-datatype, not a character based datatype. `elapsed` int default NULL, `src_ip` varchar(15) default NULL, `result_code` varchar(25) default NULL, `http_status` TINYINT default NULL, `bytes` int default NULL, `request` varchar(50) default NULL, `authname` varchar(10) default NULL, `type` varchar(20) default NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; /*!50003 SET @OLD_SQL_MODE=@@SQL_MODE*/; DELIMITER ;; /*!50003 SET SESSION SQL_MODE=STRICT_TRANS_TABLES */;; /*!50003 CREATE */ /*!50017 [EMAIL PROTECTED] */ /*!50003 TRIGGER `unix2normaltime` BEFORE INSERT ON `visitas` FOR EACH ROW begin set New.date=date(from_unixtime(New.date)); end */;; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trigger
Mostly because use 2 field to represent the same data is a waste of storage i think. Oh, ok. So you're sending a non-date value and you want to transform it into an actual character presentation of a date value, but making things easier on yourself is a waste of storage. Feel free to continu jumping through hoops and try to solve the problem down the line instead of right there at the beginning. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com The aplication which is writing to the database is sending the date in unix format. I can't no change that, so i suposse using a triger will help. The application pass the 'xx.xx' value when is inserting to the table. Righto. Well, what does this mean then: im converting the unixtime to normal time with from_unixtime. So after i did the convertion i write the result to the table. It works ok when i write to a varchar column, but not to a date column Why not store the unixtime value and use an additional column to store an actual DATE value instead? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trigger
Mostly because use 2 field to represent the same data is a waste of storage i think. - Martijn Tonies [EMAIL PROTECTED] escribió: The aplication which is writing to the database is sending the date in unix format. I can't no change that, so i suposse using a triger will help. The application pass the 'xx.xx' value when is inserting to the table. Righto. Well, what does this mean then: im converting the unixtime to normal time with from_unixtime. So after i did the convertion i write the result to the table. It works ok when i write to a varchar column, but not to a date column Why not store the unixtime value and use an additional column to store an actual DATE value instead? Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trigger
The aplication which is writing to the database is sending the date in unix format. I can't no change that, so i suposse using a triger will help. The application pass the 'xx.xx' value when is inserting to the table. Righto. Well, what does this mean then: im converting the unixtime to normal time with from_unixtime. So after i did the convertion i write the result to the table. It works ok when i write to a varchar column, but not to a date column Why not store the unixtime value and use an additional column to store an actual DATE value instead? Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
