Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Location
    Arkansas
    Posts
    15

    Unanswered: The CREATE TABLE statement is not allowed within a trigger

    I have a stored procedure that uses temp tables. I am getting this error when I call the procedure from the trigger.

    Is this doable?

    Thanks
    LJ
    Hope is the feeling that the feeling that you have will not last very long.

  2. #2
    Join Date
    Sep 2003
    Posts
    522
    yes it is, but instead of explicitly doing a create, do a select...into #tmp from...

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Why are you doing that?

    Post the trigger so we can check it out...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Oct 2003
    Location
    Arkansas
    Posts
    15
    Sorry for the delay in getting back to the post.

    Why?

    I have created a duplicate Table (TableName_Audit) for auditing purposes. Each time a field is changed in the Origonal I record the origonal record and the action in the Audit table using a IUD trigger

    I am doing this for 15 tables.

    I am using this same trigger to call this stored procedure (Notification posted @ end). This stored Proc uses the TableName, IDField and ID# to get the current values for the record and the previous value and format a e-mail message to e-mail to the proper authorities.

    Since there are 15 tables I want to do this dynamically instead of hardcoding all the formatting into each trigger. I am creating the tables dynamically

    As far as the trigger


    exec Notification 'Contacts', 'ContactID',@ContactID

    is the line in the trigger.

    Procedure:

    CREATE PROCEDURE [DBO].[Notification] @TN Varchar(25), @IDF Varchar(25), @IDN Integer
    As

    Declare -- Audit Variables
    @TableName as Varchar(25),
    @SQLstr as varchar(1000),
    @ColumnName as Varchar(25),
    @IdField as VarChar(25),
    @Data as varchar(100),
    @NewData as varchar(100),@OldData as varchar(100),
    @IDNo as integer,
    @i as integer,
    -- EMail Notification Variables
    @Addresses as varchar(250), @ToNext as Varchar(100),
    @strOutput as Varchar(2500),
    @Message as Varchar(2500), @MessageBeg as VarChar(1000), @MessageEnd as varchar(1000),
    @Subject as varchar(150)

    -- This Procedure also nees some error handling added
    -- These Values Will be passes into the procedure

    Select @TableName =@TN
    -- The IDField could be found dynamically (since this is the PK Field on the @TableName Table)
    -- but the additional code makes the process slower.
    Select @IDField = @IDF
    Select @IDNo =@IDN

    --Create Table #NewData (ID int,ColumnName varchar(25),Data varchar(100))
    --Create Table #OldData (ID int,ColumnName varchar(25),Data varchar(100))

    set @SQLstr = 'Declare ColumnList Cursor for
    select Ordinal_position, Column_Name from Information_Schema.Columns where Table_Name = ' + char(39) + @TableName + char(39)

    Print @SQLstr
    exec(@SQLstr)

    Open ColumnList

    -- Set up the New and Old Data Tables
    -- Use cursors @ the end to format output for ease of trouble shooting
    Fetch Next From ColumnList into @i, @ColumnName
    Print convert(char(5),'Col#') + ' ColumnName'
    While @@Fetch_Status = 0
    Begin
    select @SQLstr = 'Select into #NewData (ID, ColumnName, Data) ' +
    -- select @SQLstr = 'insert into #NewData (ID, ColumnName, Data) ' +
    '(select top 1 ' + convert(varchar(10),@i) + ', '+ char(39) + @ColumnName + char(39) + ', ' +
    @ColumnName + ' from ' + @TableName + ' where ' + @IDField + ' = ' + convert(varchar(6),@IDNo)+ ')'
    Print @SQLstr
    exec(@SQLstr)

    select @SQLstr = 'select into #OldData (ID, ColumnName, Data) ' +
    -- select @SQLstr = 'insert into #OldData (ID, ColumnName, Data) ' +
    '(select top 1 ' + convert(varchar(10),@i) + ', '+ char(39) + @ColumnName + char(39) + ', ' +
    @ColumnName + ' from ' + @TableName + ' where ' + @IDField + ' = ' + convert(varchar(6),@IDNo)+ ' and Action <> ''C'')'
    Print @SQLstr
    exec(@SQLstr)

    Fetch Next From ColumnList into @i, @ColumnName

    end

    Close ColumnList
    Deallocate ColumnList

    Select * from #NewData
    Select * from #OldData

    -- Format the New and old columns for strOutput
    Declare NewVals Cursor for
    select ColumnName, Data from #NewData

    Declare OldVals Cursor for
    select Data from #OldData

    Open NewVals
    Open OldVals

    fetch Next From NewVals into @ColumnName, @NewData
    fetch Next From OldVals into @OldData

    select @strOutput = convert(char(20),'ColumnName') + convert(char(20),'New Value') + 'OldValue' + char(10)
    select @strOutput = @strOutput + '-----------------------------------------------------------------' + Char(10)

    While @@Fetch_Status = 0
    Begin

    select @strOutput = @strOutput + convert(char(20),@ColumnName) + Coalesce(convert(char(20),@NewData),'null')
    + Coalesce(@OldData,'null') + char(10)

    fetch Next From NewVals into @ColumnName, @NewData
    fetch Next From OldVals into @OldData

    End

    Drop table #NewData
    Drop table #OldData

    Close NewVals
    Close OldVals

    Deallocate NewVals
    Deallocate OldVals


    -- Get E-Mail Addresses
    -- Format E-Mail Body
    -- Insert E-Mail in Pending E-Mail Table

    if exists (select email from Contacts where email is not null and [Function] = 'DBA')
    begin
    Select @Subject = (@TableName + ' Information in Cross Ref DB Has Changed')

    Select @MessageBeg = ('The Cross Reference DB has been revised for ' + @TableName + '.' + Char(10) +
    'Please goto <http://xxx.xx.xxx.xx/scss/CrossRef/default.asp> to see the changes ' + Char(10) +
    'or see the changes below.' + char(10) + Char(10))

    Select @MessageEnd = ('Thank You.' + char(10) + 'Tech Delivery.' + Char(10) + 'SOC - Pine Bluff, AR' + Char(10) +
    'This is a automated E-Mail if you have received this E-Mail in error, please contact: Polly Irons <mailto:someone@Somewhere.com>.' )

    select @ToNext = (select Email from Contacts where [Function] = 'DBA') + ';'
    Select @Addresses = @ToNext
    Select @ToNext = (select Email from Contacts where [Function] = 'WebMaster') + ';'
    Select @Addresses = @Addresses + ';' + @ToNext
    Select @ToNext = 'jander9@Somewhere.com'
    Select @Addresses = @Addresses + ';' + @ToNext

    -- Create this table for future use with multiple e-mail addresses.

    Create Table #Addresses (Address varchar(100))
    select @SQLstr = 'insert into #Addresses (Address) ' +
    '(Select Distinct Email ' +
    'from contacts C, ' + @TableName + ' TN ' +
    'where C.ContactID = TN.contactID and TN.' + @IDField + ' = ' + convert(varchar(10),@IDNo) +
    ')'

    --Print @SQLstr
    exec(@SQLstr)
    -- This should use a cursor and loop if there is more than 1 Address
    select @Addresses = (Select * from #Addresses)

    Drop table #Addresses

    Select @Message = @MessageBeg + @strOutput + char(10) + @MessageEnd

    insert Pending_EMail (Date_Time_Entered,To_List, Subject, Message_Body) values (GetDate(), @Addresses, @subject, @Message)

    end
    GO
    Last edited by LittleJonny; 12-08-03 at 13:02.
    Hope is the feeling that the feeling that you have will not last very long.

  5. #5
    Join Date
    Oct 2003
    Location
    Arkansas
    Posts
    15
    ms_sql_dba

    Your suggestion gets me around the Create table error but I still have the problem with dropping the table before I exit the Proc.

    Thanks
    LJ
    Hope is the feeling that the feeling that you have will not last very long.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •