Click here to Skip to main content
14,974,631 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Is it possible to allow dynamic SQL inside a trigger or not ?

if it is not allowed, is there a way to allow it ?


thnx in advance
Posted
Comments
J4amieC 7-Jun-10 6:06am
   
Instead of asking "is it possible?" have you actually tried?

   
Comments
KaRaMiLo 7-Jun-10 7:14am
   
hmm , i know that but this link still don't illustrate Is it ok to use Dynamic SQL with TRIGGERS !!
I'm fairly certain that you can do whatever you want inside triggers (for the most part). But I would caution using triggers in the first place. They tend to be performance hogs and can sometimes lead to confusion when debugging complex databases.

As for the dynamic SQL, if you're going to use it, they are also potential performance, security, and maintenance issues. Because they are generated live each execution, you won't have any benefits from the SQL engine being able to save its execution plan. Secondly, dynamic SQL opens you up potentially for SQL injection unless you're careful about how you use your inputs. Finally, it'll be that much harder to debug because you'll be looking at code writing code.

You can do it, but just make sure you're aware of the potential influences that decision will have on the system around it. I would make sure a trigger (and even dynamic SQL) is even necessary before embarking down that road. I would personally lock down the system to only use stored procedures and have them execute the 'dynamic code' over using a trigger.

But, if you must, here is a link that might help[^].
   
Comments
KaRaMiLo 9-Jun-10 8:33am
   
thnx for ur answer , i know all of that but i want to test this case exactly locally on my pc to do it , it is just an alternative to a solution i already got using stored procedures.
KaRaMiLo 9-Jun-10 8:42am
   
here is what am doing , but i want to allow dynamic sql upon UPDATE ! :


DELIMITER //
CREATE TRIGGER update_prefix BEFORE UPDATE ON prefix_info FOR EACH ROW
BEGIN
SET @curr_number=0,@num_record=0,@temp=1,@cond=0;
SELECT count(subscriber_id)curr_numb INTO @num_records FROM account_info ;

while @temp <= @num_record do


if @temp <= 1 then
SELECT number INTO @curr_number FROM account_info WHERE NEW.prefix_id = account_info.sub_prefix_id limit 1;
UPDATE account_info SET account_info.subscriber_id = concat(NEW.prefix_value,@curr_number) WHERE NEW.prefix_id = account_info.sub_prefix_id ;
SET @temp=@temp+1;
end if;

if @temp > 1 then
SET @cond = @temp-1 ;
SET @cond = concat(@cond,",1");
SET @cond = concat("SELECT number INTO @curr_number FROM account_info WHERE NEW.prefix_id=account_info.sub_prefix_id limit ",@cond);
PREPARE stmt FROM @cond;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
UPDATE account_info SET account_info.subscriber_id = concat(NEW.prefix_value,@curr_number) WHERE NEW.prefix_id = account_info.sub_prefix_id ;
SET @temp=@temp+1;
end if;


end while;

END ;//

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900