June 17, 2013 at 2:21 pm
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
June 17, 2013 at 2:38 pm
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
June 17, 2013 at 2:39 pm
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 belowselect 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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 17, 2013 at 2:50 pm
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
June 17, 2013 at 2:55 pm
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 17, 2013 at 3:06 pm
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
June 17, 2013 at 3:13 pm
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 17, 2013 at 3:21 pm
you are right, but for now i think if you have the expertise for such an update statement please let me know.
thanks
June 17, 2013 at 3:24 pm
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 17, 2013 at 3:51 pm
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
June 18, 2013 at 8:00 am
SQLTestUser (6/17/2013)
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 selectselect 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
OK so we are part way there. You have me at an extreme disadvantage here. Your original tables were all A,B,C etc now they are all different completely cryptic names (I hope those are not the real names of your tables).
What I don't understand is the business logic. This query returns 5 columns. I think you are wanting to see if some are NULL or that sort of thing? I also don't know the relationship from any of these tables to your audit table. To get your values I think you either want to use coalesce or a case expression. The business rules will help determine what would work best.
_______________________________________________________________
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 18, 2013 at 9:40 am
Thanks for the reply,
i am looking for Null values and based on those null values i want to update a column in one of the tables. A trigger that would update on insert or update with a update statement that has if statements
thanks
June 18, 2013 at 9:48 am
SQLTestUser (6/18/2013)
Thanks for the reply,i am looking for Null values and based on those null values i want to update a column in one of the tables. A trigger that would update on insert or update with a update statement that has if statements
thanks
No offense but you are talking in circles here. I thought you wanted to update ONE table, but now we are back to updating multiple tables.
An update cannot have IF statements. In t-sql IF statements are used to control flow of processing statements. You might need a case expression in your update statement. I do not have even close to a clear picture of what you are doing here. If you can post ddl for ALL of the tables and some data along with a clear definition of what you are trying to do I will do my best to help.
_______________________________________________________________
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 18, 2013 at 2:13 pm
Thanks for the help , and it is sort of hard to explain what i actually want expect to put it as how i have already. Thanks for all the help. i will try to get the process done through the front end.
thanks again
June 18, 2013 at 2:16 pm
SQLTestUser (6/18/2013)
Thanks for the help i guess i just can not explain the process as i want, thank u for the help
The issue hear seems to be a language barrier. We can easily work through that if we can find a language we both speak. Lucky for us that language is sql. As I said if you can post ddl and sample data along with an explanation of what you want we can try to knock this out.
_______________________________________________________________
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply