MySQL Forums
Forum List  »  Triggers

trigger in multiple loop
Posted by: Mehmet TEPE
Date: May 11, 2015 08:12AM

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?

Options: ReplyQuote


Subject
Views
Written By
Posted
trigger in multiple loop
2425
May 11, 2015 08:12AM
893
May 11, 2015 11:00AM


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.