How to lookup and insert setup values

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

General SQL Server Forums

New to SQL Server Programming

How to lookup and insert setup values

Author

Topic

scholin
Yak Posting Veteran

56 Posts

Posted - 2010-01-03 : 07:57:42

I have a client setup table defining which fields will be visible on a web database using show/hide properties on the web page.

When a client opens a web page and creates a new record in a table called dbo.IOM_Tests, I want to find a way to lookup and insert values to be used on the web to indicate which fields can be visable or not.

For this web page there are a possible 10 dbo.IOM_Tests fields that can be visible if the corresponding dbo.client test fields are marked true.

So for example, in dbo.client table, I will have 10 bit fields called test1, test2, test3, etc.

In my dbo.IOM_Test table I will have the same bit fields. Both tables will have a linking field called client_id.

I am looking for help on writing the code upon insert of a new dbo.IOM_Test record to lookup in dbo.client where client_id=client=id and inserting the values from dbo.client to dbo.IOM_Test.

Do I use a trigger or procedure and how would I write this?

Thank you in advance for any help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-03 : 11:37:57

you can use an insert trigger. however, its not clear what fields you need to copy to IOM_test and is it based on values of test1,test2,..?

scholin
Yak Posting Veteran

56 Posts

Posted - 2010-01-03 : 18:34:50

Thank you Visakh for responding. Here is what I have:

Table: dbo.client
Primary Key: Client_ID
Setup columns of interest(all bit data type): test1, test2, test3, . . through test10

Table that I want to insert these values into after a new record is created: dbo.IOM_tests
Foreign Key: Client_ID
Columns to insert the data into: test1, test2, test3 - through 10
Where Client_id=Client_id

So the fields in both tables are same type and name. Because different clients who each potentially require different tests to be displayed on the web site depending on how I have set them up, I want to use these fields when a new dbo.IOM_tests record is created as this will trigger the trigger the viability of each module.

I am not sure how to write it.

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-04 : 01:35:56

then its just like


UPDATE t
SET t.test1=c.test1,
t.test2=c.test2,..
FROM dbo.IOM_tests t
JOIN dbo.client c
ON c.Client_id=t.Client_id

scholin
Yak Posting Veteran

56 Posts

Posted - 2010-01-04 : 10:20:00

Visakh,

I am testing this out with the first 4 testX fields but I get and error saying "Incorrect syntax near the keyword 'FROM'."

Do you see anything with what I am doing here?

Thanks - MIke
*******************************

UPDATE t
SET t.test1=c.test1,
t.test2=c.test2,
t.test3=c.test3,
t.test4=c.test4,

FROM dbo.IOM_tests t
JOIN dbo.client c
ON c.Client_id=t.Client_id

scholin
Yak Posting Veteran

56 Posts

Posted - 2010-01-04 : 10:23:49

Almost forgot to ask - when I get this working, how would I write this to be a permanent trigger to trigger every time a new dbo.IOM_Tests table is created?

Thank you again for all your help!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-04 : 10:29:16

delete the last comma before FROM

No, you're never too old to Yak'n'Roll if you're too young to die.

scholin
Yak Posting Veteran

56 Posts

Posted - 2010-01-04 : 11:11:40

I know SQL is basic to you guys but because I don't fully understand this yet it never ceases to put a smile on my face when these things actually work - kind of like watching a magic trick!

Yep, it was the extra comma.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-05 : 03:24:56

quote:
Originally posted by scholin

Almost forgot to ask - when I get this working, how would I write this to be a permanent trigger to trigger every time a new dbo.IOM_Tests table is created?

Thank you again for all your help!

put the code inside insert trigger


CREATE TRIGGER UpdateData
ON dbo.IOM_Tests
AFTER INSERT
AS
BEGIN

UPDATE t
SET t.test1=c.test1,
t.test2=c.test2,
t.test3=c.test3,
t.test4=c.test4
FROM dbo.IOM_tests t
JOIN dbo.client c
ON c.Client_id=t.Client_id
JOIN INSERTED i
ON i.Client_id=t.Client_id

END

scholin
Yak Posting Veteran

56 Posts

Posted - 2010-01-05 : 07:08:51

Perfecto!

Thank you once again for sharing your skills and knowledge!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-06 : 03:18:06

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources