From: Anhaus, Thomas (thomas.anhaussap.com)
Date: Wed Feb 11 2004 - 07:06:28 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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

--
Dusan Kolesar
Helsinska 19
040 13 Kosice
Slovakia
e-mail : dusankolesarszm.sk
ICQ# : 160507424

--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/maxdb?unsub=thomas.anhaussap.com

--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]