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 |
|
jbreslow
Starting Member
16 Posts |
 Posted - 2011-03-01 : 14:05:32
|
| Hello,I am trying to create a trigger to copy data from one table to a duplicate table on update. I would like this to be dynamic, so there is no need to list columns. Does anybody have a good example of this?One part I am stuck on is comparing columns from the two different tables. How does one reference a value returned from a query?select count(col.name) as num_columnsfrom sysobjects OBJ, syscolumns COLwhere obj.id = col.idand obj.name = 'USER_ADDRESS'select count(col.name) as num_columns_multifrom sysobjects OBJ, syscolumns COLwhere obj.id = col.idand obj.name = 'USER_ADDRESS_MULTI'-- make sure both tables have equal number of columnsif (query1.num_columns = query2.num_columns_multi){ UPDATE USER_ADDRESS_MULTI WITH CHANGES MADE TO USER_ADDRESS}Thank you for the help,Josh |
|
|
jbreslow
Starting Member
16 Posts |
Posted - 2011-03-02 : 13:32:48
|
| This did it... Thanks Josh!!!-- count columns in user_address tableSELECT @ua = (select count(col.name) as num_columnsfrom sysobjects OBJ, syscolumns COLwhere obj.id = col.idand obj.name = 'USER_ADDRESS')-- count columns in user_address_multi tableselect @uam = (select count(col.name) as num_columns_multifrom sysobjects OBJ, syscolumns COLwhere obj.id = col.idand obj.name = 'USER_ADDRESS_MULTI')IF @ua = @uam BEGIN print @uam ENDELSE BEGIN RAISERROR ('Columns do not match, change was not committed', 11,1) ROLLBACK END |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
Trigger Help