question

john123 avatar image
john123 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
sql-server-2008sql server
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

john123 avatar image john123 commented ·
I want to rename a column using dynamic query
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
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.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Valentino Vranken avatar image Valentino Vranken commented ·
At least he's checking sys.columns to avoid duplicate columns ;)
3 Likes 3 ·
john123 avatar image
john123 answered
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'
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.