How can I call a stored procedure - which has a returning value - within a trigger?

Database
N/A

How can I call a stored procedure - which has a returning value - within a trigger?

Can I declare any variables within a trigger to store the returning value (OUT parameter, e.g. an INTEGER) from a stored procedure, like this:

CALL stdproc(variableName);

And after that, how can I use this returning value for making decisions inside the trigger (within an IF statement)?

Thanks!

4 REPLIES
N/A

Re: How can I call a stored procedure - which has a returning value - within a trigger?

I don't think you can do that.

How about moving the conditional logic into stdproc or another SP containing a call stdproc?

Dieter

N/A

Re: How can I call a stored procedure - which has a returning value - within a trigger?

OK, now I see. It is solved by putting the conditional statement into the SP, as you suggested. Thanks!

The next step for me would be to getting this code working:

REPLACE PROCEDURE vmtest.p()
BEGIN
DECLARE var1 INTEGER;
WITH P(x,y) AS (SELECT name, e_mail FROM user_nontemp.EMP)
SELECT COUNT(*) INTO var1 FROM P;

IF (var1 > 1)
THEN ABORT 'Aborted.';
END IF;
END;

I've already tried it in a lot of ways, but it always refuses the using of the WITH clause. It is in connection with my previous topic, only in a simplified case:

http://forums.teradata.com/forum/database/problem-with-enforcing-temporal-ri-with-triggers-in-td-13-...

How about this problem?

N/A

Re: How can I call a stored procedure - which has a returning value - within a trigger?

WITH seems not to be supported within SPs, you have to write it the old way using a Derived Table:

REPLACE PROCEDURE vmtest.p()
BEGIN
DECLARE var1 INTEGER;
SELECT COUNT(*) INTO var1 FROM (SELECT name, e_mail FROM user_nontemp.EMP) as p;

IF (var1 > 1)
THEN ABORT 'Aborted.';
END IF;
END

Dieter

N/A

Re: How can I call a stored procedure - which has a returning value - within a trigger?

Hm, I guessed similar causes... The conventional way is ok, unless I try to use the TD_NORMALIZE_MEET function, which requires the WITH clause.

Interesting problem. And also a bit annoying by now... :)

They claim that:

From the aspect of integrity assurance, the best way to guarantee the referential integrity of a

table without taking advantage of a declarative standard or batch referential constraint is to

use a procedural constraint such as a set of triggers to handle inserts, updates, and deletions to

the tables in the relationship.

...

The following are suggested queries that can be used to validate temporal referential

constraints in a child table: Examples...

The examples are working fine, but not inside a trigger or a stored procedure.

(APPENDIX D from Temporal Table Support reference: http://www.info.teradata.com/edownload.cfm?itemid=102320064)