December 18, 2013 at 1:42 pm
Is it possible to set a field value in a table based on 2 field values in 2 different tables? Which table gets the trigger?
table 1: 'dept' field must equal "OI"
table 2: 'age' field must be greater then 59
IF those conditions exist
table 3: set 'OB' = True
Here is what Ive done so far, im pretty sure im not returning anything for @age and I don't know how to check, this is on Insert. Sorry im a noob 😀
Declare @age Varchar (2)
DECLARE @dept VARCHAR (2)
SELECT @dept = dept FROM INSERTED
Select @age = [QA.AV].Age from [dbo].[QA.AV]
WHERE QACase IN (SELECT Qacase FROM INSERTED)
Update [qa.ombud]
Set [qa.ombud].ombud = 'True'
Where @Dept = 'OI' AND @age > '59'
December 18, 2013 at 1:59 pm
toneranger (12/18/2013)
Triggers are like events for a table. So you want to add the trigger to the table where you will be inserting data.
Here is what Ive done so far, im pretty sure im not returning anything for @age and I don't know how to check, this is on Insert. Sorry im a noob 😀
Declare @age Varchar (2)
DECLARE @dept VARCHAR (2)
SELECT @dept = dept FROM INSERTED
Select @age = [QA.AV].Age from [dbo].[QA.AV]
WHERE QACase IN (SELECT Qacase FROM INSERTED)
Update [qa.ombud]
Set [qa.ombud].ombud = 'True'
Where @Dept = 'OI' AND @age > '59'
Your code has some major issues. It can't handle multiple rows. I am not really sure how to help here with the code because we don't even know what table you are starting with. Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
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/
December 18, 2013 at 2:10 pm
toneranger (12/18/2013)
Is it possible to set a field value in a table based on 2 field values in 2 different tables? Which table gets the trigger?
All of them, if you use triggers, as a change in any of them could cause the issue. This overly complicates things.
table 1: 'dept' field must equal "OI"
table 2: 'age' field must be greater then 59
IF those conditions exist
table 3: set 'OB' = True
This is one of those perfect opportunities to use a calculated column. You won't be able to persist it because of the external data sources, so it may come down to how large your table 3 is. I would avoid using a trigger for this.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 18, 2013 at 2:11 pm
Yes, what Sean said, and:
Your 'where' clause in the update doesn't actually limit any rows in the table because it doesn't check any columns in the table. So it will set the values in all the rows when the variables are set correctly.
You'll need a trigger on table1 to check table2 and update table2 and you'll need a trigger on table2 to check table1 and update table3. That is assuming that the updates to table1 and table2 can happen independently.
Sample DDL and data, and expected outcomes will help a lot...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply