Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
create trigger check_td before 
for each row begin

declare v_code_ID varchar(25);
declare c_Worker1 cursor for select code_ID from Worker1;
declare c_Worker2 cursor for select code_ID from Worker2;

if inserting or updating Worker1 then
  open c_Worker2;
  fetch c_Worker2 into v_code_ID;

while c_Worker2%found loop
if v_code_ID = :new.code_ID then

  SIGNAL SQLSTATE '-1'
        SET MESSAGE_TEXT = 'Error A.';

end if;
fetch c_Worker2 into v_code_ID;
end loop;

close c_Worker2;
end if;

if inserting or updating Worker2 then
open c_Worker1;
fetch c_Worker1 into v_code_ID;

while c_Worker1%found loop
if v_code_ID = :new.code_ID then

  SIGNAL SQLSTATE '-1'
        SET MESSAGE_TEXT = 'Error B.';

end if;
fetch c_Worker1 into v_code_ID;
end loop;

close c_Worker1;
end if;

end;

I come from Oracle databases and I'm trying to get this MySQL Trigger working. The error says that the problem is in the 4th line (or around) but after searching, I found that this way is the correct way to declare varchar variables in MySQL.

Can somebody give me a hand? It's slightly difficult because some syntax changes a bit.

Thank you very much, any help is appreciated.

share|improve this question

1 Answer 1

  1. The way of declaring VARCHAR is correct
  2. The error is that you have to specify the table, and the action. i.e. ON worker1 BEFORE {INSERT|UPDATE|DELETE}
  3. "if inserting or updating Worker1 then" I think you need two triggers if you want the action to be performed BEFORE INERT and BEFORE UPDATE
  4. Change the delimiter, and end your trigger with it.
  5. If the provided code is not meant to be pseudopod, I recommend reading about MySQL triggers before start coding them.
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.