SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 [RESOLVED] - Trigger and Columns
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mickey_pt
Starting Member

6 Posts

Posted - 08/02/2010 :  07:01:46  Show Profile  Reply with Quote
Hello
Need 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?

Thanks

New one :)

Edited by - mickey_pt on 08/02/2010 07:28:51

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 08/02/2010 :  07:09:39  Show Profile  Visit webfred's Homepage  Reply with Quote
Assuming the extra columns are at the end of table/column list.
Try this for example:

insert your_table
select *, getdate(), 'I'
from inserted

But 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.
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/02/2010 :  07:14:35  Show Profile  Reply with Quote

INSERT INTO MyAuditTable
SELECT GetDate() as MyAudtDate, 'xxx' AS MyOperation, *
FROM inserted

Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/02/2010 :  07:15:32  Show Profile  Reply with Quote
"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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/02/2010 :  07:17:03  Show Profile  Reply with Quote
See: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=170215

Edited by - Kristen on 01/14/2012 08:32:29
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 08/02/2010 :  07:21:01  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

mickey_pt
Starting Member

6 Posts

Posted - 08/02/2010 :  07:28:28  Show Profile  Reply with Quote
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 all

New one :)
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/02/2010 :  08:05:33  Show Profile  Reply with Quote
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 ...)

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000