CODEWITHSUNDEEP
mysqltriggers
Sumant
Sumant

Reputation: 964

Not able to create Trigger in mysql

Hi i have table structure like 

CREATE TABLE IF NOT EXISTS `history` (
  `order_product_id` varchar(20) NOT NULL,
  `Details` text NOT NULL,
  PRIMARY KEY (`order_product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS `order_product` (
  `order_product_id` varchar(64) NOT NULL,
  `porder_status` int(11) NOT NULL,
  PRIMARY KEY (`order_product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `order_product` (`order_product_id`, `porder_status`) VALUES
('1339789127O532877', 11),
('1339789127O532933', 2),
('1339868495O010300', 2),
('1339868495O010342', 0),
('1339869923O564839', 0);

Now i want to write a trigger which fires on updating the value of order_product.porder_status & will insert/update order_product.porder_status value to history table as 

DELIMITER $$ 
CREATE TRIGGER addtohistory 
AFTER UPDATE ON order_product 
FOR EACH ROW 
BEGIN 
INSERT INTO history (order_product_id,Details)
  values 
    (NEW.order_product_id,CONCAT_WS(':',NEW.porder_status,CURRENT_TIMESTAMP)) 
  ON DUPLICATE KEY UPDATE 
  Details = 
    CONCAT_WS(',',Details,CONCAT_WS(':',NEW.porder_status,CURRENT_TIMESTAMP));
END;$$

when i am creating trigger it is saying query executed successfully,On execution of SHOW triggers result is Nothing, So it concludes there is no trigger present in the database. I don't know where i am making mistake. Please help me to find out the bug. I am using innodb engin with MySQL Server 5.5.

Thanks in advance.

Got it.

DROP TRIGGER IF EXISTS `addtohistory`;
DELIMITER $$
CREATE TRIGGER `addtohistory` AFTER UPDATE ON `order_product`
 FOR EACH ROW BEGIN
    INSERT INTO history (order_product_id,Details) values (NEW.order_product_id,CONCAT_WS(':',NEW.porder_status,CURRENT_TIMESTAMP)) ON DUPLICATE KEY UPDATE Details = CONCAT_WS(',',Details,CONCAT_WS(':',NEW.porder_status,CURRENT_TIMESTAMP));
  END
$$
DELIMITER ;

i don't understand but i executed above expression & it works.

Upvotes: 0

Views: 1229

Answers (1)

Shlomi Noach
Shlomi Noach

Reputation: 9354

Thank you for the complete schema code.

What do you mean by saying "the trigger is not present inside the database"? I followed on your code, and then issued:

SHOW TRIGGERS \G
*************************** 1. row ***************************
             Trigger: addtohistory
               Event: UPDATE
               Table: order_product
           Statement: BEGIN INSERT INTO history (order_product_id,Details) values (NEW.order_product_id,CONCAT_WS(':',NEW.porder_status,CURRENT_TIMESTAMP)) ON DUPLICATE KEY UPDATE Details=CONCAT_WS(',',Details,CONCAT_WS(':',NEW.porder_status,CURRENT_TIMESTAMP));END
              Timing: AFTER
             Created: NULL
            sql_mode: 
             Definer: root@localhost
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: latin1_swedish_ci

So the trigger seems to be there.

Moreover, it seems to be working:

UPDATE order_product SET porder_status=123 WHERE order_product_id='1339869923O564839';
Query OK, 1 row affected (0.08 sec)
Rows matched: 1  Changed: 1  Warnings: 0

SELECT * FROM history;
+-------------------+-------------------------+
| order_product_id  | Details                 |
+-------------------+-------------------------+
| 1339869923O564839 | 123:2012-07-14 15:33:05 |
+-------------------+-------------------------+

So, everything seems fine! Exactly what query do you issue that doesn't work?

Upvotes: 2

Related Questions