0

I am just trying to use trigger instead of check constraint and code one but it gives me an error.

CREATE TRIGGER conflict 
ON roozane 
FOR EACH ROW 
BEGIN 
if rDate = NEW.rDate then
if NEW.rStartTime < rStartTime AND NEW.rEndTime < rEndTime then
INSERT INTO roozane (rID,rDate,rStartTime,rEndTime,rPlace,rComment,rType) values (NEW.rID,NEW.rDate,NEW.rStartTime,NEW.rEndTime,NEW.rPlace,NEW.rComment,NEW.rType);
end if
end if
END;$$ 

And error

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON roozane FOR EACH ROW BEGIN if ( rDate=NEW.rDate ) then if ( NEW.rStart' at line 2

EDIT

CREATE TRIGGER conflict BEFORE INSERT
ON roozane 
FOR EACH ROW 
BEGIN 
if rDate = NEW.rDate then
if NEW.rStartTime < rStartTime AND NEW.rEndTime < rEndTime then
INSERT INTO roozane (rID,rDate,rStartTime,rEndTime,rPlace,rComment,rType) values (NEW.rID,NEW.rDate,NEW.rStartTime,NEW.rEndTime,NEW.rPlace,NEW.rComment,NEW.rType);
end if
end if
END;$$ 

and the error

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 7

tnx for help

3
  • Where is your trigger_time and trigger_event? CREATE TRIGGER conflict BEFORE UPDATE ON roozane dev.mysql.com/doc/refman/5.5/en/create-trigger.html Commented Jul 1, 2013 at 20:43
  • yea fixed tnx but now it forwarded to INSERT command and here show syntax error Commented Jul 1, 2013 at 21:07
  • I'm sorry but I don't understand your last comment. Could you please post your updated query and the error? Commented Jul 1, 2013 at 21:21

3 Answers 3

1

You need a *trigger_time* and *trigger_event*. For example: CREATE TRIGGER conflict AFTER INSERT

Sign up to request clarification or add additional context in comments.

Comments

0

You need a semicolon after each end if to terminate those compound statements.

You don't need a semicolon after the last END, because presumably you have used DELIMITER $$ to change the statement terminator in the mysql client.

I tested the following. It did not get a syntax error, but of course I have no table called roozane so I got a different error. :-)

CREATE TRIGGER conflict BEFORE INSERT
ON roozane 
FOR EACH ROW 
BEGIN 
if rDate = NEW.rDate then
if NEW.rStartTime < rStartTime AND NEW.rEndTime < rEndTime then
INSERT INTO roozane (rID,rDate,rStartTime,rEndTime,rPlace,rComment,rType) values (NEW.rID,NEW.rDate,NEW.rStartTime,NEW.rEndTime,NEW.rPlace,NEW.rComment,NEW.rType);
end if;
end if;
END$$ 

Comments

0

You have several problems with your trigger.

As far as syntactical and logic errors go

  1. Looking at the error message apparently you didn't use DELIMITER $$ at the beggining of your script.
  2. You have three undeclared variables in your trigger rDate, rStartTime, rEndTime. If you use stored procedure level variables you need to declare them first and eventually assign values to them.
  3. As @BillKarwin mentioned in his answer you have to have semicolons at the end of each IF ... END IF; statement and you don't need semicolon after closing END of a BEGIN...END block of your trigger since you should've changed DELIMITER earlier to $$.

That being said syntactically correct version of your trigger might be following

DELIMITER $$
CREATE TRIGGER conflict 
BEFORE INSERT ON roozane 
FOR EACH ROW 
BEGIN 
  DECLARE rDate      DATE;
  DECLARE rStartTime TIME;
  DECLARE rEndTime   TIME;

  IF rDate = NEW.rDate THEN
    IF NEW.rStartTime < rStartTime AND NEW.rEndTime < rEndTime THEN
        INSERT INTO roozane (rID,rDate,rStartTime,rEndTime,rPlace,rComment,rType)
        VALUES(NEW.rID,NEW.rDate,NEW.rStartTime,NEW.rEndTime,NEW.rPlace,NEW.rComment,NEW.rType);
    END IF;
  END IF;
END$$ 

Here is SQLFiddle demo that shows that now your trigger is being successfully created but does nothing since declared variables by default have values of NULL and other values have not been assigned to them.

Here goes the most important part: event if the problem with the variables will be fixed unfortunately your trigger won't work anyway because MySql with its rather limited support for triggers doesn't allow data manipulation statements (INSERT in your case) on the same table (roozane in your case) you are attaching your trigger to.

Now, to help you to fix your trigger you need to explain what you want your trigger to check for.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.