Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Trigger to set a value for a column Expand / Collapse
Author
Message
Posted Monday, June 17, 2013 2:21 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 2:56 PM
Points: 160, Visits: 502
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
Post #1464368
Posted Monday, June 17, 2013 2:38 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:43 AM
Points: 12,895, Visits: 32,090
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1464375
Posted Monday, June 17, 2013 2:39 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:34 PM
Points: 12,923, Visits: 12,342
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 Moden's 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)
Post #1464376
Posted Monday, June 17, 2013 2:50 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 2:56 PM
Points: 160, Visits: 502
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
Post #1464384
Posted Monday, June 17, 2013 2:55 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:34 PM
Points: 12,923, Visits: 12,342
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 Moden's 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)
Post #1464387
Posted Monday, June 17, 2013 3:06 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 2:56 PM
Points: 160, Visits: 502
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
Post #1464392
Posted Monday, June 17, 2013 3:13 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:34 PM
Points: 12,923, Visits: 12,342
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 Moden's 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)
Post #1464399
Posted Monday, June 17, 2013 3:21 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 2:56 PM
Points: 160, Visits: 502
you are right, but for now i think if you have the expertise for such an update statement please let me know.
thanks
Post #1464404
Posted Monday, June 17, 2013 3:24 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:34 PM
Points: 12,923, Visits: 12,342
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 Moden's 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)
Post #1464407
Posted Monday, June 17, 2013 3:51 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 2:56 PM
Points: 160, Visits: 502
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
Post #1464416
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse