Variables As Query Results

[Jim Dandy]

Greetings,

I am trying to create a trigger that will populate a second table with the time difference between the most recent timestamps for sent (type = 0) and received (type = 1) files with matching IDs (or MDNs). Initially it will check for an ID match, if there is no match it will do nothing. I am aware that I can still get false positives, but for it's temporary purposes it will suffice. The queries work but I am having difficulty with the variables to implement my trigger. I had little luck in finding articles relevant to what I am attempting to do. Any advice on implementation or syntax would be greatly appreciated.

Thank you.

CREATE TABLE `status` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` binary(1) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `mdn` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

+----+------+---------------------+-----------+
| id | type | timestamp           | mdn       |
+----+------+---------------------+-----------+
|  1 | 0    | 2011-09-02 16:49:45 | 123456789 |
|  2 | 1    | 2011-09-02 17:00:36 | 123456789 |
|  3 | 0    | 2011-09-06 11:14:21 | 321483082 |
|  4 | 1    | 2011-09-06 14:57:24 | 321483082 |
|  5 | 0    | 2011-09-06 15:04:36 | 333444555 |
|  6 | 1    | 2011-09-06 15:04:39 | 333444555 |
|  7 | 0    | 2011-09-06 15:27:35 | 111222333 |
|  8 | 1    | 2011-09-06 15:30:02 | 111222333 |
|  9 | 0    | 2011-09-06 15:40:31 | 777888999 |
| 10 | 1    | 2011-09-06 15:40:33 | 777888999 |
+----+------+---------------------+-----------+

CREATE TABLE `diff` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sent_id` int(11) DEFAULT NULL,
  `rcvd_id` int(11) DEFAULT NULL,
  `time_diff` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

+----+-----------+-----------+-----------+
| id | sent_id   | rcvd_id   | time_diff |
+----+-----------+-----------+-----------+
|  1 | 321483082 | 321483082 |     13383 |
|  2 | 321483082 | 321483082 |     13383 |
|  3 | 321483082 | 321483082 |     13383 |
|  4 | 321483082 | 321483082 |     13383 |
|  5 | 321483082 | 321483082 |     13383 |
|  6 | 333444555 | 333444555 |         3 |
|  7 | 111222333 |      NULL |      NULL |
|  8 | 111222333 | 111222333 |       147 |
|  9 | 777888999 | 777888999 |         2 |
+----+-----------+-----------+-----------+


delimiter $$

 CREATE TRIGGER v_api_trigger
 AFTER insert ON v_api.status
 FOR EACH ROW

DECLARE check VARCHAR;
SET check = (SELECT timestampdiff (SECOND, (SELECT timestamp FROM v_api.status WHERE mdn = (select mdn from v_api.status ORDER BY timestamp DESC LIMIT 1) AND type = 0),(SELECT timestamp FROM v_api.status where mdn = (select mdn from v_api.status ORDER BY timestamp DESC LIMIT 1) AND type = 1)));

DECLARE sentmdn INT;
SET sentmdn = (SELECT mdn FROM v_api.status WHERE mdn = (select mdn from v_api.status ORDER BY timestamp DESC LIMIT 1) AND type = 0);

DECLARE rcvdmdn INT;
SET rcvdmdn = (SELECT mdn FROM v_api.status where mdn = (select mdn from v_api.status ORDER BY timestamp DESC LIMIT 1) AND type = 1);

DECLARE timedifference INT;
SET timedifference = (SELECT timestampdiff (SECOND,(SELECT timestamp FROM v_api.status WHERE mdn = (select mdn from v_api.status ORDER BY timestamp DESC LIMIT 1) AND type = 0),(SELECT timestamp FROM v_api.status where mdn = (select mdn from v_api.status ORDER BY timestamp DESC LIMIT 1) AND type = 1)));

    IF check IS NULL THEN 
    
    ELSE

   INSERT INTO v_api.diff (sent_id,rcvd_id,time_diff) 
    VALUES (sentmdn,rcvdmdn,timedifference)
       
    END IF;

  END$$

delimiter ;

Error message when attempting to implement trigger.

ERROR 1064 (42000): 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 'DECLARE check VARCHAR;
SET check = (SELECT timestampdiff (SECOND, (SELECT time' at line 5



Edited 3 time(s). Last edit at 09/07/2011 01:05PM by Jim Dandy.