• 1. simple database design question
    I have several dbs with numerous properly "normalized" tables, now I need a very simple db to track name, phone #, and email address of a small group. I am thinking of just creating a single table db. I know this violates normalization rules, but in this simple instance what's the harm?
  • 2. DELETEing a row vs UPDATEing a flag
    Hi all, I have a large busy myisam table which has a lot of inserts and deletes acting on it (as well as selects and updates). I am sure I have read somewhere that it can be more efficient not to delete a row at all, but to just set a "deleted" flag on it, and then perhaps do a bulk delete at some quiet time. I can't find where I read it now though. Is this the case? Are there only particular use cases where it makes sense? Thanks
  • 3. Fixing column widths in Query Manager!
    Stupid question. Is there any way to make the column widths for result sets stay put in Query Manager? When I have lots of fields to fit onto the screen and I refresh the query, the column widths all go back to defaults and it's driving me nuts! Cheers.
  • 4. Q: compiling a UDF using VC++6
    Greetings all, I need to create a UDF under Windoze. I've found some examples of MySQL UDFs and some webpages describing how I need to compile them as a DLL (on a Windoze box). I have Visual Studio 6.0 EE . I'm running MySQL Server 5.0 installed as a precompiled binary. I do see an ./include directory under C:\Program_Files\MySQL \MySQL_Server_5.0 and this dir does contain the header files referenced in the UDF examples I've found. I need some help setting up my project in VC++6 to compile my UDF. Would someone please give me a hand? TIA, Still-learning Steve
  • 5. mysql language problems
    Hello guys! I am trying to build a database but I have problems with the records. When Im inserting greek characters I see in my command prompt "?????" and that's also on the web pages which retrieve the data. I've tried things such as "SET NAMES utf8", changing the collations of the tables but didn't work..... Any ideas what should I type to make a table work with any language (that's why I want UTF8) ?! I want to make it work not only with Greek but also with other character sets which are none-english thanks in advance!


Postby D A V E » Sun, 28 Dec 2008 00:51:58 GMT

Always use manualy Query
(UPDATE table SET column = replace(column,"-","") WHERE `column` REGEXP '-')

My question:
How to build a "trigger" for the update database which removes the sign "-"?


Postby Luuk » Sun, 28 Dec 2008 01:03:43 GMT

D A V E schreef:

( http://www.**--****.com/ )

     UPDATE table SET column = replace(column,"-","") WHERE `column` 

But i'm not sure....

Would this do an 'UPDATE' for EACH ROW it finds in table 'table'...?

And would 'AFTER UPDATE' also be triggered after an insert...?


Postby Michael Austin » Tue, 06 Jan 2009 04:50:32 GMT

Given the syntax used it would execute against all rows.  You need to 
look at the docs.


In an trigger there is an OLD and NEW column.. OLD being the existing 
and NEW being the new values you have supplied in the INSERT or UPDATE 

Similar Threads:

1.(RegExp in Access):VBA (RegExp in SQLServer):?

Currently I am using the RegExp object to parse a large dataset in an Access 
table - but this table was exported from SQL Server, and the very correct 
question was asked - why not just do it in SQL Server.

What would be the best way to convert the VBA code I use in Access to SQL 
Server - being only marginally familiar with T-SQL syntax and not at all 
familiar with what can or cannot be done?

Darryl Kerkeslager 

2.create a trigger for update field

I've a member table with a field named


(type int)

that contain values like 1215285455

that I've a credit field (type int)

I would like to create a new trigger that add 1 value in credit field 
each time that a user enter in the site.... please note that I've to add 
1 only one time per day

if today I enter 3 time I want only add one value in credit field

Can you help me??

I can also create new temp field for manage this information

thanks to create error raised by trigger


i would have a trigger to check the data before inserting/updating a
table and if the data is not valid it would raise an error ...

like if < 0 then raise_error(10001, ' you cannot use the
resources anymore') end if ;

in oracle i used raise_application_error(errcode, 'explaination');

how to do the same thing on mysql ?

I also got an 'weird' data handling in mysql, suppose the field was
"not null" but when i tried to insert/update the data .. it still able
to insert the data ...

id : int, auto increment, primary key
name : varchar(20) ; not null ;
desc : varchar(20): not null ;

when i did a script like this
insert into tableX (name) values ('ABCDEF');

it would insert into database without generating error ... it should
raise an error because 'desc' should be filled in (not null).

how to solve this problems without my program doing a checking one by
one  ?


4.Trigger on creating a record

Hi All

I am writing an in-house application for managing our projects.  all
going fine so far.

I would like to automatically create a folder on the server (FreeBSD
5.21) when a new record is added to the projects table.  The folder
will be in a samba shared folder and the name would come from a
combination of two compulsory fields in the projects table.

I am looking to use a trigger on the insert command but do not know
how to interact with the OS.  The ownership of the parent folder will
be given to the mysql user.

Any suggestions



5.Trouble creating triggers

Here is the code I tried:

delimiter $;
CREATE TRIGGER archive_addresses_ins BEFORE INSERT ON archive_addresses
    SET NEW.interview_date = CURRENT_TIMESTAMP;
delimiter ;

I adapted this from an example on page 207 in the third edition of
"MySQL: The definitive guide to using, programming, and administering
MySQL 4.1 and 5.0" by Paul DuBois.

When submitted using MySQL Query Browser, I get an error 1064,
complaining about the last line shown.

I was going to add this trigger to each of my archive tables, and then
use an insert statement of the following form to keep the archives up
to date.

INSERT INTO archive_t
     SELECT * FROM t
           WHERE .....

Of course, the where clause will be structured to ensure only the
current data being stored in the main tables (using either insert or
update statements) will be added to the archive.

The tables archive_t and t are identical, except for the engine
(ARCHIVE and InnoDB respectively) and the fact archive_t has the extra
field "interview_date"

I want to make sure I have this right before writing the code for the
several dozen archive tables.

For the applications where I am doing this, the first purpose of the
archive tables is to ensure there is a complete record of all data
entered, for the purposes of supporting accurate audits.  And for some,
there is an additional purpose of supporting monthly and annual summary

DuBois says that triggers can't refer to tablenames, and rightly points
out that this limits their utility.  If he is wrong (or has been made
wrong by changes made to MySQL 5 since his book went to press), then
I'd add triggers to the main tables that insert the data provided to
them into the archive tables.

Three questions:

1) What is wrong with the SQL statements above that create my triggers?

2) Is there a faster way to create the triggers I need for all several
dozen archive tables; faster than just typing similar code for each

3) Is there a better way to achieve my purpose?




7. Errore creating trigger

8. creating trigger to auto set create/modify dates

Return to mysql


Who is online

Users browsing this forum: No registered users and 64 guest