trigger in multiple loop
Hi all,
i have 4 table on mysql server.
CREATE TABLE `users` (
`userkey` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) COLLATE utf8_turkish_ci DEFAULT NULL,
PRIMARY KEY (`userkey`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_turkish_ci;
CREATE TABLE `accounts` (
`accountkey` int(11) NOT NULL AUTO_INCREMENT,
`accountname,` varchar(255) COLLATE utf8_turkish_ci DEFAULT NULL,
PRIMARY KEY (`accountkey`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_turkish_ci;
CREATE TABLE `prcesses` (
`processkey` int(11) NOT NULL AUTO_INCREMENT,
`processname` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
PRIMARY KEY (`processkey`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_turkish_ci;
CREATE TABLE `access` (
`accesskey` int(11) NOT NULL AUTO_INCREMENT,
`userid` int(11) DEFAULT NULL,
`accountid` int(11) DEFAULT NULL,
`precessid` int(11) DEFAULT NULL,
`read` enum('0','1') COLLATE utf8_turkish_ci NOT NULL DEFAULT '0',
`write` enum('0','1') COLLATE utf8_turkish_ci NOT NULL DEFAULT '0',
PRIMARY KEY (`accesskey`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_turkish_ci;
i need new trigger.
create trigger user_access after insert on users
for each row
begin
declare account_counter int;
declare process_counter int;
declare account_count int;
declare process_count int;
set process_count = select count(*) from processes;
set account_count = select count(*) from accounts;
set account_counter = 0;
loop1: loop
if account_counter < account_count then
set account_counter = account_counter+1;
set process_counter = 0;
loop2: loop
if process_counter < process_count then
set process_counter = process_counter+1;
insert into access(userid,accountid,precessid) values (new.userkey,(select accountkey from accounts limit account_counter,1),(select processkey from processes limit process_counter,1));
iterate loop2;
end if;
leave loop2;
end
end
iterate loop1;
end if;
leave loop1;
end
end
end;
where is my problem? whats true syntax?