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]
