subject:"Trigger problem"

Re: Trigger problem

2008-05-25 Thread 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]

Trigger problem

2008-05-15 Thread 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 :)

Re: Trigger problem

2008-05-15 Thread 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]

Re: Trigger problem

2007-11-13 Thread 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

2007-11-08 Thread 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

2007-11-07 Thread 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]

Trigger problem

2007-11-06 Thread 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 problem

2007-11-06 Thread 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]

Re: Trigger problem

2007-11-06 Thread 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

Trigger problem

Re: Trigger problem

Re: Trigger problem

Re: Trigger problem

Re: Trigger problem

Trigger problem

Re: Trigger problem

Re: Trigger problem

9 matches

Site Navigation

Mail list logo

Footer information