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!