MySQL Forums
Forum List  »  Informix

Dynamic SQL is not allowed
Posted by: aftab khan
Date: November 13, 2006 06:04AM

I have to choose the table name on fly in the trigger and then pass dynamic sql to
Stored procedure which make use of PREPARE and EXECUTE statements.I know that triggers can't have PREPARE and EXECUTE statement, but STORED PROCEDURES can make use of PREPARE AND EXECUTE. So thats why I had put PREPARE AND EXECUTE statements in Stored procedures, which I call from trigger, I recieve the following error.

\\\\\\\\\\\\\
Dynamic SQL support is not enable in triggers and functions
\\\\\\\\\\\

Can any body help me...


CREATE TRIGGER trigger1 after INSERT ON cdr
FOR EACH ROW

BEGIN

DECLARE id BIGINT;
DECLARE vname VARCHAR(200);
DECLARE destination VARCHAR(200) DEFAULT 'null';
DECLARE rows1 BIGINT DEFAULT 0;
DECLARE sql1 VARCHAR(2000);

SELECT v.vendorid,v.name, ve.DestinationID INTO id, vname, destination
FROM (select name, v.VendorID from vendor v natural join vendorendpoints where endpoint=NEW.Field27) v NATURAL JOIN (SELECT * FROM vendorextraction order by length desc) ve
WHERE
substr(NEW.Field8 , 1 ,ve.length)=substr(CONCAT(ve.Prefix,ve.OperatorList),1,ve.length) limit 0,1;

SET @@sql_mode='ansi';
SET sql1 =CONCAT('insert into `',CONCAT(vname,id),'` values(',id,',"',vname,'","',NEW.Field5,'","',NEW.Field35,'","',NEW.Field12,'","',NEW.Field8,'","',NEW.Field0,'")');

IF (id >0) THEN
call testing(sql1);
ELSE
SET sql1 =CONCAT('insert into `unknown` values(',id,',"',vname,'","',NEW.Field5,'","',NEW.Field35,'","',NEW.Field12,'","',NEW.Field8,'","',NEW.Field0,'")');
call testing(sql1);
END IF;

END

/////////////

CREATE PROCEDURE testing (IN param1 VARCHAR(3000))
BEGIN
SET @s =param1;
PREPARE stmt1 FROM @s;
EXECUTE stmt1;
END

Options: ReplyQuote


Subject
Views
Written By
Posted
Dynamic SQL is not allowed
9614
November 13, 2006 06:04AM


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.