Error : create table statement is not allowed within trigger

Error : create table statement is not allowed within trigger

Post by john william » Sat, 26 Oct 2002 03:16:19



What am I missing? I have a trigger for an insert and that
fires a stored procedure. The stored procedure fails in an
attempt to create a temporary table. The following error
is displayed.

The CREATE TABLE statement is not allowed within a
trigger.

In looking at books online, I do not see this as a illegal
command for a trigger. Is it illegal or is my syntax
invalid?

A section of the stored procedure is provided below.

Thanks in advance for any suggestions.

   CREATE PROCEDURE [SP_SEND_SCM_MAIL] AS
   BEGIN









   CREATE TABLE #texttab (
        Item varchar(50),
        ItemDescrption varchar(75),
        UnitPrice varchar(10),                  
        OrderQty varchar(10),          
        ItemTotalCost varchar(10))

 
 
 

Error : create table statement is not allowed within trigger

Post by Anith Se » Sat, 26 Oct 2002 03:31:23


Could not reproduce your problem. Can you post some more
details regarding this? How are you calling your Stored Proc?
Can you post your trigger code here? Also post a simplified
version of the table DDLs & sample data so that this can be
reproduced.

--
- Anith

 
 
 

Error : create table statement is not allowed within trigger

Post by john william » Sat, 26 Oct 2002 04:50:34


Anith,
In order to provide the information, I created a some
simpler test tables to recreate the problem. We are
running SQL 7. When I insert a record, I receive the same
error.
Also, if I place the code directly into the trigger, it
fails on the check syntax with the error.

Re: Error : create table statement is not allowed within
trigger

The table name
   TABLE1
      field1 char (10)

   trigger
   CREATE TRIGGER [Table1_create] ON [TABLE1]
   FOR INSERT
      exec CREATE_TABLE

   stored procedure
   CREATE PROCEDURE [TABLE1_create] AS
   BEGIN
      CREATE TABLE #texttab(
       Item varchar(50),
       ItemDescrption varchar(75),
       UnitPrice varchar(10),
       OrderQty varchar(10),
       ItemTotalCost varchar(10))
   END    

Thanks for taking a look.

Quote:>-----Original Message-----
>Could not reproduce your problem. Can you post some more
>details regarding this? How are you calling your Stored
Proc?
>Can you post your trigger code here? Also post a
simplified
>version of the table DDLs & sample data so that this can
be
>reproduced.

>--
>- Anith

>.