Click here to monitor SSC
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
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16586 Visits: 17024
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)
SQLTestUser
SQLTestUser
SSC-Enthusiastic
SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)

Group: General Forum Members
Points: 173 Visits: 548
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
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16586 Visits: 17024
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)
SQLTestUser
SQLTestUser
SSC-Enthusiastic
SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)

Group: General Forum Members
Points: 173 Visits: 548
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
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16586 Visits: 17024
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)
SQLTestUser
SQLTestUser
SSC-Enthusiastic
SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)

Group: General Forum Members
Points: 173 Visits: 548
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
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16586 Visits: 17024
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)
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