Trigger /Updates - SQL Server Forums

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

Old Forums

CLOSED - General SQL Server

Trigger /Updates

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-04 : 13:59:24

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

3246 Posts

Posted - 2002-03-04 : 14:56:57

In addition to reading up on triggers in BOL, you might want to read these articles by Merkin on Dynamic SQL:

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

------------------------
GENERAL-ly speaking...

eon
Starting Member

4 Posts

Posted - 2002-03-07 : 14:52:39

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.

indram
Starting Member

6 Posts

Posted - 2002-03-07 : 21:06:04

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.

eon
Starting Member

4 Posts

Posted - 2002-03-08 : 10:55:14

Thanks for the reply. I will give it a shot and let you know what the outcome is.

Appreciate your help,

eon
Starting Member

4 Posts

Posted - 2002-03-11 : 09:48:47

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,

indram
Starting Member

6 Posts

Posted - 2002-03-11 : 20:12:17

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.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-11 : 20:16:42

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.

indram
Starting Member

6 Posts

Posted - 2002-03-13 : 20:29:56

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

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-13 : 20:40:17

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)

indram
Starting Member

6 Posts

Posted - 2002-03-14 : 06:13:29

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?

eon
Starting Member

4 Posts

Posted - 2002-03-14 : 09:30:56

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources