Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mickey_pt
Starting Member
6 Posts |
 Posted - 2010-08-02 : 07:01:46
|
| HelloNeed some info.I have a big table with more than 100 columns, i need to create a trigger to every time the table is updated (insert,delete or update), i create a copy of the original row to another table with the same structure, but with two extra columns, the date of the update, and the operation.If i had the same structure i just needed to make a select into...But i have the two extra columns, do i need to refer all columns in the select and then in the insert use the previous values plus the new ones.It's there another way to do this, that i don't need to write all the names?ThanksNew one :) |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-08-02 : 07:09:39
|
Assuming the extra columns are at the end of table/column list.Try this for example:insert your_tableselect *, getdate(), 'I'from insertedBut honestly, I would prefer to have all column names in the select list.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-02 : 07:14:35
|
| [code]INSERT INTO MyAuditTableSELECT GetDate() as MyAudtDate, 'xxx' AS MyOperation, *FROM inserted[/code] |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-02 : 07:15:32
|
"Assuming the extra columns are at the end of table/column list."We put them at the front, and don't use a column list, so that the trigger will work when we add columns in the future (provided we remember to add the columns to botht he Main table and the Audit table  |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-02 : 07:17:03
|
| See: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=170215 |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-08-02 : 07:21:01
|
I always have a bad feeling when using * as column list. Apart from ad hoc queries.It's purely a matter of taste
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
mickey_pt
Starting Member
6 Posts |
Posted - 2010-08-02 : 07:28:28
|
| After writing this i solved the problem... wirting sometimes helps. :)Thanks for the Tip, i was thinking to put the columns in the end, but it's better to put it in the beginning.Thanks to allNew one :) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-02 : 08:05:33
|
quote:
Originally posted by webfred
I always have a bad feeling when using * as column list. Apart from ad hoc queries.
I'm with you on that, but I do have a set of carefully considered, and permitted, exceptions to house-rules. This is one of them. SELECT * in a VIEW is another one that we allow in some places ("I want columns from the MasterTable and the Lookup-Description of the columns that have associated tables" would be another example ...) |
|
|
|
|
|
|
|
|
[RESOLVED] - Trigger and Columns