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
 Old Forums
 CLOSED - General SQL Server
 Trigger /Updates
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 03/04/2002 :  13:59:24  Show Profile  Visit AskSQLTeam's Homepage
EON writes "Here is my dilemma, I have 2 tables Table1 in one database and Table2 in another both on the same server. Here is my layout

Table1
Database:
Smartlogin
Name of table:
Employees
Fields:
Emp_no | Firstname | Lastname | Homephone | SocialSecurity
2 John Doe 214-444-2221 222-22-2222
3 Joe Schmoe 214-333-2222 444-22-5555
Table2
Database:
UpdatedData
Name of table:
Holdingarea
Fields:
ID | Fldname | NewValue | DBName | TblName
2 Firstname User1 smartlogin Employees
2 Lastname Last1 smartlogin Employees
3 Firstname User2 "" ""
3 Lastname Last2 "" ""

Here is my question (Just to mention I am a junior SQL developer)
I need to update to values in table1 with the values in Table2 using a update in a trigger. How can I do so when my fields are ambigious because the actual field names exists as records, only reflecting the actual field names in Table 1(I hope that makes sense). I know I could probably due a update table.field = table.field based on ID's but how could I do so when they are records? Also, there are thousands of records that will need to be updated if you can give me an idea as to how I could tackle this question along with the most efficient method it would be greatly appreciated.

Thanks,"

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 03/04/2002 :  14:56:57  Show Profile  Visit AjarnMark's Homepage
In addition to reading up on triggers in BOL, you might want to read these articles by Merkin on Dynamic SQL:

http://www.sqlteam.com/item.asp?ItemID=4599
http://www.sqlteam.com/item.asp?ItemID=4619

------------------------
GENERAL-ly speaking...
Go to Top of Page

eon
Starting Member

4 Posts

Posted - 03/07/2002 :  14:52:39  Show Profile
Thanks for the reply, but the info. is not what I was really looking for. I need to be able to refresh the values from the holding table into their respective tables that the field name/record name indicates.(Does that make any sense:-)) I'm also trying to figure out a way to only update the referencing tables/records based on the employee ID only if the data is not equal to the holding table. I have thousands of employee IDs and I dont want the trigger to kick off each time there is an insert in my table and refreshing the entire destination tables every time. Any suggestions are appreciated.


Go to Top of Page

indram
Starting Member

Indonesia
6 Posts

Posted - 03/07/2002 :  21:06:04  Show Profile
I assume that you’ve read the trigger in BOL, so at least you know how to construct it. First, I want to clarify your problems:
-You want to use the field value in table2 as a field name in table1
-Only corresponding records that are going to be updated, for example: ID 2 for emp_no 2.

For the first one you need to build dynamic SQL statement. How? There are two ways to do this: use the EXEC ('..') or sp_executesql. Each way has its own advantages. Read more in BOL.

For the next problem you need the inserted table. The inserted table stores copies of the affected rows during INSERT and UPDATE statements.


....
--declare the variables first
....
SELECT @ID =ID, @FieldName = FieldName, @NewValue = NewValue, @DBName=DBName, @TblName = TblName
FROM inserted

....
--now construct the SQL Statement
EXEC ('UPDATE ' + @DBName +'.dbo.' + @TblName +
' SET ' + @FieldName +'=' + '''' +@NewValue+ ''''
' WHERE emp_no = ' @ID)
....

Hope it helps.



Go to Top of Page

eon
Starting Member

4 Posts

Posted - 03/08/2002 :  10:55:14  Show Profile
Thanks for the reply. I will give it a shot and let you know what the outcome is.

Appreciate your help,

Go to Top of Page

eon
Starting Member

4 Posts

Posted - 03/11/2002 :  09:48:47  Show Profile
Hey Indram,

I thought when you use variables that the variable can only hold a single value? I am trying to figure out how I can pass the EmpID into the variable since I will be using a trigger to actually do an automatic update. I see how passing the specific ID will only update the mentioned employee, but like I said since I am using trigger how can I pass those values into the trigger.

thanks,

Go to Top of Page

indram
Starting Member

Indonesia
6 Posts

Posted - 03/11/2002 :  20:12:17  Show Profile
I believe that the INSERTED table only contain 1 row (the row which is being inserted/updated) so each time the trigger fired you can only update 1 row. You get the ID value from this INSERTED table. Try to read more on INSERTED table topics in BOL.



Go to Top of Page

robvolk
Most Valuable Yak

USA
15635 Posts

Posted - 03/11/2002 :  20:16:42  Show Profile  Visit robvolk's Homepage
The inserted pseudo-table can have more than one row in it, as long as the INSERT or UPDATE statement affects more than one row (the same applies for the deleted pseudo-table). It's a good idea to write triggers so that they can handle multiple rows; even if your operations are limited to one row now, you can expand them in the future without having to change the triggers.

Go to Top of Page

indram
Starting Member

Indonesia
6 Posts

Posted - 03/13/2002 :  20:29:56  Show Profile
quote:

The inserted pseudo-table can have more than one row in it, as long as the INSERT or UPDATE statement affects more than one row (the same applies for the deleted pseudo-table).



Yup, you're right. I miss that. So eon, you need a cursor to iterate this table (Although we should avoid the cursor operation, I don't see any other way to do this. Somebody can help?).

Go to Top of Page

robvolk
Most Valuable Yak

USA
15635 Posts

Posted - 03/13/2002 :  20:40:17  Show Profile  Visit robvolk's Homepage
NO CURSORS!

I'm sure there's a primary key on this table, just JOIN the inserted table to the other table on that key column (for example, ID):

CREATE TRIGGER updateTable2 ON Table1 FOR UPDATE AS
UPDATE T2
SET T2.col1=I.col1
FROM Table2 T2 INNER JOIN inserted I ON (T2.ID=I.ID)


Go to Top of Page

indram
Starting Member

Indonesia
6 Posts

Posted - 03/14/2002 :  06:13:29  Show Profile
Well, in most cases we can use that set operation. However, eon case is different. He has to build the query on the run time. Field value on affected table is the field name on the query. So it should look like this:


EXEC ('UPDATE ' + @DBName +'.dbo.' + @TblName +
' SET ' + @FieldName + '='+... )


Or maybe you can show us the way to solve eon problem?



Go to Top of Page

eon
Starting Member

4 Posts

Posted - 03/14/2002 :  09:30:56  Show Profile
Hey guys,

Appreciate all the feedback. Here is my actual query.

Update test1.dbo.Contact
set Email = web.dbo.holding.newvalue
from web.dbo.holding inner join test1.dbo.Contact
on (web.dbo.holding.empno = test1.dbo.Contact.id)
where web.dbo.holding.fldnme = "Email"

In my trigger I have about several individual statements like this one above and each one referencing another field in the destination database.table. My problem is that I have a holding table that will contain all the fields and records populated from the web into my holding area. In my holding table their are 5 fields that I am using to associate the referencing tables, one of my fields contains the actual field names of the referencing tables but they are listed as records(that is the purpose of my syntax above). When I insert all my code into the trigger it launches of course and everything gets updated, but then I have two problems 1) It updates everything each time and 2) Only the first record it finds will get updated so if their are duplicate records because of test purposes the most current record does not get updated into the real time table.

Thanks for any help,

Eon

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