Trigger to set a value for a column

Question

Trigger to set a value for a column

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply

SQLTestUser Hall of Fame Points: 3997 More actions · Answer 1

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 SSC Guru Points: 286562 More actions · Answer 2

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 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/