MySQL Forums
Forum List  »  Triggers

Setup trigger - newbie
Posted by: raju vora
Date: March 21, 2015 09:23AM

Afternoon All,

I am trying to set up a trigger to insert into a logging table when something is inserted or deleted from another table, but cant seem to get it to work! Help is much appriciated:

Data to be written to:
CREATE TABLE IF NOT EXISTS `heating_log` (
  `log_id` int(11) NOT NULL AUTO_INCREMENT,
  `sensor_id` varchar(15) NOT NULL,
  `date` date NOT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `current` decimal(5,3) DEFAULT NULL,
  `target` decimal(5,3) DEFAULT NULL,
  `on_off` int(1) NOT NULL COMMENT '1 signifies on, 0 is off',
  PRIMARY KEY (`log_id`),
  UNIQUE KEY `log_id` (`log_id`),
  KEY `indx_sensor` (`sensor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=ascii AUTO_INCREMENT=1 ;

ALTER TABLE `heating_log`
  ADD CONSTRAINT `fk_heating_log` FOREIGN KEY (`sensor_id`) REFERENCES `sensor_master` (`sensors`);
data being pulled from:

CREATE TABLE current (
  sensor varchar(15) NOT NULL,
  target decimal(5,3) NOT NULL,
  last_reading decimal(5,3) NOT NULL,
  `date` date NOT NULL,
  `time` time NOT NULL,
  PRIMARY KEY (sensor),
  KEY sensor (sensor)
) ENGINE=InnoDB DEFAULT CHARSET=ascii;


ALTER TABLE current
  ADD CONSTRAINT fk_sensor_current FOREIGN KEY (sensor) REFERENCES sensor_master (sensors);

and


a view:

Structure for view 'view_display'
--

CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER VIEW pi.view_display AS select pi.current.sensor AS sensor,pi.current.last_reading AS last_reading,pi.current.target AS target,pi.sensor_master.room AS room from (pi.current join pi.sensor_master) where (pi.current.sensor = pi.sensor_master.sensors);


The trigger I'm trying to use:

insert into pi.heating_log (sensor_id,date,time,current,target,on_off)
select cu.sensor, cu.date, cu.time, cu.last_reading, vd.target, 1 from pi.current cu, pi.view_display vd where  cu.sensor = new.sensor_id and vd.sensor = new.sensor_id


The value sensor or sensor_id would be something like:
28-000005958bae or 28-000005961ac1


The idea is that in the heating_log, i will write the sensorid, date, time, current value, target value and a number 1 for inserts, or 0 for deletes.

I'm using phpMyAdmin to set things up so if theres more info needed please ask, and i will do my best to provide!

Options: ReplyQuote


Subject
Views
Written By
Posted
Setup trigger - newbie
2239
March 21, 2015 09:23AM
1049
March 21, 2015 02:30PM
1033
March 21, 2015 03:01PM
1094
March 30, 2015 03:53PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.