asked
how to update column name by using row value of another table row is updated
I have two tables 'MainTable' and 'ColumnsTable'.In main table i have two columns 'ID,Description', In 'Columns Table' at first i have one column i have 'ID' column. Then i have created a trigger on MainTable such that when a row is inserted in 'MainTable', by using 'Description Column' row value is added as column to 'ColumnsTable'. Now i want next level that, when a row is updated in 'MainTable' of 'Description Column' with particular value Then the column name in 'ColumnsTable' should get renamed with new row name in 'MainTable' of 'Description Column' The code i have written for Insert Trigger is as follows ALTER TRIGGER trgInsert ON MainTable AFTER INSERT AS DECLARE @Description NVARCHAR(50) SET @Description=(SELECT TOP 1 Description From MainTable ORDER BY ID DESC) Declare @SQL VarChar(1000) IF NOT EXISTS(SELECT * FROM sys.columns WHERE Name = '@Description' AND object_id = OBJECT_ID('ColumnsTable')) SELECT @SQL = 'ALTER TABLE [Education].[dbo].[Columnstable] ADD [' + @Description + '] MONEY' Exec (@SQL) Go
Dude, what? You want an INSERT statement to fire a trigger to create a new column on a different table? And to force that to be of type MONEY? And only on a single table / single columname? This reeks of bad database design / bad data design. I suspect you're not telling us the full story here.
At least he's checking sys.columns to avoid duplicate columns ;)
ALTER TRIGGER [dbo].[UpdateRow] ON [dbo].[MainTable] FOR UPDATE AS DECLARE @OldName NVARCHAR(20) DECLARE @NewName NVARCHAR(20) DECLARE @String NVARCHAR(100) DECLARE @Combine NVARCHAR(100) SET @OldName=(SELECT Description FROM DELETED) SET @NewName=(SELECT Description FROM INSERTED) SET @String='[Education].[dbo].[TblColumns]' SET @Combine=@String +'.'+ @OldName EXEC sp_rename @Combine, @newname,'Column'
15 People are following this question.
