Re: Assistance with trigger

[Machiel Richards]

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

[Machiel Richards]

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.

[shawn l.green]

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.

[Michael Vaughan]

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

[Neil Tompkins]

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

Re: Editing existing Trigger MySQL 5.6

[hsv]

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

Editing existing Trigger MySQL 5.6

[Neil Tompkins]

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

Can not add trigger

[aaronrus]

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.

[Andrew Moore]

Is there white space at the end of the trigger name?

A
On Feb 20, 2012 10:04 PM, "Brent Clark"  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

[Karen Abgarian]

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

Re: trick trigger

[Claudio Nanni]

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

Re: trick trigger

[Shawn Green (MySQL)]

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

trick trigger

[John G. Heim]

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: Mysql Trigger

[Peter Brawley]

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: Mysql Trigger

[vishesh kumar]

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 wrote:

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

[Reindl Harald]

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

Mysql Trigger

[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
-- 
http://linuxmantra.com

Re: trigger-dumping

[Peter Brawley]

 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

Re: trigger-dumping

[S�ndor Hal�sz]

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

[hsv]

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?

[Wagner Bianchi]

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 

> 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 
>
> 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?

[Jerry Schwartz]

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 

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?

[Jerry Schwartz]

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  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?

[Wagner Bianchi]

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 

> 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?

[Michael Dykman]

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

Trigger?

[Jerry Schwartz]

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: AFTER Delete Trigger question

[João Cândido de Souza Neto]

As far as I know, you can´t change data on the same table in triggers.

"Kevin Labecot"  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

   

AFTER Delete Trigger question

[Kevin Labecot]

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: ERROR 1442 (HY000) when delete inside trigger statement

[viraj]

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

Re: ERROR 1442 (HY000) when delete inside trigger statement

[Jesper Wisborg Krogh]

--- Original Message ---
> From: viraj 
> 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 1442 (HY000) when delete inside trigger statement

[viraj]

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 in running trigger

[Ananda Kumar]

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
>

Re: Error in running trigger

[Krishna Chandra Prajapati]

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

[Ananda Kumar]

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

Error in running trigger

[Jeetendra Ranjan]

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 while creating trigger

[Peter Brawley]

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


  

error while creating trigger

[Jeetendra Ranjan]

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: trigger

[Phil]

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

RE: trigger

[Gavin Towey]

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

[Gavin Towey]

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

[Phil]

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

[Michael Dykman]

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

trigger

[Stefan Onken]

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: Error - "Select Column Not Found Within Trigger"

[Dan Saul]

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  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  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',
> >

Re: Error - "Select Column Not Found Within Trigger"

[Michael Dykman]

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

Error - "Select Column Not Found Within Trigger"

[Dan Saul]

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: Another Trigger Question!

[Dan Nelson]

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

Another Trigger Question!

[Marcel Grandemange]

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: Trigger and "Warning: #1265 Data truncated"

[Keith Edmunds]

> 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

Re: Trigger and "Warning: #1265 Data truncated"

[Walter Heck - OlinData.com]

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 Edmunds 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 and "Warning: #1265 Data truncated"

[Keith Edmunds]

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 working with server 5.0.51 but not 5.0.22

[Rolando Edwards]

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

Re: Trigger working with server 5.0.51 but not 5.0.22

[Mattia Merzi]

2009/5/13 Mauricio Tellez :
> 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

Trigger working with server 5.0.51 but not 5.0.22

[Mauricio Tellez]

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

Trigger causes error in binlog

[Olaf Stein]

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

[Martijn Tonies]

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]

Re: Trying to Create a Trigger

[Andy Shellam]

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]

Re: Trying to Create a Trigger

[Wm Mussatto]

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

[Martijn Tonies]

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

[Peter Brawley]

>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

[Lola J. Lee Beno]

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

[Andy Shellam]

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

[Lola J. Lee Beno]

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

[Martijn Tonies]

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

[Lola J. Lee Beno]

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

[Lola J. Lee Beno]

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

[Jim Lyons]

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

[Martijn Tonies]

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

[Lola J. Lee Beno]

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

[David Giragosian]

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

Trying to Create a Trigger

[Lola J. Lee Beno]

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]

how to create a trigger

[Uwe Kiewel]

-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]

Re: trigger that calls a webservice??

[Antony T Curtis]

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 that calls a webservice??

[Mark Leith]

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??

[James]

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]

trigger that calls a webservice??

[robert rottermann]

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 problem

[Paul DuBois]

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]

Re: Trigger problem

[Rob Wultsch]

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]

Trigger problem

[rustam ershtukaev]

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

DISABLE TRIGGER alternative

[Olaf Stein]

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]

RE: trigger question..

[bruce]

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: trigger question..

[joe]

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]

trigger question..

[bruce]

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: before insert trigger

[Rolando Edwards]

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

[Ed Reed]

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...

[Martijn Tonies]

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...

[bruce]

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

[bruce]

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]

trigger/cron process questions...

[bruce]

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

[Scott]

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]

Re: Trigger problem

[Lucky Wijaya]

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

[David Schneider-Joseph]

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]

Re: Trigger problem

[Lucky Wijaya]

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 problem

[David Schneider-Joseph]

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]

Trigger problem

[Lucky Wijaya]

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

[Martijn Tonies]

>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

[Patricio A. Bruna]

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

[Martijn Tonies]

>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]

Re: Trigger

[Patricio A. Bruna]

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.

- "Martijn Tonies" <[EMAIL PROTECTED]> escribió:
> >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]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Trigger

[Martijn Tonies]

>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]