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 Tuesday, June 18, 2013 8:00 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 3:21 PM
Points: 13,083, Visits: 11,918
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 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


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 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 #1464670
Posted Tuesday, June 18, 2013 9:40 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:36 AM
Points: 158, Visits: 492
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
Post #1464739
Posted Tuesday, June 18, 2013 9:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 3:21 PM
Points: 13,083, Visits: 11,918
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 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 #1464747
Posted Tuesday, June 18, 2013 2:13 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:36 AM
Points: 158, Visits: 492
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
Post #1464869
Posted Tuesday, June 18, 2013 2:16 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 3:21 PM
Points: 13,083, Visits: 11,918
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 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 #1464870
Posted Tuesday, June 18, 2013 3:50 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:36 AM
Points: 158, Visits: 492
DDL

CREATE TABLE [dbo].[Status](
[ID] [int] NOT NULL,
[Type] [nvarchar](5) NOT NULL,
[Status] [nvarchar](10) NULL,
[OpenedByID] [nvarchar](15) NULL,
[OpenedDate] [datetime] NULL,
CONSTRAINT [Cause_PK] PRIMARY KEY NONCLUSTERED
(
[ID] ASC,
[Type] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


CREATE TABLE [dbo].[Fix](
[ID] [int] NOT NULL,
[Type] [nvarchar](5) NOT NULL,
[Description] [nvarchar](4000) NULL,
CONSTRAINT [Fix_PK] PRIMARY KEY NONCLUSTERED
(
[ID] ASC,
[Type] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


CREATE TABLE [dbo].[Measure](
[ID] [int] NOT NULL,
[Type] [nvarchar](5) NOT NULL,
[OverallPlan] [nvarchar](4000) NULL,
CONSTRAINT [Measure_PK] PRIMARY KEY NONCLUSTERED
(
[ID] ASC,
[Type] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


CREATE TABLE [dbo].[Statement](
[ID] [int] NOT NULL,
[Type] [nvarchar](5) NOT NULL,
[Title] [nvarchar](100) NULL,
[Happening] [nvarchar](4000) NULL,
CONSTRAINT [Statement_PK] PRIMARY KEY NONCLUSTERED
(
[ID] ASC,
[Type] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Cause](
[ID] [int] NOT NULL,
[Type] [nvarchar](5) NOT NULL,
[CausativeID] [smallint] NOT NULL,
[CauseDesc] [nvarchar](4000) NULL,
[ShortPCDesc] [nvarchar](100) NULL,
CONSTRAINT [ID_PK] PRIMARY KEY NONCLUSTERED
(
[ID] ASC,
[Type] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO



THe select statement
Select C.CausativeID,F.Description,M.OverallPlan,S.Title, ST.Status
from Statement S left join Cause C on C.ID = S.ID
left join Measure M on S.ID = M.Id
left join Fix F on S.ID = F.ID
left join Status ST on S.ID = ST.ID



** the causative iD can have multiple ID's associated to the same ID from STatus, but i only want to check if there is an ID

i want to add a column on status that can be set to Status when the Status.Status is NULL or has no value
or as Cause when there null or no value in Cause.CausativeID ans STatus.Status have value
or as Fix when F.Description has no value and Cause.CausativeID ans STatus.Status have value
or as Status when ST. Status has no value and Cause.CausativeID ans STatus.Status,and F.Description have values

thanks
Post #1464893
Posted Wednesday, June 19, 2013 7:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 3:21 PM
Points: 13,083, Visits: 11,918
SQLTestUser (6/18/2013)

THe select statement
Select C.CausativeID,F.Description,M.OverallPlan,S.Title, ST.Status
from Statement S left join Cause C on C.ID = S.ID
left join Measure M on S.ID = M.Id
left join Fix F on S.ID = F.ID
left join Status ST on S.ID = ST.ID



** the causative iD can have multiple ID's associated to the same ID from STatus, but i only want to check if there is an ID

i want to add a column on status that can be set to Status when the Status.Status is NULL or has no value
or as Cause when there null or no value in Cause.CausativeID ans STatus.Status have value
or as Fix when F.Description has no value and Cause.CausativeID ans STatus.Status have value
or as Status when ST. Status has no value and Cause.CausativeID ans STatus.Status,and F.Description have values

thanks


Thank you for posting ddl. It is really hard to figure out because there is still no sample data and the description of your desired output is totally unclear. It looks like you need a case expression with 4 possibilities.

The first option doesn't make any sense to me at all. The next three options might be something like this but it is hard to know for sure.

case 
when Cause.CausativeID IS NULL and STatus.Status IS NOT NULL then Cause
when F.Description IS NULL AND Cause.CausativeID IS NOT NULL AND STatus.Status IS NOT NULL then Fix
when ST.Status IS NOT NULL and Cause.CausativeID IS NOT NULL AND STatus.Status IS NOT NULL AND F.Description then Status
end as TheNewStatus



_______________________________________________________________

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 #1465181
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse