What you presented is essentially the correct way for interrupting triggers in MySQL 5.1
I wrote about this before
You cannot do Dynamic SQL in triggers.
You may have to resort to coding the my_signal
like this:
CREATE PROCEDURE `my_signal`(error_type INT)
BEGIN
IF error_type = 1 THEN
UPDATE `Error: can not insert new OWNER !` set x=1;
END IF;
IF error_type = 2 THEN
UPDATE `Error: Row can not reference itself!` set x=1;
END IF;
.
.
END//
then change the trigger to look like this:
DELIMITER $$
CREATE
TRIGGER `employee_before_insert` BEFORE INSERT
ON `Employee`
FOR EACH ROW BEGIN
CASE
WHEN NEW.designation = 'OWNER' THEN
CALL my_signal(1);
WHEN NEW.SSN = NEW.MSSN THEN
CALL my_signal(2);
END CASE;
END$$
DELIMITER ;
Sorry for the clumsy programming but Signal Processing is MySQL 5.1 is horrible.
QUESTION #1
What is error in this procedure?
You seem to have some scope confusion on the variables
ANSWER TO QUESTION #1
PROBLEM : Your parameters have identical names to column names in the tables. This could produce some unpredictable results.
SOLUTION : Change the names of the parameters so that they are distinct from the column names
create procedure accountstatus
(
IN inmode varchar(27),
IN given_AccountStatus_id int,
IN given_AccountStatus varchar(255),
IN given_CreatedOn datetime,
IN given_CreatedBy varchar(255),
IN given_UpdatedOn datetime,
IN given_UpdatedBy varchar(255),
IN given_is_active bit)
Begin
if inmode = 'insert'
then
insert into accountstatus
(AccountStatus_id, Account_Status, CreatedOn, CreatedBy, UpdatedOn, UpdatedBy, is_active)
values
(given_AccountStatus_id, given_Account_Status, given_CreatedOn, given_CreatedBy, given_UpdatedOn, given_UpdatedBy, given_is_active);
end if;
/*update*/
if inmode = 'update'
then
update accountstatus acc
set
-- acc.AccountStatus_id = given_AccountStatus_id, <- Not Needed for UPDATE
acc.Account_Status = given_Account_Status,
acc.CreatedOn = given_CreatedOn,
acc.CreatedBy = given_CreatedBy,
acc.UpdatedOn = given_UpdatedOn,
acc.UpdatedBy = given_UpdatedBy,
acc.is_active = given_is_active
where
acc.AccountStatus_id = given_AccountStatus_id;
end if;
/*delete*/
if inmode = 'delete'
then
update accountstatus acc
set
-- acc.AccountStatus_id = given_AccountStatus_id, <- Not Needed for DELETE
acc.is_active = 0
where
acc.AccountStatus_id = given_AccountStatus_id;
end if;
/*select*/
if inmode = 'select'
then
select * from accountstatus acc
where
acc.AccountStatus_id = given_AccountStatus_id;
end if;
end
CAVEAT : Please note that I commented out two lines
QUESTION #2
Is there any other ways to implement this procedure?
ANSWER TO QUESTION #2
You could use triggers
QUESTION #3
How it will effect the performance of the database?
ANSWER TO QUESTION #3
Doing bulk operations can make the MySQL server process to tedious work and bog it down. Here is other posts showing how to use SQL efficiently to replace a trigger and stored procedure, why too many triggers can be bad, and how as little code as possible
EPILOGUE
The simpler the code in the Stored Procedure or Trigger, the less impact on performance, especially on bulk INSERTs, UPDATEs, and DELETEs.
Please consider the Storage Engine and its locking characteristics (using MyISAM) when using triggers and the autocommit behavior (if using InnoDB).
Best Answer
Most likely the issue is with the first dynamic query, the one you are storing into
@sql0
:You are concatenating the value of
@i
into the query, but@i
has not been defined yet at this point – so it is null. Concatenating a null with other string literals gives you a null as well. So@sql0
ends up to be a null and that is what you are attempting to execute subsequently and getting the error mentioned.My guess is you meant the
@i
to be part of the dynamic query, so it should probably go like this:that is, you need to concatenate the name of
@i
, not the value. That way the dynamic query will be storing the result ofcount(*)
into@i
.Note, though, that you may also need to declare the variable explicitly before the dynamic query. Otherwise the dynamic query will probably implicitly declare the variable at its own, nested, level, and the variable will go out of scope once the query is completed, and so the
@i
reference further in your query will still evaluate to null.