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.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 [RESOLVED] - Trigger and Columns

Author  Topic 

mickey_pt
Starting Member

6 Posts

Posted - 2010-08-02 : 07:01:46
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 :)

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_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

22859 Posts

Posted - 2010-08-02 : 07:14:35
[code]
INSERT INTO MyAuditTable
SELECT GetDate() as MyAudtDate, 'xxx' AS MyOperation, *
FROM inserted
[/code]
Go to Top of Page

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

Kristen
Test

22859 Posts

Posted - 2010-08-02 : 07:17:03
See: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=170215
Go to Top of Page

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

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 all

New one :)
Go to Top of Page

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 ...)

Go to Top of Page
   

- Advertisement -