Creating column through T-SQL

Question

Post reply

Creating column through T-SQL

mail2payan

SSCrazy

Points: 2286
More actions
August 19, 2009 at 11:45 pm

#95760

Is there any possibility to create columns through TRIGGER?
I mean to say, if I have table name employee_register where column will be generated in the following way:
-----------------------------------------------------
EMPCODE | 8/17/2009 | 8/18/2009 | 8/19/2009 | .....
-----------------------------------------------------
EMP001 | 1 | 0 | | .....
EMP002 | 0 | 0 | | .....
EMP003 | | | | .....
.
.
.
-----------------------------------------------------
----------------------------------------
Daipayan
A Beginner to the World of DBMS!
----------------------------------------

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

I strongly recommend to stay with a relational data model, meaning to have three columns: EMPCODE, Date, value.

Therewith you don't need any code to add a new column for a new date.

The structure you've shown could be done with a view based on the table above, using dynamic cross tab.

Edit: removed option of using PIVOT since this is not a 2K5 forum...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

Unfortunately I saw this post first and answered there. You can check that out for my answer.

I did post a link to this thread in that thread to redirect everyone here.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Daniel Smith-480684 Say Hey Kid Points: 694 More actions · Answer 3

The straight-forward answer is no, see Jack Corbett's reply on the duplicate post.

The other thing and this is restating what Imu92 says is that even if you could you shouldn't.

There are two reasons for this in my mind: first a trigger is used to alter values in related tables, or the same table. A simple example is to think of milk in a fridge. If someone uses some milk then a trigger would note that more milk needed to be bought after checking the amount left.

The second reason is that this is the path to madness. If triggers can alter tables, views etc: then this opens the door to all sorts of hacky programming, circular dependencies, not to say the danger of your brain collasping when contemplating threading and which user action has fired which trigger causing a column to be renamed, added or deleted. I hope you get the picture.