On Wed, 11 Feb 2004 14:06:28 +0100, Anhaus, Thomas <[EMAIL PROTECTED]>
wrote:
Dusan Kolesar wrote :
Hello,
I want to insert row into more then one table.
List of tabels into which I want to insert is in the next table:
CREATE TABLE "ADMIN"."EVENT_TABLES"
(
"TB_NAME" Varchar (30) ASCII
)
I want to do it inside the trigger.
I don't know if is it posible at all, because in the manual is written:
INSERT [INTO] <table_name> ...
<table_name> ::= [<owner>.]<identifier>
There is nothing like table name can be a variabe.
Thank you very much.
Dusan
It seems that you need dynamic SQL inside your trigger, i.e. the insert
statements
for the additional tables have to be constructed at runtime of the
trigger. Example :
Assuming a table TEST has 2 columns Col1 integer and Col2 char(20)
CREATE TRIGGER INS_TEST FOR TEST AFTER INSERT EXECUTE (
VAR
tableName char(32);
insertStmt char(200);
SELECT TB_NAME FROM ADMIN"."EVENT_TABLES;
WHILE $RC = 0 DO
BEGIN
FETCH INTO :tableName;
IF $RC = 0
THEN
BEGIN
insertStmt = 'INSERT INTO ' || tableName || ' VALUES (' ||
CHR(Col1) || ',''' || col2 || ''')';
execute insertStmt;
END;
END;
IF $RC <> 100 THEN STOP (-31001, 'unexpected error ' || chr($rc));
)
Regards,
Thomas
This is exactly what I need.
Thank you.
Dusan
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
