MySQL Forums
Forum List  »  Quality Assurance

SQL Command truncated
Posted by: Hans Ari
Date: March 12, 2011 04:05AM

I wonder why this is happened, I want to figure out how to resolve this problem.

I develop an application using .Net 2005, and connect to MySQL server using ADO.net, connect as root.
I want to create some tables and triggers, but this is what I found.

Here's the problem :
I create a table named TblUser using SQL statement :
CREATE TABLE `workshop`.`TblUser`
(`userID` VARCHAR(10) NOT NULL DEFAULT '',
`username` VARCHAR(50) NOT NULL DEFAULT '',
`typeID` INT UNSIGNED NOT NULL DEFAULT 0,
`userPwd` VARCHAR(255) NOT NULL DEFAULT '',
`pwdHint` VARCHAR(255) NOT NULL DEFAULT '',
`userStatus` INT UNSIGNED NOT NULL DEFAULT 1,
`logID` INT UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY(`userID`))ENGINE=MyIsam DEFAULT CHARSET=latin1;
The SQL statement executed successfully.

Next, I want to create a trigger after updating TblUser.userID using SQL statement :
CREATE TRIGGER `workshop`.`TblUser_after_update`
AFTER UPDATE ON TblUser
FOR EACH ROW BEGIN
UPDATE TblUserLog SET userID=NEW.userID WHERE userID=OLD.userID
UPDATE TblArrival SET acceptedBy=NEW.userID WHERE acceptedBy=OLD.userID
UPDATE TblAssignment SET assignedBy=NEW.userID WHERE assignedBy=OLD.userID
UPDATE TblAssignmentCompletion SET acceptedBy=NEW.userID WHERE acceptedBy=OLD.userID
UPDATE TblLeaving SET approvedBy=NEW.userID WHERE approvedBy=OLD.userID
END;
But, the statement produced an error :
ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.0.24-community-nt]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 'UPDATE TblUserLog SET userID=NEW.userID WHERE userID=OLD.userID
UPDATE TblArriv' at line 4

I tried it in MySQL Query Browser, and I had the same error too.

Can anybody help me ? I don't know whether this is a bug, or there is an error in my statement.
I'm sorry for my english, thanks for your intention.

Options: ReplyQuote


Subject
Views
Written By
Posted
SQL Command truncated
2141
March 12, 2011 04:05AM


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.