Trigger to add field 1

[screamager]

Hello,

How would i create a trigger that adds a column to one table when a row is inserted into another table?

Thanks

 

[bperry]

It sounds like you want to put the ALTER TABLE command into your trigger. Here's the example:

ALTER TABLE myTable
ADD NewColumn CHAR(20) NOT NULL

 

[screamager]

Thanks a lot. To expand on the question...if I add a row to a table called USER_INDEX I get an unique IndexID (primary key). How can I pass the indexID to the second table and name the new field indexXXX where XXX is the IndexID from the first table?

 

[bperry]

Instead of issuing the cmd directly, you may have to use dynamic sql. This involves building up a string and then executing the string.

Something like this:
declare @newColName varchar(20)
set @newColName = 'INDEX' + 'xxxx' -- or whatever

declare @sqlcmd varchar(100)
set @sqlcmd
= 'alter table myTable ADD '
+ @newColName
+ ' CHAR(20) NOT NULL'

sp_executesql (@sqlcmd)

 

[tlbroadbent]

I'd like to know what the purpose of adding a column to a table every time a row is added to another table. What data will these columns hold?

This doesn't seem to be good database or program design to me. I've worked in databases for over twenty years and never seen such a requirement. I envision all manner of problems with this process. One immediate problem is the 1024 columns per table and 8060 bytes per row limits in SQL Server. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[bperry]

screamager:
Pay attention to the response from tbroadbent; that's a very sensible and pertinent suggestion.


tbroadbent:
(imagine how strange I felt proposing dynamic sql.)