Re: Triggers

[John Loeffler]

Thanks Ronan!

I've updated my code but having difficulty conceptualizing "old" and "new' on the tables and how they interact.

drop trigger if exists tsu_resp;
create trigger tsu_resp after update on respondent for each row
update job set ts=current_timestamp;

CREATE TABLE `job` (
`jobnum` int(11) NOT NULL,
`job_name` varchar(100) NOT NULL,
`company` varchar(100) NOT NULL,
`client` varchar(50) default NULL,
`ts` timestamp default NULL,
PRIMARY KEY (`jobnum`)) engine=innodb;

CREATE TABLE `respondent` (
`resp_id` bigint(20) NOT NULL,
`stprov_id` int(11) default NULL,
`country_id` int(11) default NULL,
`jobnum` int(11) NOT NULL,
PRIMARY KEY (`resp_id`),
FOREIGN KEY (`jobnum`) references job (`jobnum`)) engine=innodb;


My challenge is that I only want the appropriate jobs current_timestamp updated. Ex., if an UPDATE is made to job number 101 I don't want the current_timestamp(s) updating jobs 102, 103, etc.

Again, any help is much appreciated.