SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Trigger to set a value for a column


Trigger to set a value for a column

Author
Message
SQLTestUser
SQLTestUser
SSC Veteran
SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)

Group: General Forum Members
Points: 271 Visits: 548
I have a column that has to be set with a certain value as A,B,C or D when a certain column has a value i.e. is not null. The columns are in various tables in the databases and the select is show below

select A.decription, B.Decsription,C.Description,D.Decription
from Audit A left join A.Id = B.ID
left join on B.Id =C.ID
left join on C.ID = D.ID

and the column X in Audit table has to be updated with A,B,C,D as when if there is value in A.description the value for X will be B, when B.Description has value the value on X will be C, when c.description is there the value for X will be D

how do i form a trigger that does this
thanks
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28640 Visits: 39977
details or ddl wasn't available, so here's my best guess.

is the trigger setting values on the table is is set for, or a completely different table ("Audit")?

because you obfuscated everything, you also lost any details for the actual commands;
from your description now, a column"X" gets set to a table, instead of a column in a table?

if there is value in A.description the value for X will be B


regardless of the details, you'll just need to do an UPDATE FROM statement in your trigger

a rough draft looks something like this i guess:

UPDATE myTarget
SET MyTarget.X = B.SomeColumnName
from Audit myTarget
inner join INSERTED ON myTarget.ID = INSERTED.ID
left join B on myTarget.Id = B.ID
left join C on B.Id = C.ID
left join D on C.ID = D.ID



Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26450 Visits: 17557
SQLTestUser (6/17/2013)
I have a column that has to be set with a certain value as A,B,C or D when a certain column has a value i.e. is not null. The columns are in various tables in the databases and the select is show below

select A.decription, B.Decsription,C.Description,D.Decription
from Audit A left join A.Id = B.ID
left join on B.Id =C.ID
left join on C.ID = D.ID

and the column X in Audit table has to be updated with A,B,C,D as when if there is value in A.description the value for X will be B, when B.Description has value the value on X will be C, when c.description is there the value for X will be D

how do i form a trigger that does this
thanks


So you want to update the value of column X in Audit when a value in either A, B, C, or D happens? You would need to create insert/update triggers on EACH of those tables.

It seems to me that you might have better luck with a different approach than triggers. If you could explain the process, the business logic and provide a bit more details maybe somebody will have a better idea than triggers for this type of thing.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
SQLTestUser
SQLTestUser
SSC Veteran
SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)

Group: General Forum Members
Points: 271 Visits: 548
the logic is to trace where the user is in the application at a certain period, and display the information on the application accordingly. So i added a column and i am trying to figure out the data from different table and update the related column accordingly.

an update would do that for existing records but for new records i need the column to update each time the records change in different table. i hope that made mo sense than my original post
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26450 Visits: 17557
SQLTestUser (6/17/2013)
the logic is to trace where the user is in the application at a certain period, and display the information on the application accordingly. So i added a column and i am trying to figure out the data from different table and update the related column accordingly.

an update would do that for existing records but for new records i need the column to update each time the records change in different table. i hope that made mo sense than my original post


It seems like you are making this more then you need to? One way to solve this so you can track what screen or whatever a user is to could create a simple table.


create table UserLocation
(
UserID int,
UserLocation varchar(50)
)



Now when there is a row for the current user you just update this one row. If it doesn't exist, you create it. Make that a stored proc that receives those two parameters and this becomes a lot simpler than trying to jump through all these hoops.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
SQLTestUser
SQLTestUser
SSC Veteran
SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)

Group: General Forum Members
Points: 271 Visits: 548
Yes but what for existing users, they are already in different locations and i think this is the best way to do it.
any ideas??

i need an if else in my update and have that in a trigger.

update X
SET tableA.field 1 = 'this is field2'
where TableA.field is not null
or set tableA.field = 'this is field2'
where TableA.field is not null and TableB.field is not null
from table A inner join table B on tableA.Id = TableB.ID
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26450 Visits: 17557
SQLTestUser (6/17/2013)
Yes but what for existing users, they are already in different locations and i think this is the best way to do it.
any ideas??

i need an if else in my update and have that in a trigger.

update X
SET tableA.field 1 = 'this is field2'
where TableA.field is not null
or set tableA.field = 'this is field2'
where TableA.field is not null and TableB.field is not null
from table A inner join table B on tableA.Id = TableB.ID


I would centralize the logic, then you only have to create this data for your existing data. Otherwise you are going to be fighting this forever. Each time you add a new table you will have to modify this process.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
SQLTestUser
SQLTestUser
SSC Veteran
SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)

Group: General Forum Members
Points: 271 Visits: 548
you are right, but for now i think if you have the expertise for such an update statement please let me know.
thanks
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26450 Visits: 17557
SQLTestUser (6/17/2013)
you are right, but for now i think if you have the expertise for such an update statement please let me know.
thanks


Are you able to write a select statement to get the correct value for a given user?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
SQLTestUser
SQLTestUser
SSC Veteran
SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)

Group: General Forum Members
Points: 271 Visits: 548
yes that is pretty easy to select them and check what value is there its just the update i am struggling with. here is the select

select P.Id, P.Desc as PD,
Tf.Description as TF ,
PC.PID as PCI,
PM.OP as Op
from P left join TF ON
P.ID = TF.ID
left join PC on
Pc.ID = P.ID
left Join PCM on
PCM.ID = P.ID

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search