March 4, 2004 at 6:55 am
I have a procedure as a trigger for a field , that checks the existence of a table in another database and should create the table if it does not exist. The problem is with the select statement that checks sysobjects of the other database to see if the table eixsts. While it works just as it is supposed to in query analyzer, within the trigger code it returns the table ID as 0(zero) even if the table exists.
I use the select statement to create the table instead of create table since the latter cannot be used by triggers and that works fine.
The code is as follows. Any help would be appreciated.
CREATE TRIGGER test ON [dbo].[Table1]
after INSERT
AS
declare @fld2 as integer
declare @tablename as varchar(50)
declare @myInt int
if update (fld2)
begin
select @fld2 = fld2 from inserted
print @fld2
set @tablename = ' tblFilenames_CE' +cast ( @fld2 as varchar)
print @tablename
end
select @myInt = 0
select @myInt = id
from volume_control..sysobjects
where xtype = 'U' and name = @tablename
print 'id:'
print @myInt ----- problem here - always returns zero in the trigger
if @myint = 0
begin
print 'will create table : ' + @tablename
exec('select * into volume_control.dbo.tblFilenames_CE'+@fld2 + ' from volume_template')
end
March 4, 2004 at 7:45 am
Correct me if I am wrong but I see a lot of probelms in that code:
1. select @fld2 = fld2 from inserted
(What happens when more than one record is inserted)
2. what if fld2 is null (table name =null)
3. set @tablename = ' tblFilenames_CE' +cast ( @fld2 as varchar)
you are creating a new table per value, is that what you want ?
other than that the trigger should fire accordingly
* Noel
March 4, 2004 at 7:55 am
1. There is only one record inserted at any time. It is a small table updated once with one record in a month or so.
2. fld2 cannot have null values.
3. yes, I want a new table for every new value of fld2.
The big problem that I have is that the check of table existence from the other database fails with a return Id of zero even when the table exists.
March 4, 2004 at 8:23 am
Your error is here :
set @tablename = ' tblFilenames_CE' +cast ( @fld2 as varchar)
You have a leading space remove it
and you get what you asked for
* Noel
March 4, 2004 at 8:34 am
That was very careless of me. Thanku, that worked.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply